Шаблоны отчётности из реальной практики
Реализуйте классические панели мониторинга: кривые удержания, топ-N для каждой категории и выделение сессий — всё с помощью оконных функций
«Шаблоны отчётности из реальной практики» — бесплатный урок SQL Academy на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.
Шаблон: Top-N для каждой группы
Три лучших заказа для каждого пользователя:
WITH ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
FROM orders
)
SELECT * FROM ranked WHERE rn <= 3;Шаблон: накопительные итоги
Накопительная выручка с течением времени:
SELECT day, revenue,
SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue;Шаблон: первое появление
Первый момент, когда каждый пользователь выполнил каждое действие:
SELECT user_id, action, MIN(ts) AS first_at
FROM events
GROUP BY user_id, action;
-- Or with window functions for full row:
WITH firsts AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id, action ORDER BY ts) AS rn
FROM events
)
SELECT * FROM firsts WHERE rn = 1;Шаблон: удержание когорт
Пользователи, сгруппированные по неделе регистрации; удержание — по номеру недели N:
WITH cohorts AS (
SELECT id AS user_id, date_trunc('week', created_at) AS cohort_week
FROM users
),
activities AS (
SELECT user_id, date_trunc('week', ts) AS active_week FROM events
)
SELECT c.cohort_week,
(a.active_week - c.cohort_week) / 7 AS week_offset,
COUNT(DISTINCT a.user_id) AS active
FROM cohorts c
JOIN activities a USING (user_id)
WHERE a.active_week >= c.cohort_week
GROUP BY c.cohort_week, week_offset
ORDER BY c.cohort_week, week_offset;Шаблон: анализ воронки
Сколько пользователей достигают каждого шага:
SELECT
COUNT(*) AS signed_up,
COUNT(*) FILTER (WHERE first_login_at IS NOT NULL) AS logged_in,
COUNT(*) FILTER (WHERE first_purchase_at IS NOT NULL) AS purchased
FROM users;Шаблон: формирование сессий
Объединяйте события в сессии, если интервал между ними превышает 30 минут:
WITH gaps AS (
SELECT user_id, ts,
CASE
WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts)
> INTERVAL '30 min'
THEN 1 ELSE 0
END AS new_session
FROM events
)
SELECT user_id, ts,
SUM(new_session) OVER (PARTITION BY user_id ORDER BY ts) AS session_id
FROM gaps;Шаблон: сравнение периодов
Сравнение текущего и предыдущего месяца:
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
revenue - LAG(revenue) OVER (ORDER BY month) AS delta,
(revenue::FLOAT / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) - 1) * 100 AS pct_change
FROM monthly_revenue
ORDER BY month;Шаблон: сводный результат
Формат с широкими строками с помощью FILTER:
SELECT user_id,
SUM(amount) FILTER (WHERE month = '2024-01') AS jan,
SUM(amount) FILTER (WHERE month = '2024-02') AS feb,
SUM(amount) FILTER (WHERE month = '2024-03') AS mar
FROM monthly_spend
GROUP BY user_id;Шаблон: активные пользователи сегодня
DAU/WAU/MAU:
SELECT
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '1 day') AS dau,
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '7 days') AS wau,
COUNT(DISTINCT user_id) FILTER (WHERE ts >= NOW() - INTERVAL '30 days') AS mau
FROM events;Шаблон: заполнение пропусков
Для дней без событий нужно показывать 0, а не пропускать их:
SELECT day, COALESCE(COUNT(e.id), 0) AS events
FROM generate_series(CURRENT_DATE - 30, CURRENT_DATE, INTERVAL '1 day') AS day
LEFT JOIN events e ON date_trunc('day', e.ts) = day
GROUP BY day
ORDER BY day;Объединение оконных функций для анализа
Несколько столбцов с оконными вычислениями в одном запросе — удобно для чтения и быстро:
SELECT day, revenue,
LAG(revenue) OVER w AS prev,
AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg_7d,
SUM(revenue) OVER (ORDER BY day) AS running_total
FROM daily_revenue
WINDOW w AS (ORDER BY day)
ORDER BY day;Итоги
Большинство отчётов строится на нескольких шаблонах: top-N, накопительные итоги, когорты, воронки, формирование сессий, сравнение периодов, сводные таблицы и заполнение пропусков. Освойте их — и сможете создать любую необходимую панели мониторинга SQL.
Быстрая проверка
Вы создаёте отчёт «5 лучших продуктов для каждой категории». Какой идиоматический шаблон SQL следует использовать?
Часто задаваемые вопросы
Урок «Шаблоны отчётности из реальной практики» бесплатный?
Да — полный текст урока «Шаблоны отчётности из реальной практики» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.
Чему я научусь в уроке «Шаблоны отчётности из реальной практики»?
Реализуйте классические панели мониторинга: кривые удержания, топ-N для каждой категории и выделение сессий — всё с помощью оконных функций Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.
Нужен ли мне опыт, чтобы начать SQL Academy?
Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.
Сколько времени занимает урок «Шаблоны отчётности из реальной практики»?
Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.
Можно ли писать и запускать код в этом уроке SQL Academy?
Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.
Все уроки этого курса
- Рамки: ROWS и RANGE
- LAG и LEAD с оконными рамками
- Разбиение на группы с NTILE и Cume_Dist
- Шаблоны отчётности из реальной практики