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

Эти три функции по-разному назначают позиции при совпадении значений.

• ROW_NUMBER присваивает каждой строке уникальный порядковый номер, даже при совпадении значений.
• RANK присваивает совпавшим строкам один номер и пропускает следующие позиции.
• DENSE_RANK присваивает совпавшим строкам один номер, не оставляя пропусков.
• Для дедупликации до одной строки используйте ROW_NUMBER, а в топ-N запросах осознанно выбирайте нужную логику ранжирования.

Для дедупликации я ранжирую записи каждого пользователя от самой новой к самой старой.

• Считаю ROW_NUMBER с PARTITION BY user_id и ORDER BY updated_at DESC.
• Фильтрую результат по rn = 1, чтобы оставить одну полную строку на пользователя.
• После updated_at добавляю стабильный тайбрейкер, например первичный ключ.
• Не использую GROUP BY с MAX, если нужны колонки именно из той же последней записи.

Надёжный расчёт скользящего среднего за 7 дней начинается с полного дневного ряда.

• Сначала агрегирую выручку ровно до одной строки на календарный день.
• Применяю AVG с рамкой ROWS BETWEEN 6 PRECEDING AND CURRENT ROW.
• Джойню календарный ряд дат, чтобы дни без заказов тоже присутствовали.
• Заполняю пропущенную дневную выручку нулём, если отсутствие заказов действительно означает нулевую выручку.

Я выбираю между этими вариантами с учётом читаемости, переиспользования и необходимости материализации.

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

Фильтр по правой таблице в WHERE чаще всего и заставляет LEFT JOIN терять строки без совпадений.

• После джойна колонки правой таблицы у строк без совпадения равны NULL.
• Условие вроде WHERE orders.status = 'paid' отбрасывает такие строки с NULL.
• Поэтому оставшийся результат фактически ведёт себя как INNER JOIN.
• Если строки из левой таблицы нужно сохранить, перенесите условие по правой таблице в ON.

Точный подсчёт уникальных значений дорог, потому что движку приходится отслеживать каждое из них.

• На больших таблицах операция требует много памяти, хеширования или сортировки.
• Приближённые функции и скетчи HyperLogLog дают погрешность около 1-2 процентов при значительно меньших затратах на вычисления.
• Если отчёт требует точности, регулярно используемые метрики с подсчётом уникальных значений стоит предагрегировать в сводные таблицы.
• Метод нужно выбирать по требованиям к точности, а не по умолчанию брать самый дорогой вариант.

Даты первого и последнего заказа можно посчитать одним запросом с группировкой.

• Группирую по user_id и считаю MIN(order_date) и MAX(order_date).
• К двум полученным значениям применяю функцию разницы дат из используемой СУБД.
• Если нужны атрибуты крайних заказов, использую FIRST_VALUE или LAST_VALUE.
• Для LAST_VALUE задаю рамку до UNBOUNDED FOLLOWING, иначе функция по умолчанию вернёт текущую строку.

LAG и LEAD дают доступ к соседним строкам без self-join.

• LAG читает предыдущую строку, а LEAD следующую строку внутри выбранного окна.
• LAG(revenue) по месяцам ставит выручку прошлого месяца рядом с текущим значением.
• Тогда рост месяц к месяцу равен разнице текущего и прошлого значений, делённой на прошлое.
• LAG по таймстемпам событий также подходит для анализа пауз между действиями, сессий и оттока.

При замедлении запроса я отдельно проверяю изменения в данных, плане выполнения и состоянии хранилища.

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

В SQL я отношусь к NULL как к неизвестному значению, а не как к обычному сравнимому значению.

• Предпочитаю NOT EXISTS, потому что NOT IN может не вернуть ни одной строки, если подзапрос содержит NULL.
• Помню, что COUNT по колонке и большинство агрегатов пропускают NULL, а подсчёт всех строк учитывает такие строки.
• Использую COALESCE только там, где бизнес-смысл подстановочного значения явно определён.
• Проверяю долю NULL и применяю IS DISTINCT FROM для NULL-безопасных сравнений там, где он поддерживается.

Для сравнения каждой строки со средним по группе обычно нужна оконная функция.

• AVG(order_value) OVER (PARTITION BY customer_id) сохраняет исходную гранулярность строк.
• Отклонение можно посчитать сразу рядом с каждым заказом.
• Self-join с предагрегацией добавляет код и ещё одну возможность ошибиться в ключах джойна.
• Джойн оправдан только при слабой поддержке окон или когда агрегат строится по другому отфильтрованному набору строк.

WHERE фильтрует исходные строки, а HAVING фильтрует группы после агрегации.

• Неагрегатные условия помещаю в WHERE, чтобы лишние строки отбрасывались раньше.
• HAVING использую для условий вроде SUM(amount) > 1000.
• Обычное условие на строку в HAVING может сработать, но заставит движок делать лишнюю агрегацию.
• Для каждого условия определяю, зависит ли оно от результата агрегатной функции.

Сессионизация сводится к поиску разрывов и последующему накопительному подсчёту.

• Через LAG получаю таймстемп предыдущего события каждого пользователя.
• Помечаю новую сессию, если прошлого события нет или разрыв превышает 30 минут.
• Считаю нарастающую SUM флага по пользователю и получаю номера сессий.
• Если таймстемпы могут совпадать, добавляю детерминированный тайбрейкер событий.

Перцентили описывают скошенные распределения лучше, чем одно среднее.

• Для точной непрерывной медианы использую PERCENTILE_CONT(0.5) там, где функция поддерживается.
• Для очень больших данных в хранилище при необходимости использую приближённую функцию квантилей.
• Тяжёлые хвосты в распределениях выручки и времени отклика могут сильно отдалять среднее от типичного наблюдения.
• Показываю медиану вместе с хвостовой метрикой вроде p90, чтобы отразить типичные значения и хвост распределения.

По умолчанию я использую UNION ALL, если дедупликация не является явным требованием.

• UNION ALL просто добавляет наборы строк и не ищет дубли в общем результате.
• UNION добавляет глобальную сортировку или хеширование, что дорого на широких данных.
• Автоматическая дедупликация может скрыть дубли, которые на самом деле нужно расследовать.
• UNION стоит брать только после определения колонок, по которым две строки результата считаются дублями.

Условная агрегация является переносимым способом развернуть известные значения в колонки средствами SQL.

• Группирую по измерению, которое должно остаться в строках.
• Каждую выходную колонку строю через SUM и условие CASE для нужного месяца.
• Нативный PIVOT короче, но обычно тоже требует заранее перечислить значения колонок.
• По-настоящему динамические колонки оставляю BI-слою, а не генерирую нестабильный SQL.

Рекурсивные CTE полезны, когда число шагов обхода заранее неизвестно.

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

Партиционирование и кластеризация важны, потому что форма запроса определяет объём сканируемых данных.

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

p-value показывает, насколько необычен наблюдаемый результат при предположении об отсутствии эффекта.

• Сначала предполагаем, что тестируемое изменение не даёт реального эффекта.
• Представляем повторение эксперимента и выборочную вариативность при этом предположении.
• p-value равен вероятности получить результат не менее экстремальный, чем наблюдаемый.
• Это не вероятность того, что изменение сработало или что нулевая гипотеза верна.

Доверительный интервал показывает выборочную неопределённость вокруг оценки.

• Процедура для 95-процентного интервала накрывает истинное значение примерно в 95 процентах повторных выборок.
• Оценку прироста показываю вместе с нижней и верхней границами.
• Интервал от -1 до +7 ведёт к другим решениям, чем интервал от +2 до +4.
• Сравниваю правдоподобный диапазон с важными для бизнеса выгодами и потерями, а не только с нулём.