Аналитический 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…
Как посчитать воронку конверсии на SQL? Middle
Для каждого пользователя определить, дошёл ли он до каждого шага в правильном порядке (время шага не раньше предыдущего) и в пределах окна (например, 7 дней от первого шага)…
Как удалить дубликаты и оставить последнюю версию записи? Junior
Найти: GROUP BY ключ HAVING COUNT() > 1. Оставить последнюю версию: пронумеровать строки внутри ключа ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC) и взять rn = 1…
Как найти самую длинную серию дней подряд, когда пользователь был активен? Middle
Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем…
Почему после JOIN суммы в отчёте оказались больше, чем должны быть? Junior
Связь один-ко-многим размножает строки: заказ с тремя позициями после JOIN с позициями превращается в три строки, и SUM(order.amount) учитывает сумму заказа трижды. Решение …
Как посчитать когортный retention на SQL? Middle
Определить когорту каждого пользователя (обычно неделя или месяц первого действия), для каждого события посчитать номер периода от начала когорты, затем посчитать уникальных…

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

Закрытая бета

Знать тему — половина дела

На живом собеседовании sotto слышит вопрос интервьюера и показывает короткий ответ и план. Оставьте контакт — пришлём доступ и промокод.

Промокод на 2 часа — сверх пробных 30 минут
Программа для Windows — скачать сразу, войти — через Яндекс ID или VK ID
Первыми узнаете о версии для Mac — пока sotto только для Windows
Прямая связь с разработчиком: что улучшить — сделаем