Вопросы на собеседовании аналитика данных

INNER JOIN оставляет только строки, у которых нашлась пара в обеих таблицах. LEFT JOIN сохраняет все строки левой таблицы, а там, где пары нет, ставит NULL в столбцы правой. Если соединить клиентов с заказами через INNER JOIN, клиенты без заказов исчезнут, а через LEFT JOIN останутся с пустыми полями заказа.

Условие в ON проверяется во время соединения, а WHERE фильтрует уже готовый результат. Если после LEFT JOIN написать «WHERE o.status = 'paid'», клиенты без заказов отпадут, потому что у них status равен NULL, и LEFT JOIN по сути превратится в INNER. Чтобы сохранить всех клиентов, условие пишут в ON: «LEFT JOIN orders o ON o.client_id = c.id AND o.status = 'paid'».

WHERE отбирает строки до группировки, а HAVING отбирает группы после того, как посчитаны агрегаты. Это следует из порядка выполнения запроса: сначала FROM и WHERE, потом GROUP BY и HAVING и только затем SELECT. Клиентов, у которых больше трёх оплаченных заказов, найдёт запрос «SELECT client_id, COUNT(*) FROM orders WHERE status = 'paid' GROUP BY client_id HAVING COUNT(*) > 3».

NULL означает, что значение неизвестно, поэтому сравнение «phone = NULL» даёт не «истину», а тоже NULL, и строка в результат не попадает. Пустые значения ищут через «IS NULL» и «IS NOT NULL». С агрегатами похожая история: «COUNT(*)» считает все строки, а «COUNT(phone)» только те, где телефон заполнен.

«SELECT c.id, COALESCE(SUM(p.amount), 0) AS paid_total FROM clients c LEFT JOIN payments p ON p.client_id = c.id AND p.status = 'paid' GROUP BY c.id». LEFT JOIN сохраняет клиентов без оплат, условие на статус стоит в ON, а COALESCE превращает пустую сумму в ноль.

GROUP BY схлопывает строки группы в одну, а оконная функция считает значение по набору строк и оставляет каждую строку на месте. Например, «SUM(amount) OVER (PARTITION BY client_id)» добавит к каждому заказу общую сумму покупок клиента, и сами заказы из результата не исчезнут.

Если в таблице по строке на каждый день без пропусков, подойдёт запрос «SELECT day, AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d FROM daily_revenue». Рамка берёт текущую строку и шесть предыдущих, а ORDER BY задаёт, какие строки считаются предыдущими.

ROW_NUMBER нумерует строки подряд, даже если значения равны. RANK даёт равным одинаковый ранг и пропускает следующие номера, а DENSE_RANK пропусков не делает. Для сумм 500, 500 и 300 ROW_NUMBER выдаст 1, 2, 3, RANK выдаст 1, 1, 3, а DENSE_RANK выдаст 1, 1, 2. Если нужен строгий топ-3, берут ROW_NUMBER, потому что остальные функции при равенстве значений вернут больше строк.

Пронумерую записи внутри каждого пользователя от новой к старой и оставлю первую: «SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, id DESC) AS rn FROM events) t WHERE rn = 1». Второй ключ сортировки нужен на случай одинакового времени, иначе запрос будет возвращать разные строки от запуска к запуску.

CTE — это именованный подзапрос, который объявляют через WITH в начале запроса, и он живёт только в рамках этого запроса. Так каждый шаг расчёта получает имя: сначала «paid_orders», потом «client_totals», потом итоговый SELECT.

В центре схемы стоит таблица фактов с событиями и числами, например продажи с суммой и количеством. Вокруг неё лежат таблицы измерений с описаниями: товары, клиенты, даты, магазины. Факты ссылаются на измерения по ключам. Если измерения дополнительно разбить на связанные таблицы, получится «снежинка».

ВПР ищет значение только в первом столбце диапазона и возвращает данные из столбцов правее. ПРОСМОТРX ищет в любом столбце, возвращает значение слева или справа, по умолчанию ищет точное совпадение и умеет выводить свой текст вместо ошибки #Н/Д. В Excel 2016 и 2019 ПРОСМОТРX нет, поэтому там используют связку ИНДЕКС и ПОИСКПОЗ, которая тоже ищет в любую сторону.

Сводная таблица считает итоги по выбранным разрезам без формул. Из выгрузки продаж быстро получится выручка по месяцам и категориям. Срезы позволяют фильтровать отчёт кнопками, а расчётные поля добавляют показатели вроде доли или маржи.

Power Query подключается к файлам, базам и другим источникам, чистит и объединяет таблицы и записывает каждый шаг обработки. Когда приходит новая выгрузка, отчёт обновляется одной кнопкой, и не нужно заново удалять дубли и править форматы.

Сначала разберусь, что считать дублем: полностью одинаковые строки или повтор номера заказа. Удалю дубли и сверю, сколько строк ушло. Потом добавлю столбец маржи как разницу выручки и себестоимости и построю сводную, где в строках категории, а в значениях сумма выручки и маржи. В конце сравню итог сводной с суммой по исходному столбцу.

Тип графика зависит от вопроса. Категории сравнивают столбцами, динамику показывают линией, а круговую диаграмму берут, только чтобы показать долю целого, и только когда категорий немного.

Перед сборкой я выясняю, какое решение будет приниматься по дашборду и кто его смотрит. Каждый график должен отвечать на один из этих вопросов. Если просят график, который ни на что не отвечает, предлагаю вынести его в отдельный отчёт с детализацией, потому что один понятный дашборд по теме полезнее десяти.

Сверю цифры с источником и с ожиданиями заказчика: если выручка вдвое больше привычной, сначала ищу ошибку у себя. Проверю, что фильтры работают так, как подписано, а пропуски в данных не замазаны соединённой линией. В конце дам будущему пользователю решить на дашборде пару реальных задач.

Вычисляемый столбец считается для каждой строки таблицы при обновлении данных и хранится в модели, например маржа по каждой продаже. Мера считается в момент показа, с учётом фильтров и разрезов отчёта, например общая выручка за выбранный месяц и регион. Поэтому итоги и доли, которые должны меняться вместе с фильтрами отчёта, считают мерами.

loc выбирает строки и столбцы по меткам, а iloc выбирает их по порядковым номерам. После фильтрации индекс таблицы идёт с пропусками, например 0, 3, 7, и тогда «df.loc[3]» вернёт строку с меткой 3, а «df.iloc[1]» вернёт вторую строку по порядку, то есть ту же самую.

merge соединяет таблицы по ключу, как JOIN в SQL, а параметр how задаёт тип соединения: inner, left, right или outer. concat склеивает таблицы друг под другом или рядом без поиска совпадений, например выгрузки за январь и февраль с одинаковыми столбцами.

Значит, ключ не уникален. Если у одного клиента три строки в одной таблице и две в другой, merge выдаст по нему шесть строк, все сочетания. Перед соединением проверю уникальность ключа, а в самом merge поставлю «validate='many_to_one'», и pandas выдаст ошибку, если справа ключ повторяется.

Через groupby и agg: «df.groupby('city').agg(orders=('order_id', 'count'), revenue=('amount', 'sum'))». Так за один проход получится и число заказов, и выручка по каждому городу, а имена новых столбцов задаются сразу.

Посчитаю пропуски по столбцам через «df.isna().sum()» и посмотрю, случайны ли они. Если их мало и они разбросаны случайно, строки можно удалить через dropna. Если пропусков много, заполню медианой или самым частым значением через fillna, а во временном ряду соседним значением.

Проверю, свежие ли данные и пришли ли они целиком, ведь успешная загрузка ещё не значит, что источник не был пустым. Посмотрю на дубли, пропуски и форматы дат. Потом проверю данные на здравый смысл: нет ли отрицательного количества товара и пропусков только по выходным.

Когда в данных длинный хвост или выбросы. Медиана — это середина ряда, и несколько огромных значений на неё почти не влияют, а среднее тянется к ним. Если у большинства сотрудников зарплата около 100 тысяч, а у двух директоров по миллиону, среднее покажет «типичную» зарплату, которую почти никто не получает. Поэтому сначала смотрю на распределение, а потом выбираю показатель.

Дисперсия — это средний квадрат отклонения от среднего, поэтому она выражена в квадратных единицах, например в рублях в квадрате. Стандартное отклонение — корень из дисперсии, оно в тех же единицах, что и данные, и его проще объяснить. Оба показателя чувствительны к выбросам.

Сначала найду их, например через ящик с усами или по отклонению больше трёх стандартных отклонений от среднего. Потом разберусь, ошибка это или реальное значение. Ошибку исправлю или уберу, а реальные значения оставлю или ограничу общим порогом. Если это A/B-тест, порог задаю один для обеих групп, потому что при отдельной чистке групп растёт число ложных срабатываний.

Если брать много выборок и считать по каждой среднее, распределение этих средних с ростом выборки приближается к нормальному, даже когда сами данные скошены, как доходы или чеки. Поэтому для t-теста в A/B-эксперименте нормальность самих данных не требуется, достаточно большой выборки.

p-value — это вероятность получить такой же или ещё более сильный результат, если на самом деле эффекта нет. Если p-value меньше выбранного заранее уровня значимости, обычно 0,05, разницу считают статистически значимой. Вероятность того, что гипотеза верна, p-value не показывает.

Ошибка первого рода — увидеть эффект, которого нет, то есть отвергнуть верную нулевую гипотезу. Её вероятность и есть уровень значимости, например 5%. Ошибка второго рода — не заметить эффект, который есть.

«Средний чек по выборке 1 500 рублей, но это оценка по части покупателей. Интервал от 1 420 до 1 580 рублей — диапазон, в котором с надёжностью 95% лежит настоящий средний чек всех покупателей. Чем больше данных, тем уже этот диапазон».

Если тест ошибается в 1% случаев, то из 1 000 человек болен один, и тест его, скорее всего, найдёт. Ещё примерно у 10 здоровых тест ошибочно окажется положительным. Значит, среди 11 положительных результатов болен один, и вероятность около 9%.

DAU — это число уникальных пользователей за день, а MAU считает их за месяц. Их отношение называют stickiness, оно показывает, как часто месячная аудитория возвращается. Если DAU 5 000, а MAU 50 000, stickiness равен 10%.

Разобью пользователей на когорты по дню первой активности. Для каждой когорты посчитаю, какая доля вернулась на седьмой день после первого входа. В SQL это первый день пользователя через «MIN(event_date)» в CTE, соединение с событиями и деление числа вернувшихся на размер когорты.

ARPU — это выручка, делённая на всех пользователей за период, ARPPU — выручка, делённая только на платящих. Они связаны: ARPU равен ARPPU, умноженному на долю платящих.

LTV — это сколько денег в среднем приносит пользователь за всё время, пока он пользуется продуктом. Простой способ — взять когорту и сложить выручку от неё за каждый месяц жизни, а потом разделить на размер когорты. Сравнение когорт показывает, растёт ли ценность новых пользователей.

Выручка равна трафику, умноженному на конверсию и на средний чек. Конверсия выросла, значит, просел другой множитель: стало меньше посетителей или упал средний чек. Один из возможных сценариев связан со скидками: они поднимают конверсию, но часть покупок случилась бы и без скидки, поэтому выручка может упасть.

Промокод чаще применяют те, кто и так активно покупает, поэтому такие группы различаются ещё до акции. Разница между ними покажет вовлечённость, а не эффект промокода. Причину проверяют экспериментом со случайной контрольной группой. Если его не провели, используют квазиэксперименты, например сравнение динамики до и после акции с похожей группой, и прямо называют допущения.

Гипотезу и целевую метрику, которую должна сдвинуть функция, определю до запуска. Добавлю защитные метрики, которые нельзя уронить, например отмены или жалобы. Посчитаю размер выборки и длительность, договорюсь о правиле остановки и о сегментах, которые будем смотреть.

От уровня значимости, мощности, разброса метрики и минимального эффекта, который хотим заметить, то есть MDE. Чем меньше эффект мы хотим поймать и чем больше разброс метрики, тем больше нужно пользователей. Поэтому MDE согласуют с бизнесом до запуска, чтобы понять, какой рост вообще стоит внедрения.

Каждый раз, когда мы проверяем результат по ходу теста, растёт шанс случайно поймать «значимость» без реального эффекта. При частых проверках ложные срабатывания случаются в разы чаще заложенных 5%. Поэтому правило остановки фиксируют заранее, а если проверять по ходу всё-таки нужно, используют методы с поправкой на повторные проверки.

Это SRM, расхождение размера групп с планом. Проверю его критерием хи-квадрат. Если оно значимо, результат интерпретировать не буду, пока не найду причину, потому что попадание в группу зависит от неизвестного фактора, который может влиять и на метрику. Искать стоит в механизме разбиения, в условиях попадания в тест, в фильтрах и в логировании, а смотреть удобно в разрезах платформы и версии приложения.

Без поправки на множественные сравнения нельзя, потому что при десяти независимых проверках на уровне 5% шанс хотя бы одной ложной находки около 40%. Бонферрони делит уровень значимости на число проверок, метод Холма даёт тот же контроль с меньшей потерей мощности, а Бенджамини-Хохберг контролирует долю ложных находок, когда гипотез много.

Сначала проверю, не сломался ли сбор событий и пришли ли данные за весь день. Потом сравню с тем же днём неделю назад и учту праздники и сезонность. Дальше найду, где именно падение, в разрезах платформы, версии приложения, региона и источника трафика, и проверю технические сбои. Изменения в продукте и маркетинге смотрю последними.

Начну с цели, то есть с выручки, и разложу её на число заказов и средний чек. Заказы — это активные пользователи, умноженные на частоту заказов, а пользователи — это новые плюс вернувшиеся. Средний чек равен числу позиций в заказе, умноженному на среднюю цену позиции.

Проверю, значимы ли результаты в каждой стране и хватило ли выборки во второй. Если в первой эффект значим, а во второй тест просто не набрал данных, раскатаю в первой и продолжу тест во второй. Если во второй эффект достоверно нулевой или отрицательный, поищу причину в различиях стран: цены, платёжные привычки, состав аудитории.

«Два года работаю аналитиком в интернет-магазине. Пишу запросы на SQL, собираю отчёты в Power BI и считаю результаты A/B-тестов. Последний большой проект — анализ оттока по когортам: я нашёл, что треть ушедших клиентов не дошла до второго заказа из-за долгой доставки, и мы запустили бесплатную доставку второго заказа. Хочу больше работать с экспериментами, поэтому меня заинтересовала ваша вакансия».

«Продакт хотел понять, нужна ли новая лента рекомендаций. Я спроектировал A/B-тест на две недели с конверсией в заказ как целевой метрикой и отменами как защитной. Конверсия выросла, отмены не изменились, и ленту раскатили на всех».

«Я посчитал рост конверсии после редизайна и не заметил, что в тот же период стартовала скидочная акция. Руководитель спросил про акцию, и вывод пришлось пересчитать. С тех пор перед любым сравнением до и после я проверяю календарь акций и релизов».

Расскажу, какие задачи в аналитике мне интересны и почему именно в этой компании: например, что я пользуюсь их сервисом и хотел бы разбираться, почему пользователи возвращаются или уходят.

Начну с вывода и рекомендации в двух-трёх предложениях, а цифры и метод оставлю на вопросы. Например: «Новая форма оплаты подняла конверсию на 3%, эффект значимый. Рекомендую оставить её для всех. Подробный расчёт и ограничения могу показать».

Выясню, на чём основано несогласие, и, если заказчик нашёл изъян в анализе, пересмотрю вывод. Если изъяна нет, разделю, что известно точно, что вероятно, а что пока открыто, и предложу, как проверить спорную часть и сколько это займёт. «Не знаю» без плана проверки звучит как отписка, а с планом проверки звучит как честный ответ.

Назову причину фактами и без эмоций: «Компания небольшая, и аналитика там сводится к отчётам. Я хочу работать с экспериментами и продуктом, а на текущем месте таких задач нет».

Заранее подготовлю три цифры: минимум, комфортный уровень и рыночный уровень для похожих вакансий. Назову цифру чуть выше рыночной или минимум, оговорив, какой уровень для меня комфортен. Деньги предложу обсудить после разговора о задачах.