КейсПрактика

Курсы · бесплатно, без регистрации

Продвинутый SQL: оконные функции, сессии и метрики — курс с проверкой задач

Курс SQL для аналитика, который уже пишет JOIN и GROUP BY: 51 урок и 154 задачи на базе авиакомпании с журналом событий приложения. Оконные функции и рамки, серии и сессии, JSON, история изменений, качество данных, продуктовые метрики и эксперименты — бесплатно и без регистрации.

Начать с первого урокаКарта курса
  • 16 модулей, 51 урок
  • 154 задачи с проверкой
  • 14 таблиц, 4,1 млн строк
  • синтаксис PostgreSQL

Оконная функция за десять секунд

Это база курса: рейсы, бронирования, программа лояльности и журнал событий приложения. Запустите готовый запрос или поменяйте его.

query.sqlбаза «Авиаперевозки», расширенная · 14 таблиц · 4,1 млн строк
Готовые запросы

Результат появится здесь. Запрос выполняется на той же учебной базе, что и в курсе.

Как устроен урок

  1. 01

    Идея

    Короткое объяснение одной конструкции и пример, который можно запустить на той же базе.

  2. 02

    Разбор

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

  3. 03

    Три задачи

    Разминка, основная и со звёздочкой. Подсказок с каждой задачей меньше, подсказка в две ступени — по запросу.

  4. 04

    Проверка

    Сервер выполняет запрос и сверяет результат с эталоном. Если не сошлось, видно, чем именно: столбцы, строки или значения.

Программа курса

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

Окна, даты и соединения

Ранги и топ-N, доли и накопление, соседние строки, ряды дат без пропусков и местное время, соединение таблицы с собой, FULL JOIN и рекурсия.

  1. Модуль 1

    Оконные функции: нумерация и ранги

    Агрегат рядом с каждой строкой, места и ничьи, топ-N в группе и удаление дублей

    Что будете уметь: Сравнивать строку со средним и размером её группы, не теряя строк; нумеровать и ранжировать строки внутри групп; выбирать топ-N в каждой группе и оставлять последнюю запись по ключу.

  2. Модуль 2

    Оконные функции: доли, накопление, соседи

    Доля от итога, накопительный итог и скользящее среднее, сравнение с предыдущей и следующей строкой через LAG и LEAD

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

  3. Модуль 3

    Даты без пропусков

    Календарь без дыр через generate_series, сетка «день × объект» через CROSS JOIN, недели и разница в днях, неполные периоды на краю данных и местное время

    Что будете уметь: Строить ряды дат и часов, где пустой день — ноль, а не пропуск, и сетки, где на месте каждая пара «день × объект»; считать дни между датами и сравнивать периоды честно, не путая обрыв данных с падением; переводить московское время в местное по часовому поясу аэропорта.

  4. Модуль 4

    Сложные соединения и рекурсия

    Таблица, соединённая сама с собой, сверка двух наборов через FULL JOIN и рекурсивный обход маршрутной сети

    Что будете уметь: Находить пары строк внутри одной таблицы без двойного счёта, сверять два набора через FULL JOIN и находить пустые клетки сетки, обходить сеть рекурсивным запросом с ограничением глубины и защитой от циклов.

Последовательности и структура

Рамки окон и перцентили, серии и острова, сессии и пути пользователя, сложная агрегация, JSON и массивы.

  1. Модуль 5

    Рамки окон и перцентили

    ROWS, RANGE и GROUPS, первое и последнее значение окна, квантильные группы, медиана и перцентили по группам

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

  2. Модуль 6

    Серии и острова

    Серии дней подряд через разность даты и номера строки, паузы и возвращения через LAG, периоды сбоя по часам и склейка пересекающихся интервалов

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

  3. Модуль 7

    Сессии и пути

    Нарезка журнала событий на сессии, источник сессии из свойств события, первое и последнее касание перед покупкой, соседние шаги, воронка внутри сессии и строка пути

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

  4. Модуль 8

    Сложная агрегация

    Подытоги в одном запросе, развороты строк в столбцы и обратно, склейка значений, массивы и логические флаги

    Что будете уметь: Строить отчёты с подытогами и общим итогом и отличать итог от NULL в данных; разворачивать метрики в столбцы и собирать столбцы обратно в строки переносимым SQL; склеивать значения группы в строку в нужном порядке, считать уникальные сочетания столбцов и отвечать на вопросы «все ли» и «хоть один».

  5. Модуль 9

    JSON и массивы

    Поля из JSON-свойств событий, числа и даты из текста, массивы в строки и соединение событий приложения с бронями и билетами

    Что будете уметь: Доставать поля из JSON и приводить их к нужному типу, разворачивать массивы в строки и считать популярность элементов, соединять события с таблицами по ключу из JSON и по элементам массива и находить расхождения сумм — и знать, чем запись в DuckDB отличается от PostgreSQL.

Данные в работе

История изменений, качество данных, продуктовые метрики и эксперименты — так, как их считают в продакшене.

  1. Модуль 10

    История изменений

    Интервалы действия записи и значение на момент, соединение событий с историей на их дату, сборка истории из дат изменений и проверка стыков

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

  2. Модуль 11

    Качество данных

    Дубли доставки и первая доставка, поздние события и окно дозревания, проверки-инварианты и сверка двух источников через FULL JOIN

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

  3. Модуль 12

    Продуктовые метрики как в работе

    DAU, WAU и MAU на каждый день и stickiness, retention когорт — классический, rolling и с поправкой на зрелость, LTV на одинаковом горизонте и движение выручки по клиентам

    Что будете уметь: Считать активную аудиторию скользящими окнами без COUNT(DISTINCT) OVER, строить когорты с флагом на клиента и пустыми незрелыми клетками, сравнивать LTV когорт на одном горизонте и раскладывать изменение выручки на новых, растущих, сократившихся, ушедших и вернувшихся.

  4. Модуль 13

    Эксперименты в SQL

    Проверка сплита до результата, эффект с доверительным интервалом и сегменты без подгонки — на двух A/B-тестах приложения

    Что будете уметь: Проверять A/B-тест перед чтением: SRM хи-квадратом, единицу анализа и момент назначения; считать разницу конверсий, её стандартную ошибку, 95% интервал и z на переносимом SQL; разбирать эффект по сегментам с поправкой на множественные проверки и писать вывод с честными оговорками.

Задачи с собеседований

Классические задачи уровня middle с разбором.

  1. Модуль 14

    Задачи с собеседований

    N-я величина и медиана вручную, последовательности и повторы, разбор падения метрики — как их дают на интервью аналитика

    Что будете уметь: Решать типовые задачи собеседования уровня middle и отвечать на вопросы следом: вторая и N-я величина при ничьих, медиана без встроенной функции, возврат назавтра, серии подряд, повтор в течение N дней, удаление дублей, изменение неделя к неделе, разложение падения по сегментам и неаддитивность уникальных.

Итоговые кейсы

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

  1. Модуль 15

    Итоговый кейс: продажи и посадка

    Воронка от продажи до вылета, когорты броней с флагом и поправкой на зрелость, продажи на одинаковом горизонте и записка руководителю с проверкой данных

    Что будете уметь: Собирать из окон, дат и соединений ответ на рабочий вопрос — воронку, когорты с флагом на объект, сравнение на одинаковом горизонте и сводную по вариантам — и проверять, не держится ли вывод на срезе или особенности выгрузки.

  2. Модуль 16

    Итоговый кейс: одностраничное оформление

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

    Что будете уметь: Достраивать запросы из знакомых шагов — сессии, острова, JSON, история изменений, проверки качества — до ответа на вопрос о запуске: считать эффект теста с интервалом и проверять, что на самом деле измеряет метрика, прежде чем писать вывод.

Примеры задач

Формулировки — из самого курса: от первого ранга до задачи с собеседования.

Кому подойдёт и что нужно знать заранее

Курс для тех, кто уверенно пишет SELECT, JOIN, GROUP BY и подзапросы и упирается в задачи посложнее: «первая покупка каждого клиента», «сколько дней подряд», «удержание по когортам», «значим ли результат теста». Если эти темы ещё не знакомы, начните с курса «SQL с нуля».

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

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

Что дальше

Симулятор · первые главы бесплатноСимулятор SQL-аналитикаСюжетный курс: вы стажёр в компании и отвечаете запросами на вопросы команды — от DAU до разбора A/B-теста.Курс · бесплатноSQL с нуляЕсли нужно закрыть пробелы в основах: от устройства таблиц до JOIN, подзапросов и CTE.

Частые вопросы

Курс бесплатный?

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

Что нужно знать до начала?

SELECT и WHERE, агрегаты и GROUP BY, JOIN, CASE и подзапросы — то, чему учит курс «SQL с нуля». Оконные функции знать не обязательно: с них курс начинается.

Чем курс отличается от «SQL с нуля»?

Темами и данными. «SQL с нуля» доводит до JOIN и подзапросов на восьми таблицах. Здесь 14 таблиц, включая журнал событий приложения на полтора миллиона строк, а темы — оконные функции, серии, сессии, JSON, история изменений, метрики и эксперименты.

Поможет ли курс подготовиться к собеседованию?

Да, для этого есть отдельный модуль «Задачи с собеседований»: N-й по величине и медиана без встроенных функций, последовательности, поиск аномалии и объяснение падения метрики. Два итоговых кейса проверяют всё вместе на задаче без подсказок.

Какой диалект SQL в курсе?

Курс учит SQL, который работает в PostgreSQL. Запросы выполняет DuckDB; там, где базы расходятся (JSON и массивы, деление целых, функции дат), урок называет обе записи, а проверка предупреждает, если запрос не сработает в PostgreSQL.

Выдаётся ли сертификат?

Нет. Курс не выдаёт сертификатов и документов об образовании.

Что делать после курса?

Симулятор SQL-аналитика: сюжетный курс, где запросы пишут в ответ на вопросы команды, а в конце — записка менеджеру с выводами.

Начать с первого урока