Этап 4 из 14 · ≈ 12 ч

Аналитический SQL

Основа с Junior

Оконные функции, когортный retention, воронки, сессии, серии и перцентили.

Что важно знать

  • Оконные функции не схлопывают строки, в отличие от GROUP BY.
  • ROW_NUMBER, RANK, LAG, LEAD, накопительные суммы.
  • Размножение строк при JOIN «один ко многим» — завышенные суммы.
  • Медиана и перцентили — percentile_cont.

Критерии освоения

Junior ≈ 12 ч

  • Пишет запросы с оконными функциями — ранги, накопительные суммы, LAG и LEAD
  • Выбирает топ-N в каждой группе и удаляет дубли

Практика. Решить 20 задач на оконные функции на учебной базе заказов.

Middle ≈ 10 ч

  • Считает когортный retention, воронки и сессии на SQL
  • Находит ошибки размножения строк при JOIN и проверяет результаты запросов

Практика. Посчитать на SQL когортный retention, воронку покупки и среднюю длительность сессии по таблице событий.

Проверка знаний

Войдите, чтобы пройти тест: сданный тест отмечает тему пройденной на карте и в ИПР.

Вопросы с собеседований

Смотреть все
Как посчитать DAU, WAU, MAU и скользящий MAU на SQL?Junior

DAU — число уникальных пользователей за день: COUNT(DISTINCT user_id) GROUP BY day. MAU календарный — то же по месяцам. Скользящий MAU на каждый день — уникальные за последние 30 дней; его нельзя получить суммой DAU (пользователь посчитается много раз) …

Полный разбор
Как посчитать воронку конверсии на SQL?Middle

Для каждого пользователя определить, дошёл ли он до каждого шага в правильном порядке (время шага не раньше предыдущего) и в пределах окна (например, 7 дней от первого шага), затем посчитать число пользователей на каждом шаге и доли от первого и от…

Полный разбор
Как удалить дубликаты и оставить последнюю версию записи?Junior

Найти: GROUP BY ключ HAVING COUNT() > 1. Оставить последнюю версию: пронумеровать строки внутри ключа ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC) и взять rn = 1 — в выборке или в DELETE с этим условием.

Полный разбор
Как найти самую длинную серию дней подряд, когда пользователь был активен?Middle

Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем группировать по (пользователь, эта разность) и считать длину серии.

Полный разбор
Почему после JOIN суммы в отчёте оказались больше, чем должны быть?Junior

Связь один-ко-многим размножает строки: заказ с тремя позициями после JOIN с позициями превращается в три строки, и SUM(order.amount) учитывает сумму заказа трижды. Решение — сначала агрегировать каждую таблицу до нужной гранулярности, потом соединять; или…

Полный разбор
Как посчитать когортный retention на SQL?Middle

Определить когорту каждого пользователя (обычно неделя или месяц первого действия), для каждого события посчитать номер периода от начала когорты, затем посчитать уникальных активных пользователей по (когорта, период) и разделить на размер когорты.

Полный разбор
Что такое оконные функции в SQL и чем они отличаются от GROUP BY?Junior

GROUP BY схлопывает группу в одну строку; оконная функция считает по группе строк (окну), сохраняя все строки. Окно задаёт OVER (PARTITION BY … ORDER BY … ROWS …). Основные: ранжирующие (ROW_NUMBER, RANK, DENSE_RANK), смещения (LAG, LEAD), агрегаты по окну…

Полный разбор
Как выбрать топ-N записей в каждой группе, например три самых дорогих заказа каждого клиента?Junior

Пронумеровать строки внутри группы оконной функцией и отфильтровать номер во внешнем запросе: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) и WHERE rn <= 3. Если важны «ничьи» — DENSE_RANK или RANK вместо ROW_NUMBER.

Полный разбор
Как посчитать медиану и перцентили в SQL и почему среднее часто вводит в заблуждение?Junior

В PostgreSQL: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) — медиана с интерполяцией между значениями, PERCENTILE_DISC — ближайшее реальное значение. В ClickHouse — quantile, в BigQuery — APPROX_QUANTILES. Среднее чувствительно к выбросам: один заказ на…

Полный разбор
Как разбить поток событий на сессии на SQL?Middle

Сессия — серия событий пользователя без пауз дольше порога (обычно 30 минут). LAG даёт время предыдущего события; где пауза больше порога — флаг начала новой сессии; нарастающая сумма флагов по пользователю — номер сессии. Дальше агрегаты по (пользователь…

Полный разбор

← Вся карта «Data Scientist»

Партнёр платформы

Разбор собеседования с sotto

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

Разбор собеседования