3 апреля 2026 г.·34 мин чтения

50 задач по SQL для собеседования аналитика

Проверь себя · 1/3разбор после ответа

В таблице products категория хранится как Books, books, BOOKS. Вы хотите отфильтровать все варианты категории «books» в одном запросе. Какое условие в WHERE наиболее надежно?

Как устроены SQL-задачи на собеседованиях

SQL — обязательная часть технического интервью для аналитика любого уровня. Формат почти всегда одинаковый: вам дают описание таблиц и просят написать запрос. Иногда на бумаге, иногда в онлайн-редакторе, иногда устно.

Интервьюер проверяет не только знание синтаксиса. Он смотрит, как вы декомпозируете задачу, какие конструкции выбираете и можете ли объяснить, что делает каждая часть запроса. Чистый, читаемый SQL ценится выше, чем «умный» однострочник.

Ниже — 30 задач, которые покрывают основные темы: GROUP BY, JOIN, подзапросы, CTE, оконные функции, CASE WHEN, работу с датами. Задачи идут от простых к сложным. Все запросы написаны для PostgreSQL.

Если вы готовитесь к собеседованию по SQL системно, эти задачи — хорошая отправная точка.

Простой уровень

Задача 1. Количество заказов по статусу

Условие. Таблица orders:

Столбец	Тип
order_id	int
user_id	int
status	varchar
created_at	timestamp

Статусы: completed, cancelled, pending. Посчитайте количество заказов в каждом статусе.

Решение.

SELECT
    status,
    COUNT(*) AS orders_count
FROM orders
GROUP BY status
ORDER BY orders_count DESC;

Простая группировка. COUNT(*) считает все строки в каждой группе, ORDER BY сортирует результат для удобства чтения.

Что проверяет: базовое владение GROUP BY и агрегатными функциями.

Задача 2. Пользователи с более чем тремя заказами

Условие. Та же таблица orders. Найдите пользователей, у которых больше трёх завершённых заказов.

Решение.

SELECT
    user_id,
    COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed'
GROUP BY user_id
HAVING COUNT(*) > 3
ORDER BY completed_orders DESC;

Сначала фильтруем строки через WHERE (только завершённые заказы), потом группируем по пользователю и оставляем только тех, у кого больше трёх заказов, через HAVING. Порядок исполнения: WHERE — GROUP BY — HAVING.

Что проверяет: разницу между WHERE и HAVING — классический вопрос на собеседовании.

Задача 3. Список товаров без заказов

Условие. Две таблицы:

products: product_id, name, category

order_items: item_id, order_id, product_id, quantity

Найдите товары, которые ни разу не заказывали.

Решение.

SELECT
    p.product_id,
    p.name,
    p.category
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.product_id IS NULL;

LEFT JOIN сохраняет все строки из левой таблицы (products). Для товаров без заказов столбцы из order_items будут NULL. Фильтр WHERE oi.product_id IS NULL оставляет именно такие строки.

Альтернатива — NOT EXISTS:

SELECT product_id, name, category
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items oi
    WHERE oi.product_id = p.product_id
);

Оба варианта корректны. На собеседовании полезно показать, что вы знаете оба подхода.

Что проверяет: понимание LEFT JOIN и работу с NULL.

Задача 4. Средний чек по месяцам

Условие. Таблица orders: order_id, user_id, amount, created_at. Посчитайте средний чек по месяцам за 2025 год.

Решение.

SELECT
    DATE_TRUNC('month', created_at) AS month,
    ROUND(AVG(amount), 2) AS avg_amount,
    COUNT(*) AS orders_count
FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at < '2026-01-01'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

DATE_TRUNC('month', ...) приводит дату к началу месяца — удобно для группировки. ROUND округляет до двух знаков. Количество заказов добавлено для контекста: средний чек по 5 заказам и по 500 — разные вещи.

Что проверяет: работу с датами и функцию DATE_TRUNC.

Задача 5. Категория с максимальной выручкой

Условие. Таблицы products (product_id, name, category) и order_items (item_id, order_id, product_id, quantity, price). Найдите категорию товаров с максимальной суммарной выручкой.

Решение.

SELECT
    p.category,
    SUM(oi.quantity * oi.price) AS total_revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY total_revenue DESC
LIMIT 1;

JOIN связывает товары с позициями заказов. Выручка — произведение количества на цену. Группировка по категории, сортировка по убыванию, LIMIT 1 берёт верхнюю строку.

Что проверяет: JOIN двух таблиц и агрегацию с вычислением.

Средний уровень

Задача 6. Выручка за текущий и предыдущий месяц

Условие. Таблица orders: order_id, amount, created_at. Для каждого месяца покажите выручку и выручку за предыдущий месяц.

Решение.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_revenue
ORDER BY month;

CTE monthly_revenue агрегирует данные по месяцам. Затем оконная функция LAG берёт значение из предыдущей строки. Для первого месяца в выборке prev_month_revenue будет NULL — это корректно.

Если интервьюер попросит добавить процент изменения:

ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
    / LAG(revenue) OVER (ORDER BY month),
    1
) AS change_pct

Что проверяет: CTE и оконную функцию LAG.

Задача 7. Топ-3 товара в каждой категории по продажам

Условие. Таблицы products и order_items (как в задаче 5). Для каждой категории найдите три товара с наибольшим количеством проданных единиц.

Решение.

WITH product_sales AS (
    SELECT
        p.category,
        p.product_id,
        p.name,
        SUM(oi.quantity) AS total_quantity
    FROM order_items oi
    JOIN products p ON oi.product_id = p.product_id
    GROUP BY p.category, p.product_id, p.name
),
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY category
            ORDER BY total_quantity DESC
        ) AS rn
    FROM product_sales
)
SELECT category, product_id, name, total_quantity
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Первый CTE считает продажи каждого товара. Второй — нумерует товары внутри каждой категории по убыванию продаж через ROW_NUMBER. Финальный SELECT оставляет только топ-3.

Почему ROW_NUMBER, а не RANK: если два товара имеют одинаковые продажи, RANK даст им одинаковый номер, и в топ-3 может попасть четыре строки. ROW_NUMBER всегда выдаёт уникальные номера — результат предсказуем.

Что проверяет: ROW_NUMBER с PARTITION BY — одна из самых частых конструкций на собеседованиях.

Задача 8. Пользователи, которые делали заказы два месяца подряд

Условие. Таблица orders: order_id, user_id, created_at. Найдите пользователей, у которых есть заказы в два последовательных календарных месяца.

Решение.

WITH user_months AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', created_at) AS order_month
    FROM orders
)
SELECT DISTINCT um1.user_id
FROM user_months um1
JOIN user_months um2
    ON um1.user_id = um2.user_id
    AND um2.order_month = um1.order_month + INTERVAL '1 month';

CTE собирает уникальные пары «пользователь — месяц». Self-join ищет случаи, когда у того же пользователя есть запись в следующем месяце. INTERVAL '1 month' корректно обрабатывает переходы между месяцами разной длины.

Альтернатива через LEAD:

WITH user_months AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', created_at) AS order_month
    FROM orders
),
with_next AS (
    SELECT
        user_id,
        order_month,
        LEAD(order_month) OVER (
            PARTITION BY user_id ORDER BY order_month
        ) AS next_month
    FROM user_months
)
SELECT DISTINCT user_id
FROM with_next
WHERE next_month = order_month + INTERVAL '1 month';

Что проверяет: self-join и работу с интервалами дат.

Задача 9. Нарастающий итог выручки

Условие. Таблица orders: order_id, amount, created_at. Для каждого заказа покажите нарастающий итог выручки с начала дня.

Решение.

SELECT
    order_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY created_at::DATE
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
ORDER BY created_at;

PARTITION BY created_at::date разбивает данные по дням — нарастающий итог обнуляется каждый новый день. ORDER BY created_at задаёт порядок суммирования. Рамка ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW явно говорит: суммируй от первой строки окна до текущей.

Рамку можно не указывать — PostgreSQL применяет её по умолчанию при наличии ORDER BY. Но на собеседовании лучше написать явно: это показывает, что вы понимаете, как работают рамки окна.

Что проверяет: оконную функцию SUM OVER с рамкой — тема, которую подробно разбирают в статье про оконные функции.

Задача 10. Сегментация пользователей по активности

Условие. Таблица orders: order_id, user_id, amount, created_at. Разделите пользователей на сегменты по количеству заказов за последние 90 дней: heavy (10+), medium (4-9), light (1-3), inactive (0).

Решение.

WITH user_orders AS (
    SELECT
        u.user_id,
        COUNT(o.order_id) AS order_count
    FROM users u
    LEFT JOIN orders o
        ON u.user_id = o.user_id
        AND o.created_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY u.user_id
)
SELECT
    user_id,
    order_count,
    CASE
        WHEN order_count >= 10 THEN 'heavy'
        WHEN order_count >= 4 THEN 'medium'
        WHEN order_count >= 1 THEN 'light'
        ELSE 'inactive'
    END AS segment
FROM user_orders
ORDER BY order_count DESC;

LEFT JOIN с условием по дате в ON — не в WHERE. Если перенести фильтр по дате в WHERE, пользователи без заказов отсеются. CASE WHEN проверяет условия сверху вниз и возвращает первое совпавшее — поэтому порядок важен.

Что проверяет: CASE WHEN, LEFT JOIN с условием в ON и разницу между ON и WHERE.

Сложный уровень

Задача 11. Retention Day 1 по когортам

Условие. Таблица user_activity: user_id, activity_date (date). Для каждой недельной когорты (по дате первого визита) посчитайте D1 retention — долю пользователей, вернувшихся на следующий день.

Решение.

WITH first_visit AS (
    SELECT
        user_id,
        MIN(activity_date) AS first_date
    FROM user_activity
    GROUP BY user_id
),
cohorts AS (
    SELECT
        user_id,
        first_date,
        DATE_TRUNC('week', first_date) AS cohort_week
    FROM first_visit
)
SELECT
    c.cohort_week,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    COUNT(DISTINCT ua.user_id) AS returned_d1,
    ROUND(
        100.0 * COUNT(DISTINCT ua.user_id) / COUNT(DISTINCT c.user_id),
        1
    ) AS d1_retention_pct
FROM cohorts c
LEFT JOIN user_activity ua
    ON c.user_id = ua.user_id
    AND ua.activity_date = c.first_date + 1
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

Шаг первый — находим дату первого визита каждого пользователя. Шаг второй — определяем когорту (неделя первого визита). Шаг третий — через LEFT JOIN ищем активность ровно на следующий день (first_date + 1). LEFT JOIN нужен, чтобы пользователи без возврата дали NULL и попали в знаменатель, но не в числитель. COUNT(DISTINCT ua.user_id) не считает NULL.

Подробнее о расчёте retention и его подводных камнях — в отдельной статье про retention.

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

Задача 12. Цепочка событий — конверсия воронки

Условие. Таблица events: user_id, event_name, event_time (timestamp). События воронки: page_view — add_to_cart — checkout — purchase. Посчитайте конверсию между каждым шагом.

Решение.

WITH funnel AS (
    SELECT
        COUNT(DISTINCT user_id) FILTER (
            WHERE event_name = 'page_view'
        ) AS step_1_page_view,
        COUNT(DISTINCT user_id) FILTER (
            WHERE event_name = 'add_to_cart'
        ) AS step_2_add_to_cart,
        COUNT(DISTINCT user_id) FILTER (
            WHERE event_name = 'checkout'
        ) AS step_3_checkout,
        COUNT(DISTINCT user_id) FILTER (
            WHERE event_name = 'purchase'
        ) AS step_4_purchase
    FROM events
)
SELECT
    step_1_page_view,
    step_2_add_to_cart,
    ROUND(100.0 * step_2_add_to_cart / NULLIF(step_1_page_view, 0), 1)
        AS conv_1_to_2_pct,
    step_3_checkout,
    ROUND(100.0 * step_3_checkout / NULLIF(step_2_add_to_cart, 0), 1)
        AS conv_2_to_3_pct,
    step_4_purchase,
    ROUND(100.0 * step_4_purchase / NULLIF(step_3_checkout, 0), 1)
        AS conv_3_to_4_pct,
    ROUND(100.0 * step_4_purchase / NULLIF(step_1_page_view, 0), 1)
        AS total_conv_pct
FROM funnel;

FILTER (WHERE ...) — PostgreSQL-расширение, позволяющее считать условную агрегацию без CASE WHEN. NULLIF(x, 0) предотвращает деление на ноль — если на каком-то шаге ноль пользователей, результат будет NULL, а не ошибка.

Это упрощённая воронка: она не проверяет порядок событий. Если нужна строгая последовательность (add_to_cart только после page_view), задача усложняется — понадобятся оконные функции или self-join.

Что проверяет: построение воронки, FILTER, защиту от деления на ноль.

Задача 13. Поиск аномалий — дни с выручкой выше двух стандартных отклонений

Условие. Таблица orders: order_id, amount, created_at. Найдите дни, в которые суммарная выручка отклоняется от среднедневной более чем на два стандартных отклонения.

Решение.

WITH daily_revenue AS (
    SELECT
        created_at::DATE AS day,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY created_at::DATE
),
stats AS (
    SELECT
        AVG(revenue) AS avg_revenue,
        STDDEV(revenue) AS stddev_revenue
    FROM daily_revenue
)
SELECT
    dr.day,
    dr.revenue,
    ROUND(s.avg_revenue, 2) AS avg_revenue,
    ROUND(
        (dr.revenue - s.avg_revenue) / NULLIF(s.stddev_revenue, 0),
        2
    ) AS z_score
FROM daily_revenue dr
CROSS JOIN stats s
WHERE ABS(dr.revenue - s.avg_revenue) > 2 * s.stddev_revenue
ORDER BY dr.day;

CTE daily_revenue считает выручку за день. CTE stats вычисляет среднее и стандартное отклонение по всем дням. CROSS JOIN присоединяет статистику к каждой строке (одна строка к каждой — CROSS JOIN здесь уместен). Фильтр оставляет только дни, где отклонение больше двух сигм. Z-score добавлен для наглядности — он показывает, на сколько стандартных отклонений день отличается от среднего.

Что проверяет: статистические функции, CROSS JOIN, аналитическое мышление.

Задача 14. Среднее время между заказами пользователя

Условие. Таблица orders: order_id, user_id, created_at. Для пользователей с двумя и более заказами посчитайте среднее количество дней между последовательными заказами.

Решение.

WITH order_gaps AS (
    SELECT
        user_id,
        created_at,
        LAG(created_at) OVER (
            PARTITION BY user_id ORDER BY created_at
        ) AS prev_order_at,
        created_at - LAG(created_at) OVER (
            PARTITION BY user_id ORDER BY created_at
        ) AS gap
    FROM orders
)
SELECT
    user_id,
    COUNT(*) + 1 AS total_orders,
    ROUND(AVG(EXTRACT(EPOCH FROM gap) / 86400), 1) AS avg_days_between
FROM order_gaps
WHERE gap IS NOT NULL
GROUP BY user_id
ORDER BY avg_days_between;

LAG берёт дату предыдущего заказа того же пользователя. Разница created_at - prev_order_at даёт интервал. EXTRACT(EPOCH FROM gap) / 86400 переводит интервал в дни (EPOCH возвращает секунды). Для первого заказа каждого пользователя gap будет NULL — он отсеивается фильтром. COUNT(*) + 1 восстанавливает реальное число заказов: у пользователя с тремя заказами будет два промежутка.

Если на собеседовании попросят медиану вместо среднего:

PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM gap) / 86400
) AS median_days_between

Что проверяет: LAG, работу с интервалами и EXTRACT.

Задача 15. Пользователи с растущей суммой заказов три месяца подряд

Условие. Таблица orders: order_id, user_id, amount, created_at. Найдите пользователей, у которых суммарная сумма заказов росла три календарных месяца подряд.

Решение.

WITH monthly_totals AS (
    SELECT
        user_id,
        DATE_TRUNC('month', created_at) AS month,
        SUM(amount) AS total_amount
    FROM orders
    GROUP BY user_id, DATE_TRUNC('month', created_at)
),
with_prev AS (
    SELECT
        user_id,
        month,
        total_amount,
        LAG(total_amount, 1) OVER w AS prev_1,
        LAG(total_amount, 2) OVER w AS prev_2,
        LAG(month, 1) OVER w AS prev_month_1,
        LAG(month, 2) OVER w AS prev_month_2
    FROM monthly_totals
    WINDOW w AS (PARTITION BY user_id ORDER BY month)
)
SELECT DISTINCT user_id
FROM with_prev
WHERE
    -- три последовательных месяца
    prev_month_1 = month - INTERVAL '1 month'
    AND prev_month_2 = month - INTERVAL '2 months'
    -- строго растущая последовательность
    AND total_amount > prev_1
    AND prev_1 > prev_2;

Первый CTE агрегирует суммы по пользователю и месяцу. Второй — через LAG с разными смещениями достаёт значения за один и два месяца назад. Проверки prev_month_1 = month - INTERVAL '1 month' гарантируют, что месяцы идут подряд без пропусков. Без этой проверки запрос мог бы ошибочно сравнить январь с мартом, пропустив февраль.

Конструкция WINDOW w AS (...) — именованное окно. Она убирает дублирование определения окна в нескольких вызовах LAG.

Что проверяет: LAG с разными смещениями, именованные окна, проверку последовательности периодов.

Задача 16. Скользящее среднее выручки за 7 дней

Условие. Таблица orders: order_id, amount, created_at. Для каждого дня посчитайте суммарную выручку и скользящее среднее выручки за последние 7 дней (включая текущий).

Решение.

WITH daily_revenue AS (
    SELECT
        created_at::DATE AS day,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY created_at::DATE
)
SELECT
    day,
    revenue,
    ROUND(
        AVG(revenue) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS moving_avg_7d
FROM daily_revenue
ORDER BY day;

CTE агрегирует выручку по дням. Оконная функция AVG с рамкой ROWS BETWEEN 6 PRECEDING AND CURRENT ROW считает среднее по текущей строке и шести предыдущим — итого 7 дней. Важно: ROWS считает именно строки, а не календарные дни. Если в данных есть пропущенные дни, среднее будет по 7 строкам, а не по 7 календарным дням. Для строгого календарного окна понадобится предварительно заполнить пропуски через генерацию дат.

Что проверяет: оконные рамки (window frames) — ROWS BETWEEN ... AND ....

Задача 17. Медианная зарплата по отделам

Условие. Таблица employees: employee_id, name, department, salary. Для каждого отдела найдите медианную зарплату.

Решение.

SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary,
    ROUND(AVG(salary), 2) AS avg_salary,
    COUNT(*) AS employees_count
FROM employees
GROUP BY department
ORDER BY median_salary DESC;

PERCENTILE_CONT(0.5) — функция упорядоченного набора (ordered-set aggregate). Она вычисляет значение на указанном перцентиле: 0.5 — медиана. В отличие от AVG, медиана устойчива к выбросам: один сотрудник с зарплатой в 10 раз выше средней не сдвинет медиану. Для сравнения в запрос добавлено среднее — разрыв между median_salary и avg_salary покажет, есть ли в отделе выбросы.

PERCENTILE_CONT возвращает интерполированное значение (тип double precision). Если нужно ближайшее реальное значение из набора — используйте PERCENTILE_DISC.

Что проверяет: ordered-set aggregate функции, понимание разницы между средним и медианой.

Задача 18. Генерация серии дат через рекурсивный CTE

Условие. Напишите запрос, который генерирует все даты между '2025-01-01' и '2025-01-31'. Затем для каждой даты покажите количество заказов из таблицы orders (order_id, created_at), включая дни с нулём заказов.

Решение.

WITH RECURSIVE date_series AS (
    SELECT DATE '2025-01-01' AS day
    UNION ALL
    SELECT day + 1
    FROM date_series
    WHERE day < DATE '2025-01-31'
)
SELECT
    ds.day,
    COUNT(o.order_id) AS orders_count
FROM date_series ds
LEFT JOIN orders o ON o.created_at::DATE = ds.day
GROUP BY ds.day
ORDER BY ds.day;

Рекурсивный CTE начинается с якорного запроса (SELECT DATE '2025-01-01') и рекурсивной части, которая добавляет один день на каждой итерации, пока не дойдёт до конца диапазона. LEFT JOIN гарантирует, что дни без заказов попадут в результат с orders_count = 0.

В PostgreSQL для генерации дат проще использовать generate_series('2025-01-01'::date, '2025-01-31'::date, '1 day'). Но на собеседованиях часто просят именно рекурсивный CTE — он работает в любой СУБД, поддерживающей стандарт SQL:1999.

Что проверяет: рекурсивные CTE, заполнение пропусков в данных.

Задача 19. Топ-2 товара в каждой категории через LATERAL JOIN

Условие. Таблицы products (product_id, name, category) и order_items (item_id, order_id, product_id, quantity, price). Для каждой категории найдите два товара с максимальной выручкой. Используйте LATERAL JOIN.

Решение.

SELECT
    c.category,
    t.product_id,
    t.name,
    t.total_revenue
FROM (SELECT DISTINCT category FROM products) c
CROSS JOIN LATERAL (
    SELECT
        p.product_id,
        p.name,
        SUM(oi.quantity * oi.price) AS total_revenue
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    WHERE p.category = c.category
    GROUP BY p.product_id, p.name
    ORDER BY total_revenue DESC
    LIMIT 2
) t
ORDER BY c.category, t.total_revenue DESC;

LATERAL позволяет подзапросу ссылаться на столбцы из предшествующих таблиц — здесь c.category. Для каждой категории подзапрос выполняется отдельно, возвращая ровно 2 строки. Это альтернатива подходу с ROW_NUMBER из задачи 7. LATERAL бывает удобнее, когда нужен LIMIT внутри группы, и часто работает быстрее на больших данных, потому что не требует нумерации всех строк.

Что проверяет: LATERAL JOIN — продвинутая конструкция, которая отличает кандидата среднего уровня от начинающего.

Задача 20. Поворот данных с CASE WHEN (кросс-таблица)

Условие. Таблица sales: sale_id, product_category, sale_month (date), revenue. Категории: electronics, clothing, food. Постройте таблицу, где строки — месяцы, а столбцы — выручка по каждой категории.

Решение.

SELECT
    DATE_TRUNC('month', sale_month) AS month,
    SUM(CASE WHEN product_category = 'electronics' THEN revenue ELSE 0 END)
        AS electronics,
    SUM(CASE WHEN product_category = 'clothing' THEN revenue ELSE 0 END)
        AS clothing,
    SUM(CASE WHEN product_category = 'food' THEN revenue ELSE 0 END)
        AS food,
    SUM(revenue) AS total
FROM sales
GROUP BY DATE_TRUNC('month', sale_month)
ORDER BY month;

Классический приём pivot без расширений: CASE WHEN внутри агрегатной функции распределяет значения по столбцам. ELSE 0 гарантирует, что в месяцы без продаж конкретной категории будет 0, а не NULL. Столбец total добавлен для контроля: сумма по категориям должна совпадать с итогом.

Этот подход требует перечисления всех категорий в запросе. Если категории динамические, в PostgreSQL можно использовать расширение tablefunc с функцией crosstab, но на собеседованиях обычно ожидают именно вариант с CASE WHEN.

Что проверяет: поворот (pivot) данных — частая задача при подготовке отчётов.

Задача 21. Нарастающий итог с обнулением по месяцам

Условие. Таблица orders: order_id, amount, created_at. Для каждого заказа покажите нарастающий итог выручки с начала календарного месяца. Итог должен обнуляться в первый день каждого нового месяца.

Решение.

SELECT
    order_id,
    created_at,
    amount,
    SUM(amount) OVER (
        PARTITION BY DATE_TRUNC('month', created_at)
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS mtd_running_total
FROM orders
ORDER BY created_at;

PARTITION BY DATE_TRUNC('month', created_at) разбивает окно по месяцам — нарастающий итог обнуляется на границе каждого месяца. Это типичная задача для месячных KPI-дашбордов, где нужно отслеживать прогресс к плану внутри месяца (MTD — month-to-date).

Если заменить DATE_TRUNC('month', ...) на DATE_TRUNC('quarter', ...), получится квартальный нарастающий итог (QTD). Принцип тот же — меняется только ключ партиционирования.

Что проверяет: PARTITION BY в оконных функциях, понимание сброса нарастающего итога.

Задача 22. Поиск пропущенных дней активности

Условие. Таблица user_activity: user_id, activity_date (date). Для пользователя с user_id = 1 найдите все дни, в которые у него не было активности, в пределах его периода активности (от первого до последнего визита).

Решение.

WITH bounds AS (
    SELECT
        MIN(activity_date) AS first_day,
        MAX(activity_date) AS last_day
    FROM user_activity
    WHERE user_id = 1
),
all_days AS (
    SELECT generate_series(first_day, last_day, '1 day'::INTERVAL)::DATE AS day
    FROM bounds
)
SELECT ad.day AS missing_date
FROM all_days ad
LEFT JOIN user_activity ua
    ON ua.activity_date = ad.day
    AND ua.user_id = 1
WHERE ua.activity_date IS NULL
ORDER BY ad.day;

Сначала определяем границы активности пользователя. Затем через generate_series создаём полный набор дат. LEFT JOIN с таблицей активности и фильтр IS NULL оставляют только дни без записей.

Этот паттерн — «антисоединение с эталонным набором» — применим для любых задач на поиск пропусков: недостающие номера заказов, часы без событий, месяцы без транзакций.

Что проверяет: поиск пропусков в последовательностях, generate_series, anti-join паттерн.

Задача 23. Пары пользователей с одинаковыми покупками

Условие. Таблица purchases: user_id, product_id. Найдите все пары пользователей, которые купили хотя бы три одинаковых товара.

Решение.

SELECT
    p1.user_id AS user_1,
    p2.user_id AS user_2,
    COUNT(DISTINCT p1.product_id) AS common_products
FROM purchases p1
JOIN purchases p2
    ON p1.product_id = p2.product_id
    AND p1.user_id < p2.user_id
GROUP BY p1.user_id, p2.user_id
HAVING COUNT(DISTINCT p1.product_id) >= 3
ORDER BY common_products DESC;

Self-join таблицы purchases с самой собой по product_id находит совпадения покупок. Условие p1.user_id < p2.user_id исключает дубли: пара (1, 2) останется, а (2, 1) — нет. COUNT(DISTINCT p1.product_id) считает уникальные общие товары — DISTINCT нужен, если пользователь мог купить один товар несколько раз. HAVING >= 3 фильтрует только пары с тремя и более совпадениями.

Этот подход применяется в рекомендательных системах для поиска «похожих» пользователей (collaborative filtering).

Что проверяет: self-join, устранение дубликатов пар, HAVING с COUNT DISTINCT.

Задача 24. Пользователи, которые заходили, но не покупали

Условие. Таблица visits: user_id, visit_date. Таблица purchases: user_id, purchase_date. Найдите пользователей, которые заходили на сайт в январе 2025, но не совершили ни одной покупки в этом же месяце.

Решение.

SELECT DISTINCT user_id
FROM visits
WHERE visit_date >= '2025-01-01'
  AND visit_date < '2025-02-01'

EXCEPT

SELECT DISTINCT user_id
FROM purchases
WHERE purchase_date >= '2025-01-01'
  AND purchase_date < '2025-02-01'

ORDER BY user_id;

EXCEPT возвращает строки из первого запроса, которых нет во втором. Это самый читаемый способ выразить операцию «есть в A, но нет в B». Альтернативы — NOT EXISTS или LEFT JOIN ... WHERE IS NULL — работают так же, но EXCEPT проще читать и писать.

Обратная операция — INTERSECT — вернёт пользователей, которые и заходили, и покупали.

Нюанс: EXCEPT сравнивает строки целиком и автоматически убирает дубликаты (как DISTINCT). Если нужны все строки, включая дубли, используйте EXCEPT ALL.

Что проверяет: операции над множествами — EXCEPT, INTERSECT, UNION.

Задача 25. Список товаров в одной строке через STRING_AGG

Условие. Таблицы orders (order_id, user_id, created_at) и order_items (item_id, order_id, product_id) и products (product_id, name). Для каждого заказа покажите список купленных товаров в одной строке, через запятую, отсортированный по алфавиту.

Решение.

SELECT
    o.order_id,
    o.created_at,
    STRING_AGG(p.name, ', ' ORDER BY p.name) AS product_list,
    COUNT(*) AS items_count
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id, o.created_at
ORDER BY o.created_at DESC;

STRING_AGG(expression, delimiter ORDER BY ...) — агрегатная функция, которая склеивает строковые значения в одну строку с указанным разделителем. ORDER BY внутри функции задаёт порядок элементов — здесь по алфавиту. Без ORDER BY порядок будет произвольным.

Если в заказе могут быть дубликаты товаров (например, два одинаковых товара в разных позициях), добавьте DISTINCT: STRING_AGG(DISTINCT p.name, ', ' ORDER BY p.name).

Что проверяет: STRING_AGG с сортировкой — строковая агрегация, часто нужная в отчётах.

Задача 26. Разница между ROW_NUMBER, RANK и DENSE_RANK

Условие. Таблица exam_results: student_id, subject, score. Для предмета math пронумеруйте студентов по убыванию балла тремя способами: ROW_NUMBER, RANK, DENSE_RANK. Покажите разницу на данных с одинаковыми баллами.

Решение.

SELECT
    student_id,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK()       OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM exam_results
WHERE subject = 'math'
ORDER BY score DESC;

Допустим, баллы: 95, 90, 90, 85. Тогда:

score	row_num	rank	dense_rank
95	1	1	1
90	2	2	2
90	3	2	2
85	4	4	3

ROW_NUMBER всегда уникален — при одинаковых баллах порядок между ними недетерминирован. RANK даёт одинаковый номер, но пропускает следующий (после двух вторых мест сразу четвёртое). DENSE_RANK даёт одинаковый номер без пропуска (после двух вторых — третье).

На практике: ROW_NUMBER — для пагинации и выбора «ровно N строк». RANK — для спортивных рейтингов. DENSE_RANK — когда нужно знать, сколько уникальных позиций в рейтинге.

Что проверяет: понимание трёх ранжирующих функций — одна из самых частых тем на собеседованиях.

Задача 27. Условная агрегация с FILTER

Условие. Таблица events: user_id, event_name, platform, event_time. Платформы: ios, android, web. Для каждого пользователя посчитайте количество событий на каждой платформе и общее число событий.

Решение.

SELECT
    user_id,
    COUNT(*) AS total_events,
    COUNT(*) FILTER (WHERE platform = 'ios') AS ios_events,
    COUNT(*) FILTER (WHERE platform = 'android') AS android_events,
    COUNT(*) FILTER (WHERE platform = 'web') AS web_events,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE platform = 'web') / COUNT(*),
        1
    ) AS web_share_pct
FROM events
GROUP BY user_id
ORDER BY total_events DESC;

FILTER (WHERE ...) — PostgreSQL-расширение стандарта SQL, которое позволяет фильтровать строки для каждой агрегатной функции отдельно. Это чище и читаемее, чем эквивалент через CASE WHEN:

-- менее читаемый эквивалент
SUM(CASE WHEN platform = 'ios' THEN 1 ELSE 0 END) AS ios_events

FILTER работает с любой агрегатной функцией: SUM, AVG, COUNT, ARRAY_AGG и другими. Добавленный столбец web_share_pct показывает долю веб-событий — пример совмещения условной агрегации с вычислениями.

Что проверяет: условную агрегацию через FILTER — идиоматический PostgreSQL.

Задача 28. Определение пользовательских сессий по событиям

Условие. Таблица events: user_id, event_name, event_time (timestamp). Сессия — последовательность событий одного пользователя, в которой интервал между соседними событиями не превышает 30 минут. Присвойте каждому событию номер сессии.

Решение.

WITH with_gap AS (
    SELECT
        user_id,
        event_name,
        event_time,
        CASE
            WHEN event_time - LAG(event_time) OVER (
                PARTITION BY user_id ORDER BY event_time
            ) > INTERVAL '30 minutes'
            THEN 1
            ELSE 0
        END AS new_session
    FROM events
),
with_session AS (
    SELECT
        *,
        SUM(new_session) OVER (
            PARTITION BY user_id
            ORDER BY event_time
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) + 1 AS session_id
    FROM with_gap
)
SELECT
    user_id,
    session_id,
    event_name,
    event_time
FROM with_session
ORDER BY user_id, event_time;

Алгоритм в два шага. Первый CTE: через LAG смотрим на время предыдущего события. Если разрыв больше 30 минут, помечаем строку как начало новой сессии (new_session = 1). Второй CTE: кумулятивная сумма флагов new_session даёт номер сессии. + 1 нужен, потому что первое событие пользователя не помечается как новая сессия (у него нет предыдущего).

Этот паттерн — «sessionization» — стандартная задача продуктовой аналитики. Его используют в Google Analytics, Amplitude и других системах для определения сессий.

Что проверяет: комбинацию LAG и кумулятивной суммы для сегментации временных рядов.

Задача 29. В каком перцентиле находится пользователь

Условие. Таблица user_metrics: user_id, total_spent (numeric). Для каждого пользователя определите, в каком перцентиле он находится по суммарным тратам.

Решение.

SELECT
    user_id,
    total_spent,
    ROUND(
        100.0 * CUME_DIST() OVER (ORDER BY total_spent),
        1
    ) AS percentile,
    NTILE(100) OVER (ORDER BY total_spent) AS percentile_bucket
FROM user_metrics
ORDER BY total_spent DESC;

CUME_DIST() возвращает кумулятивное распределение — долю строк, значение которых меньше или равно текущему. Умножение на 100 переводит в проценты. Пользователь с percentile = 95.0 тратит больше, чем 95% остальных.

NTILE(100) — альтернативный подход: делит все строки на 100 равных «вёдер». Значение 95 означает, что пользователь попал в 95-й из 100 бакетов. Разница: CUME_DIST точнее при малых выборках, NTILE удобнее для грубого разбиения.

Практическое применение: сегментация пользователей (топ-10% vs остальные), определение порогов для уровней лояльности, поиск выбросов.

Что проверяет: CUME_DIST, NTILE — функции распределения, которые редко используют начинающие.

Задача 30. Обнаружение N+1 паттерна в логах запросов

Условие. Таблица query_log: log_id, session_id, query_text, executed_at (timestamp), duration_ms (int). Найдите сессии, в которых один и тот же запрос (по тексту) выполнялся более 10 раз подряд в течение одной секунды — это признак N+1 проблемы.

Решение.

WITH repeated_queries AS (
    SELECT
        session_id,
        query_text,
        COUNT(*) AS exec_count,
        MIN(executed_at) AS first_exec,
        MAX(executed_at) AS last_exec,
        SUM(duration_ms) AS total_duration_ms
    FROM query_log
    GROUP BY session_id, query_text
    HAVING COUNT(*) > 10
),
suspicious AS (
    SELECT
        *,
        EXTRACT(EPOCH FROM last_exec - first_exec) AS span_seconds
    FROM repeated_queries
)
SELECT
    session_id,
    query_text,
    exec_count,
    total_duration_ms,
    ROUND(span_seconds::numeric, 2) AS span_seconds
FROM suspicious
WHERE span_seconds <= 1
ORDER BY exec_count DESC;

Первый CTE группирует запросы по сессии и тексту, оставляя только те, что выполнялись больше 10 раз. Второй CTE вычисляет временной интервал между первым и последним выполнением. Финальный фильтр оставляет случаи, где все повторения уложились в одну секунду — типичный признак N+1: ORM в цикле отправляет один и тот же SELECT для каждой связанной записи.

В реальной практике query_text может содержать разные параметры. Тогда перед группировкой нужно нормализовать запрос — например, заменить числовые литералы на плейсхолдеры через REGEXP_REPLACE(query_text, '\d+', '?', 'g').

Что проверяет: аналитику логов, группировку с HAVING, работу с временными интервалами, понимание проблемы N+1.

Прокачай SQL для собеса

500+ задач по SQL: оконные функции, JOIN, CTE — с разбором каждой

Тренировать SQL в Telegram

Экспертный уровень

Задача 31. Воронка с строгим порядком событий

Условие. Таблица events содержит действия пользователей на сайте:

CREATE TABLE events (
    event_id   BIGINT PRIMARY KEY,
    user_id    INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,  -- 'page_view', 'add_to_cart', 'purchase'
    event_time TIMESTAMP NOT NULL
);

Постройте воронку конверсии: page_view -> add_to_cart -> purchase. Учитывайте только те цепочки, где события произошли строго в указанном порядке (каждое следующее событие позже предыдущего). Выведите количество уникальных пользователей на каждом шаге и процент конверсии относительно первого шага.

Решение.

WITH ordered AS (
    SELECT
        user_id,
        event_type,
        event_time,
        ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY event_time) AS rn
    FROM events
    WHERE event_type IN ('page_view', 'add_to_cart', 'purchase')
),
first_events AS (
    SELECT user_id, event_type, event_time
    FROM ordered
    WHERE rn = 1
),
pivoted AS (
    SELECT
        user_id,
        MAX(event_time) FILTER (WHERE event_type = 'page_view')    AS pv_time,
        MAX(event_time) FILTER (WHERE event_type = 'add_to_cart')  AS atc_time,
        MAX(event_time) FILTER (WHERE event_type = 'purchase')     AS pur_time
    FROM first_events
    GROUP BY user_id
),
funnel AS (
    SELECT
        COUNT(*)                                                         AS step1_page_view,
        COUNT(*) FILTER (WHERE atc_time > pv_time)                      AS step2_add_to_cart,
        COUNT(*) FILTER (WHERE atc_time > pv_time AND pur_time > atc_time) AS step3_purchase
    FROM pivoted
    WHERE pv_time IS NOT NULL
)
SELECT
    step1_page_view,
    step2_add_to_cart,
    step3_purchase,
    ROUND(100.0 * step2_add_to_cart / NULLIF(step1_page_view, 0), 1) AS pv_to_atc_pct,
    ROUND(100.0 * step3_purchase    / NULLIF(step1_page_view, 0), 1) AS pv_to_pur_pct
FROM funnel;

Решение берёт первое вхождение каждого типа события для каждого пользователя, затем разворачивает строки в столбцы через FILTER. Условие строгого порядка проверяется сравнением временных меток: atc_time > pv_time и pur_time > atc_time. Благодаря этому в воронку попадают только корректные последовательности.

Что проверяет: конструкция FILTER (WHERE ...), воронка с проверкой порядка, агрегация с условием, NULLIF для защиты от деления на ноль.

Задача 32. Сессионизация потока событий

Условие. Таблица clickstream содержит клики пользователей:

CREATE TABLE clickstream (
    click_id   BIGINT PRIMARY KEY,
    user_id    INT NOT NULL,
    url        TEXT NOT NULL,
    clicked_at TIMESTAMP NOT NULL
);

Разбейте поток событий на сессии: новая сессия начинается, если между двумя последовательными кликами пользователя прошло более 30 минут. Присвойте каждому клику номер сессии и выведите user_id, click_id, clicked_at, session_id.

Решение.

WITH with_prev AS (
    SELECT
        click_id,
        user_id,
        url,
        clicked_at,
        LAG(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at) AS prev_clicked_at
    FROM clickstream
),
with_flag AS (
    SELECT
        *,
        CASE
            WHEN prev_clicked_at IS NULL
              OR clicked_at - prev_clicked_at > INTERVAL '30 minutes'
            THEN 1
            ELSE 0
        END AS new_session_flag
    FROM with_prev
)
SELECT
    user_id,
    click_id,
    clicked_at,
    SUM(new_session_flag) OVER (PARTITION BY user_id ORDER BY clicked_at) AS session_id
FROM with_flag
ORDER BY user_id, clicked_at;

Алгоритм: с помощью LAG вычисляем время предыдущего клика. Если разрыв превышает 30 минут (или это первый клик), ставим флаг 1. Кумулятивная сумма флагов через SUM() OVER(...) формирует номер сессии. Это классический паттерн сессионизации, применяемый в веб-аналитике.

Что проверяет: LAG, кумулятивная оконная функция SUM() OVER, работа с интервалами, паттерн «gaps and islands».

Задача 33. Рост год к году по категориям

Условие. Таблица sales:

CREATE TABLE sales (
    sale_id     BIGINT PRIMARY KEY,
    category    VARCHAR(100) NOT NULL,
    sale_date   DATE NOT NULL,
    revenue     NUMERIC(12,2) NOT NULL
);

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

Решение.

WITH yearly AS (
    SELECT
        category,
        EXTRACT(YEAR FROM sale_date)::INT AS yr,
        SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY category, EXTRACT(YEAR FROM sale_date)
)
SELECT
    category,
    yr,
    total_revenue,
    prev_revenue,
    ROUND(100.0 * (total_revenue - prev_revenue) / prev_revenue, 1) AS yoy_growth_pct
FROM (
    SELECT
        category,
        yr,
        total_revenue,
        LAG(total_revenue) OVER (PARTITION BY category ORDER BY yr) AS prev_revenue
    FROM yearly
) sub
WHERE prev_revenue IS NOT NULL
ORDER BY category, yr;

Сначала агрегируем выручку по категории и году. Затем LAG даёт выручку предыдущего года в рамках категории. Рост вычисляется как (текущий - предыдущий) / предыдущий * 100. Фильтр WHERE prev_revenue IS NOT NULL убирает первый год, для которого сравнение невозможно.

Что проверяет: LAG с PARTITION BY, агрегация по временным периодам, вычисление YoY-метрик.

Задача 34. Средневзвешенное значение

Условие. Таблица product_reviews содержит оценки товаров, а reviewers — авторитетность рецензента:

CREATE TABLE product_reviews (
    review_id    BIGINT PRIMARY KEY,
    product_id   INT NOT NULL,
    reviewer_id  INT NOT NULL,
    rating       NUMERIC(2,1) NOT NULL  -- от 1.0 до 5.0
);

CREATE TABLE reviewers (
    reviewer_id  INT PRIMARY KEY,
    trust_score  NUMERIC(4,2) NOT NULL  -- вес рецензента, от 0.01 до 10.00
);

Рассчитайте средневзвешенный рейтинг каждого товара, где весом является trust_score рецензента. Выведите product_id, количество отзывов, простой средний рейтинг и взвешенный рейтинг.

Решение.

SELECT
    pr.product_id,
    COUNT(*)                                                       AS review_count,
    ROUND(AVG(pr.rating), 2)                                       AS avg_rating,
    ROUND(
        SUM(pr.rating * r.trust_score) / NULLIF(SUM(r.trust_score), 0),
        2
    )                                                              AS weighted_avg_rating
FROM product_reviews pr
JOIN reviewers r USING (reviewer_id)
GROUP BY pr.product_id
ORDER BY weighted_avg_rating DESC;

Средневзвешенное рассчитывается как сумма произведений значения на вес, делённая на сумму весов: SUM(rating * weight) / SUM(weight). Это отличается от простого AVG, где все наблюдения равнозначны. На практике такой подход используется для учёта авторитетности источника, размера выборки и других факторов.

Что проверяет: средневзвешенное через SUM/SUM, отличие от AVG, NULLIF для защиты от нуля, JOIN и агрегация.

Задача 35. Первое вхождение N подряд идущих дней активности

Условие. Таблица user_activity:

CREATE TABLE user_activity (
    user_id      INT NOT NULL,
    activity_date DATE NOT NULL,
    UNIQUE (user_id, activity_date)
);

Для каждого пользователя найдите дату начала первой серии из 7 или более подряд идущих дней активности. Если такой серии нет, пользователь не попадает в результат.

Решение.

WITH grouped AS (
    SELECT
        user_id,
        activity_date,
        activity_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date))::INT AS grp
    FROM user_activity
),
streaks AS (
    SELECT
        user_id,
        grp,
        MIN(activity_date) AS streak_start,
        MAX(activity_date) AS streak_end,
        COUNT(*)           AS streak_length
    FROM grouped
    GROUP BY user_id, grp
    HAVING COUNT(*) >= 7
),
ranked AS (
    SELECT
        user_id,
        streak_start,
        streak_length,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY streak_start) AS rn
    FROM streaks
)
SELECT user_id, streak_start, streak_length
FROM ranked
WHERE rn = 1
ORDER BY user_id;

Классический приём: если из даты вычесть порядковый номер строки, то у последовательных дат разность будет одинаковой. Группировка по этому значению выделяет непрерывные серии. Далее фильтруем серии длиной >= 7 и берём самую раннюю через ROW_NUMBER.

Что проверяет: паттерн «islands» (вычитание ROW_NUMBER из даты), HAVING, вложенные CTE, ранжирование для выбора первого элемента.

Задача 36. Анализ потребительской корзины

Условие. Таблица order_items:

CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Найдите 10 самых частых пар товаров, которые покупают вместе (в одном заказе). Выведите product_a, product_b и количество совместных покупок. Пара должна быть уникальной (без дублей вида A-B и B-A).

Решение.

SELECT
    a.product_id AS product_a,
    b.product_id AS product_b,
    COUNT(*)     AS times_bought_together
FROM order_items a
JOIN order_items b
    ON  a.order_id = b.order_id
    AND a.product_id < b.product_id
GROUP BY a.product_id, b.product_id
ORDER BY times_bought_together DESC
LIMIT 10;

Самосоединение таблицы order_items по order_id генерирует все пары товаров в рамках заказа. Условие a.product_id < b.product_id исключает дубликаты и пары товара с самим собой. Это базовая техника market basket analysis, которую на больших объёмах данных оптимизируют алгоритмом Apriori, но в SQL для аналитических задач самосоединение работает эффективно.

Что проверяет: self-join, устранение дублей через неравенство, агрегация пар, понимание комбинаторики.

Задача 37. Расчёт Net Promoter Score (NPS)

Условие. Таблица nps_survey:

CREATE TABLE nps_survey (
    response_id  BIGINT PRIMARY KEY,
    user_id      INT NOT NULL,
    score        INT NOT NULL CHECK (score BETWEEN 0 AND 10),
    survey_date  DATE NOT NULL
);

Рассчитайте NPS по месяцам. Напомним: оценки 9-10 — промоутеры, 7-8 — нейтралы, 0-6 — детракторы. NPS = % промоутеров - % детракторов.

Решение.

SELECT
    DATE_TRUNC('month', survey_date)::DATE AS survey_month,
    COUNT(*) AS responses,
    ROUND(100.0 * COUNT(*) FILTER (WHERE score >= 9) / COUNT(*), 1) AS promoter_pct,
    ROUND(100.0 * COUNT(*) FILTER (WHERE score <= 6) / COUNT(*), 1) AS detractor_pct,
    ROUND(
        100.0 * COUNT(*) FILTER (WHERE score >= 9) / COUNT(*)
      - 100.0 * COUNT(*) FILTER (WHERE score <= 6) / COUNT(*),
        1
    ) AS nps
FROM nps_survey
GROUP BY DATE_TRUNC('month', survey_date)
ORDER BY survey_month;

NPS — ключевая продуктовая метрика. В SQL её удобно считать через FILTER (WHERE ...), доступный в PostgreSQL. Каждая группа (промоутеры, нейтралы, детракторы) подсчитывается отдельно, а NPS вычисляется как разность долей. Значение может варьироваться от -100 (все детракторы) до +100 (все промоутеры).

Что проверяет: FILTER, DATE_TRUNC, понимание бизнес-метрики NPS, условная агрегация.

Задача 38. Среднее время от регистрации до первой покупки

Условие. Таблицы:

CREATE TABLE users (
    user_id      INT PRIMARY KEY,
    signed_up_at TIMESTAMP NOT NULL
);

CREATE TABLE purchases (
    purchase_id  BIGINT PRIMARY KEY,
    user_id      INT NOT NULL REFERENCES users(user_id),
    purchased_at TIMESTAMP NOT NULL,
    amount       NUMERIC(10,2) NOT NULL
);

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

Решение.

WITH first_purchase AS (
    SELECT
        user_id,
        MIN(purchased_at) AS first_purchased_at
    FROM purchases
    GROUP BY user_id
),
time_to_purchase AS (
    SELECT
        u.user_id,
        EXTRACT(EPOCH FROM fp.first_purchased_at - u.signed_up_at) / 3600.0 AS hours_to_purchase
    FROM users u
    JOIN first_purchase fp USING (user_id)
)
SELECT
    COUNT(*)                                          AS users_with_purchase,
    ROUND(AVG(hours_to_purchase)::NUMERIC, 1)         AS avg_hours,
    ROUND(
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hours_to_purchase)::NUMERIC,
        1
    )                                                 AS median_hours
FROM time_to_purchase;

Сначала находим время первой покупки каждого пользователя. Затем вычисляем разницу в часах через EXTRACT(EPOCH FROM interval). Медиана рассчитывается функцией PERCENTILE_CONT(0.5). Сравнение среднего и медианы показывает асимметрию распределения: если среднее значительно больше медианы, значит, есть «хвост» пользователей с очень долгим временем до покупки.

Что проверяет: EXTRACT(EPOCH FROM ...), PERCENTILE_CONT, MIN для первого события, разница между средним и медианой.

Задача 39. Обнаружение дублирующихся транзакций

Условие. Таблица transactions:

CREATE TABLE transactions (
    txn_id     BIGINT PRIMARY KEY,
    user_id    INT NOT NULL,
    amount     NUMERIC(10,2) NOT NULL,
    merchant   VARCHAR(200) NOT NULL,
    txn_time   TIMESTAMP NOT NULL
);

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

Решение.

WITH with_prev AS (
    SELECT
        txn_id,
        user_id,
        amount,
        merchant,
        txn_time,
        LAG(txn_id)   OVER w AS prev_txn_id,
        LAG(txn_time)  OVER w AS prev_txn_time
    FROM transactions
    WINDOW w AS (PARTITION BY user_id, amount, merchant ORDER BY txn_time)
),
suspect AS (
    SELECT txn_id
    FROM with_prev
    WHERE prev_txn_time IS NOT NULL
      AND txn_time - prev_txn_time <= INTERVAL '5 minutes'
    UNION
    SELECT prev_txn_id
    FROM with_prev
    WHERE prev_txn_time IS NOT NULL
      AND txn_time - prev_txn_time <= INTERVAL '5 minutes'
)
SELECT t.*
FROM transactions t
JOIN suspect s ON t.txn_id = s.txn_id
ORDER BY t.user_id, t.merchant, t.amount, t.txn_time;

Решение использует LAG с партицированием по (user_id, amount, merchant). Если разница во времени между соседними транзакциями не превышает 5 минут, обе помечаются как подозрительные. UNION собирает как текущие, так и предыдущие транзакции из пар-дублей. Этот паттерн широко применяется в антифрод-системах.

Что проверяет: LAG с составным PARTITION BY, именованное окно WINDOW, работа с интервалами, UNION для сбора обеих сторон пары.

Задача 40. Рекурсивный CTE для организационной иерархии

Условие. Таблица employees:

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    manager_id    INT REFERENCES employees(employee_id),
    salary        NUMERIC(10,2) NOT NULL
);

Для заданного руководителя (например, employee_id = 1) постройте полное дерево подчинённых с указанием уровня вложенности и полного пути от корня. Также посчитайте суммарный фонд оплаты труда (ФОТ) всего поддерева.

Решение.

WITH RECURSIVE org_tree AS (
    -- Якорь: сам руководитель
    SELECT
        employee_id,
        name,
        manager_id,
        salary,
        0                          AS depth,
        name::TEXT                 AS path
    FROM employees
    WHERE employee_id = 1

    UNION ALL

    -- Рекурсия: подчинённые
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        e.salary,
        ot.depth + 1,
        ot.path || ' > ' || e.name
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.employee_id
)
SELECT
    employee_id,
    REPEAT('  ', depth) || name AS indented_name,
    depth,
    path,
    salary
FROM org_tree
ORDER BY path;

-- Суммарный ФОТ поддерева
-- SELECT SUM(salary) AS total_payroll FROM org_tree;

Рекурсивный CTE состоит из якорного запроса (корневой сотрудник) и рекурсивной части (присоединение подчинённых через manager_id). Столбец path формирует полный путь конкатенацией имён. REPEAT создаёт визуальный отступ. Для больших иерархий рекомендуется добавлять ограничение глубины (WHERE depth < 20) во избежание бесконечной рекурсии.

Что проверяет: WITH RECURSIVE, якорь и рекурсивный член, конкатенация пути, работа с иерархическими данными.

Задача 41. Построение таблицы признаков для прогноза оттока

Условие. Таблицы:

CREATE TABLE users (
    user_id      INT PRIMARY KEY,
    signed_up_at DATE NOT NULL
);

CREATE TABLE events (
    event_id    BIGINT PRIMARY KEY,
    user_id     INT NOT NULL,
    event_type  VARCHAR(50) NOT NULL,
    event_time  TIMESTAMP NOT NULL
);

CREATE TABLE purchases (
    purchase_id  BIGINT PRIMARY KEY,
    user_id      INT NOT NULL,
    amount       NUMERIC(10,2) NOT NULL,
    purchased_at TIMESTAMP NOT NULL
);

Постройте таблицу признаков (feature table) для модели прогнозирования оттока. Для каждого пользователя рассчитайте: количество событий за последние 7 и 30 дней, количество покупок за 30 дней, средний чек, дни с момента последней активности, дни с момента регистрации. Опорная дата — CURRENT_DATE.

Решение.

WITH event_features AS (
    SELECT
        user_id,
        COUNT(*) FILTER (WHERE event_time >= CURRENT_DATE - INTERVAL '7 days')  AS events_7d,
        COUNT(*) FILTER (WHERE event_time >= CURRENT_DATE - INTERVAL '30 days') AS events_30d,
        MAX(event_time) AS last_event_time
    FROM events
    GROUP BY user_id
),
purchase_features AS (
    SELECT
        user_id,
        COUNT(*) FILTER (WHERE purchased_at >= CURRENT_DATE - INTERVAL '30 days') AS purchases_30d,
        AVG(amount) FILTER (WHERE purchased_at >= CURRENT_DATE - INTERVAL '30 days') AS avg_check_30d,
        MAX(purchased_at) AS last_purchase_time
    FROM purchases
    GROUP BY user_id
)
SELECT
    u.user_id,
    (CURRENT_DATE - u.signed_up_at)                                AS tenure_days,
    COALESCE(ef.events_7d, 0)                                      AS events_7d,
    COALESCE(ef.events_30d, 0)                                     AS events_30d,
    COALESCE(pf.purchases_30d, 0)                                  AS purchases_30d,
    ROUND(COALESCE(pf.avg_check_30d, 0), 2)                        AS avg_check_30d,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP - COALESCE(
        GREATEST(ef.last_event_time, pf.last_purchase_time),
        u.signed_up_at::TIMESTAMP
    ))::INT                                                        AS days_since_last_activity
FROM users u
LEFT JOIN event_features ef USING (user_id)
LEFT JOIN purchase_features pf USING (user_id)
ORDER BY u.user_id;

Feature engineering — ключевой этап подготовки данных для ML. Запрос объединяет данные из нескольких источников через LEFT JOIN, чтобы не потерять пользователей без событий или покупок. COALESCE заменяет NULL на 0. GREATEST выбирает самую позднюю дату из нескольких столбцов. Такой подход позволяет построить готовую к использованию таблицу признаков одним запросом.

Что проверяет: LEFT JOIN нескольких CTE, FILTER, COALESCE, GREATEST, проектирование признаков в SQL.

Задача 42. Модели атрибуции: first-touch, last-touch, линейная

Условие. Таблицы:

CREATE TABLE touchpoints (
    touch_id    BIGINT PRIMARY KEY,
    user_id     INT NOT NULL,
    channel     VARCHAR(50) NOT NULL,   -- 'organic', 'paid_search', 'email', 'social'
    touch_time  TIMESTAMP NOT NULL
);

CREATE TABLE conversions (
    conversion_id BIGINT PRIMARY KEY,
    user_id       INT NOT NULL,
    revenue       NUMERIC(10,2) NOT NULL,
    converted_at  TIMESTAMP NOT NULL
);

Для каждого канала рассчитайте атрибутированную выручку по трём моделям: first-touch (вся выручка первому касанию), last-touch (последнему касанию) и линейная (выручка делится поровну между всеми касаниями до конверсии).

Решение.

WITH conv_touches AS (
    SELECT
        c.conversion_id,
        c.revenue,
        t.channel,
        t.touch_time,
        ROW_NUMBER() OVER (PARTITION BY c.conversion_id ORDER BY t.touch_time ASC)  AS rn_first,
        ROW_NUMBER() OVER (PARTITION BY c.conversion_id ORDER BY t.touch_time DESC) AS rn_last,
        COUNT(*)      OVER (PARTITION BY c.conversion_id)                            AS total_touches
    FROM conversions c
    JOIN touchpoints t
        ON  c.user_id = t.user_id
        AND t.touch_time <= c.converted_at
),
attributed AS (
    SELECT
        conversion_id,
        channel,
        revenue,
        CASE WHEN rn_first = 1 THEN revenue ELSE 0 END          AS first_touch_rev,
        CASE WHEN rn_last  = 1 THEN revenue ELSE 0 END          AS last_touch_rev,
        revenue / total_touches                                   AS linear_rev
    FROM conv_touches
)
SELECT
    channel,
    ROUND(SUM(first_touch_rev), 2) AS first_touch_revenue,
    ROUND(SUM(last_touch_rev), 2)  AS last_touch_revenue,
    ROUND(SUM(linear_rev), 2)      AS linear_revenue
FROM attributed
GROUP BY channel
ORDER BY linear_revenue DESC;

Запрос соединяет конверсии с касаниями, произошедшими до момента конверсии. ROW_NUMBER определяет первое и последнее касание. Для линейной модели выручка делится на количество касаний (total_touches). Сравнение трёх моделей показывает, какие каналы инициируют путь пользователя (first-touch), какие завершают (last-touch), и даёт сбалансированную картину (linear).

Что проверяет: множественные оконные функции в одном запросе, модели атрибуции, CASE WHEN в агрегации, аналитическое мышление.

Задача 43. Инкрементальная выручка: до и после запуска фичи

Условие. Таблицы:

CREATE TABLE orders (
    order_id    BIGINT PRIMARY KEY,
    user_id     INT NOT NULL,
    amount      NUMERIC(10,2) NOT NULL,
    order_date  DATE NOT NULL
);

CREATE TABLE feature_exposure (
    user_id     INT PRIMARY KEY,
    exposed_at  DATE NOT NULL  -- дата, когда пользователь получил доступ к фиче
);

Сравните среднюю выручку на пользователя за 30 дней до и 30 дней после получения доступа к новой фиче. Контрольная группа — пользователи без доступа за тот же календарный период (средний exposed_at как ориентир).

Решение.

WITH feature_users AS (
    SELECT
        fe.user_id,
        fe.exposed_at,
        COALESCE(SUM(o.amount) FILTER (
            WHERE o.order_date BETWEEN fe.exposed_at - 30 AND fe.exposed_at - 1
        ), 0) AS rev_before,
        COALESCE(SUM(o.amount) FILTER (
            WHERE o.order_date BETWEEN fe.exposed_at AND fe.exposed_at + 29
        ), 0) AS rev_after
    FROM feature_exposure fe
    LEFT JOIN orders o ON fe.user_id = o.user_id
        AND o.order_date BETWEEN fe.exposed_at - 30 AND fe.exposed_at + 29
    GROUP BY fe.user_id, fe.exposed_at
),
pivot_date AS (
    SELECT
        (MIN(exposed_at) + (MAX(exposed_at) - MIN(exposed_at)) / 2) AS mid_date
    FROM feature_exposure
),
control_users AS (
    SELECT
        o.user_id,
        COALESCE(SUM(o.amount) FILTER (
            WHERE o.order_date BETWEEN pd.mid_date - 30 AND pd.mid_date - 1
        ), 0) AS rev_before,
        COALESCE(SUM(o.amount) FILTER (
            WHERE o.order_date BETWEEN pd.mid_date AND pd.mid_date + 29
        ), 0) AS rev_after
    FROM orders o
    CROSS JOIN pivot_date pd
    WHERE o.user_id NOT IN (SELECT user_id FROM feature_exposure)
      AND o.order_date BETWEEN pd.mid_date - 30 AND pd.mid_date + 29
    GROUP BY o.user_id, pd.mid_date
)
SELECT
    'treatment' AS group_name,
    COUNT(*)                           AS users,
    ROUND(AVG(rev_before), 2)          AS avg_rev_before,
    ROUND(AVG(rev_after), 2)           AS avg_rev_after,
    ROUND(AVG(rev_after) - AVG(rev_before), 2) AS avg_lift
FROM feature_users
UNION ALL
SELECT
    'control',
    COUNT(*),
    ROUND(AVG(rev_before), 2),
    ROUND(AVG(rev_after), 2),
    ROUND(AVG(rev_after) - AVG(rev_before), 2)
FROM control_users;

Решение строит «до/после» для тестовой группы относительно индивидуальной даты экспозиции, а для контрольной — относительно медианной даты. Разница lift-ов между группами даёт оценку инкрементального эффекта. В продакшне такой анализ дополняют статистическими тестами, но SQL-часть выглядит именно так.

Что проверяет: pre/post анализ, FILTER с диапазонами дат, контрольная vs тестовая группа, CROSS JOIN, UNION ALL.

Задача 44. RANGE vs ROWS в оконных функциях

Условие. Таблица daily_revenue:

CREATE TABLE daily_revenue (
    revenue_date DATE PRIMARY KEY,
    revenue      NUMERIC(12,2) NOT NULL
);

Рассчитайте скользящую сумму выручки за последние 7 дней двумя способами: через ROWS BETWEEN 6 PRECEDING AND CURRENT ROW и через RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW. Объясните, в каком случае результаты будут различаться.

Решение.

SELECT
    revenue_date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY revenue_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7d_rows,
    SUM(revenue) OVER (
        ORDER BY revenue_date
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
    ) AS rolling_7d_range
FROM daily_revenue
ORDER BY revenue_date;

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW берёт ровно 7 физических строк — текущую и 6 предыдущих по порядку. Если в данных пропущены дни (нет записи за выходные), ROWS всё равно возьмёт 6 предыдущих строк, которые могут охватывать более 7 календарных дней. RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW работает по значению даты: включает все строки, дата которых попадает в окно [текущая_дата - 6 дней, текущая_дата]. Если дней нет — окно будет меньше 7 строк. Таким образом, RANGE корректен при пропусках в данных, а ROWS — при гарантированной непрерывности.

Что проверяет: разница между RANGE и ROWS, оконные рамки с интервалами, понимание поведения при пропусках данных.

Задача 45. Задача «островов» — поиск непрерывных диапазонов дат

Условие. Таблица server_downtime:

CREATE TABLE server_downtime (
    server_id     INT NOT NULL,
    downtime_date DATE NOT NULL,
    UNIQUE (server_id, downtime_date)
);

Для каждого сервера найдите все непрерывные периоды недоступности (диапазоны подряд идущих дат). Выведите server_id, дату начала, дату окончания и длительность в днях.

Решение.

WITH numbered AS (
    SELECT
        server_id,
        downtime_date,
        downtime_date
            - (ROW_NUMBER() OVER (PARTITION BY server_id ORDER BY downtime_date))::INT
            AS island_group
    FROM server_downtime
)
SELECT
    server_id,
    MIN(downtime_date)                         AS period_start,
    MAX(downtime_date)                         AS period_end,
    MAX(downtime_date) - MIN(downtime_date) + 1 AS duration_days
FROM numbered
GROUP BY server_id, island_group
ORDER BY server_id, period_start;

Задача «островов» (islands problem) — классика SQL-собеседований. Идея: если из даты вычесть номер строки, то для последовательных дат результат будет одинаковым. Например: 2024-01-03 - 1 = 2024-01-02, 2024-01-04 - 2 = 2024-01-02, 2024-01-05 - 3 = 2024-01-02. Группировка по этому «псевдо-ключу» выделяет непрерывные островки. MIN и MAX дают границы диапазона.

Что проверяет: паттерн islands, ROW_NUMBER, арифметика дат, группировка по вычисляемому ключу.

Задача 46. Расчёт LTV из транзакционных данных

Условие. Таблицы:

CREATE TABLE users (
    user_id      INT PRIMARY KEY,
    signed_up_at DATE NOT NULL
);

CREATE TABLE payments (
    payment_id   BIGINT PRIMARY KEY,
    user_id      INT NOT NULL REFERENCES users(user_id),
    amount       NUMERIC(10,2) NOT NULL,
    paid_at      DATE NOT NULL
);

Рассчитайте кумулятивный LTV (Lifetime Value) по когортам: для каждого месяца регистрации покажите накопленную среднюю выручку на пользователя на 0-й, 1-й, 2-й, ... 11-й месяц жизни.

Решение.

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signed_up_at)::DATE AS cohort_month
    FROM users
),
monthly_rev AS (
    SELECT
        c.cohort_month,
        c.user_id,
        (EXTRACT(YEAR FROM p.paid_at)  - EXTRACT(YEAR FROM c.cohort_month)) * 12
        + EXTRACT(MONTH FROM p.paid_at) - EXTRACT(MONTH FROM c.cohort_month) AS lifetime_month,
        SUM(p.amount) AS month_revenue
    FROM cohorts c
    JOIN payments p USING (user_id)
    WHERE p.paid_at >= c.cohort_month
    GROUP BY c.cohort_month, c.user_id, lifetime_month
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM cohorts
    GROUP BY cohort_month
)
SELECT
    mr.cohort_month,
    mr.lifetime_month,
    cs.cohort_size,
    ROUND(SUM(mr.month_revenue) / cs.cohort_size, 2) AS avg_revenue_per_user,
    ROUND(
        SUM(SUM(mr.month_revenue)) OVER (
            PARTITION BY mr.cohort_month ORDER BY mr.lifetime_month
        ) / cs.cohort_size,
        2
    ) AS cumulative_ltv
FROM monthly_rev mr
JOIN cohort_sizes cs USING (cohort_month)
WHERE mr.lifetime_month BETWEEN 0 AND 11
GROUP BY mr.cohort_month, mr.lifetime_month, cs.cohort_size
ORDER BY mr.cohort_month, mr.lifetime_month;

LTV считается как кумулятивная средняя выручка на пользователя когорты. lifetime_month — разница в месяцах между оплатой и регистрацией. Деление суммарной выручки на размер когорты (а не на число платящих) даёт ARPU, а не ARPPU. Кумулятивная сумма через SUM(...) OVER(ORDER BY lifetime_month) формирует нарастающий LTV.

Что проверяет: когортный анализ, вычисление разницы в месяцах, SUM() OVER для кумулятивных метрик, деление на размер когорты.

Задача 47. Определение power users (топ-1% по нескольким метрикам)

Условие. Таблицы:

CREATE TABLE users (
    user_id      INT PRIMARY KEY,
    signed_up_at DATE NOT NULL
);

CREATE TABLE sessions (
    session_id   BIGINT PRIMARY KEY,
    user_id      INT NOT NULL,
    started_at   TIMESTAMP NOT NULL,
    duration_sec INT NOT NULL
);

CREATE TABLE purchases (
    purchase_id  BIGINT PRIMARY KEY,
    user_id      INT NOT NULL,
    amount       NUMERIC(10,2) NOT NULL,
    purchased_at TIMESTAMP NOT NULL
);

Найдите пользователей, входящих в топ-1% хотя бы по двум из трёх метрик: (1) количество сессий за 90 дней, (2) суммарное время сессий за 90 дней, (3) суммарная сумма покупок за 90 дней.

Решение.

WITH session_stats AS (
    SELECT
        user_id,
        COUNT(*)         AS session_count,
        SUM(duration_sec) AS total_duration
    FROM sessions
    WHERE started_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),
purchase_stats AS (
    SELECT
        user_id,
        SUM(amount) AS total_spend
    FROM purchases
    WHERE purchased_at >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY user_id
),
combined AS (
    SELECT
        u.user_id,
        COALESCE(ss.session_count, 0)  AS session_count,
        COALESCE(ss.total_duration, 0) AS total_duration,
        COALESCE(ps.total_spend, 0)    AS total_spend
    FROM users u
    LEFT JOIN session_stats ss USING (user_id)
    LEFT JOIN purchase_stats ps USING (user_id)
),
with_percentiles AS (
    SELECT
        *,
        PERCENT_RANK() OVER (ORDER BY session_count)  AS prank_sessions,
        PERCENT_RANK() OVER (ORDER BY total_duration)  AS prank_duration,
        PERCENT_RANK() OVER (ORDER BY total_spend)     AS prank_spend
    FROM combined
)
SELECT
    user_id,
    session_count,
    total_duration,
    total_spend,
    (CASE WHEN prank_sessions >= 0.99 THEN 1 ELSE 0 END
   + CASE WHEN prank_duration >= 0.99 THEN 1 ELSE 0 END
   + CASE WHEN prank_spend    >= 0.99 THEN 1 ELSE 0 END) AS top1_metrics_count
FROM with_percentiles
WHERE (CASE WHEN prank_sessions >= 0.99 THEN 1 ELSE 0 END
     + CASE WHEN prank_duration >= 0.99 THEN 1 ELSE 0 END
     + CASE WHEN prank_spend    >= 0.99 THEN 1 ELSE 0 END) >= 2
ORDER BY top1_metrics_count DESC, total_spend DESC;

PERCENT_RANK() возвращает относительную позицию строки в диапазоне от 0 до 1. Значение >= 0.99 означает, что пользователь входит в топ-1%. Суммируя бинарные флаги по трём метрикам, определяем, по скольким из них пользователь является «выбросом». Фильтр >= 2 выделяет тех, кто доминирует сразу по нескольким измерениям — это и есть power users.

Что проверяет: PERCENT_RANK, множественные метрики, LEFT JOIN нескольких CTE, композитная фильтрация.

Задача 48. Последовательность событий: A затем B в течение 24 часов

Условие. Таблица events:

CREATE TABLE events (
    event_id   BIGINT PRIMARY KEY,
    user_id    INT NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_time TIMESTAMP NOT NULL
);

Найдите пользователей, которые совершили событие 'add_to_wishlist', а затем 'purchase' в течение 24 часов (без требования, чтобы между ними не было других событий). Для каждого пользователя выведите время первого такого совпадения.

Решение.

WITH wishlist_events AS (
    SELECT user_id, event_time AS wishlist_time
    FROM events
    WHERE event_type = 'add_to_wishlist'
),
purchase_events AS (
    SELECT user_id, event_time AS purchase_time
    FROM events
    WHERE event_type = 'purchase'
),
matches AS (
    SELECT
        w.user_id,
        w.wishlist_time,
        p.purchase_time,
        ROW_NUMBER() OVER (PARTITION BY w.user_id ORDER BY w.wishlist_time, p.purchase_time) AS rn
    FROM wishlist_events w
    JOIN purchase_events p
        ON  w.user_id = p.user_id
        AND p.purchase_time > w.wishlist_time
        AND p.purchase_time <= w.wishlist_time + INTERVAL '24 hours'
)
SELECT
    user_id,
    wishlist_time,
    purchase_time,
    purchase_time - wishlist_time AS time_to_purchase
FROM matches
WHERE rn = 1
ORDER BY user_id;

Решение соединяет два подмножества событий по user_id с условием, что покупка произошла строго после добавления в список желаний и в пределах 24 часов. ROW_NUMBER с сортировкой по времени берёт первое совпадение для каждого пользователя. Этот паттерн — основа для sequence-based аналитики, когда нужно найти пользователей по цепочке действий.

Что проверяет: join с неравенством по времени, работа с интервалами, ROW_NUMBER для выбора первого совпадения, декомпозиция задачи.

Задача 49. Динамический когортный анализ: retention по источнику

Условие. Таблицы:

CREATE TABLE users (
    user_id       INT PRIMARY KEY,
    signup_source VARCHAR(50) NOT NULL,  -- 'organic', 'paid', 'referral'
    signed_up_at  DATE NOT NULL
);

CREATE TABLE activity (
    user_id       INT NOT NULL,
    activity_date DATE NOT NULL,
    UNIQUE (user_id, activity_date)
);

Постройте таблицу retention по неделям жизни (0, 1, 2, ..., 7), с разбивкой по источнику регистрации и месяцу когорты. Retention = доля пользователей когорты, активных на N-й неделе.

Решение.

WITH cohorts AS (
    SELECT
        user_id,
        signup_source,
        DATE_TRUNC('month', signed_up_at)::DATE AS cohort_month,
        signed_up_at
    FROM users
),
cohort_sizes AS (
    SELECT
        signup_source,
        cohort_month,
        COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY signup_source, cohort_month
),
weekly_activity AS (
    SELECT DISTINCT
        c.signup_source,
        c.cohort_month,
        c.user_id,
        (a.activity_date - c.signed_up_at) / 7 AS week_number
    FROM cohorts c
    JOIN activity a USING (user_id)
    WHERE a.activity_date >= c.signed_up_at
      AND (a.activity_date - c.signed_up_at) / 7 <= 7
)
SELECT
    wa.signup_source,
    wa.cohort_month,
    wa.week_number,
    cs.cohort_size,
    COUNT(DISTINCT wa.user_id) AS active_users,
    ROUND(100.0 * COUNT(DISTINCT wa.user_id) / cs.cohort_size, 1) AS retention_pct
FROM weekly_activity wa
JOIN cohort_sizes cs
    ON  wa.signup_source = cs.signup_source
    AND wa.cohort_month  = cs.cohort_month
GROUP BY wa.signup_source, wa.cohort_month, wa.week_number, cs.cohort_size
ORDER BY wa.signup_source, wa.cohort_month, wa.week_number;

Запрос строит полноценную retention-таблицу с тремя измерениями: источник, когорта, неделя. Номер недели вычисляется целочисленным делением разницы дат на 7. COUNT(DISTINCT user_id) / cohort_size даёт retention rate. Такой отчёт позволяет сравнивать качество трафика из разных каналов и выявлять когорты с аномальным удержанием.

Что проверяет: когортный анализ, retention, целочисленное деление для бакетирования, COUNT(DISTINCT), многомерная группировка.

Задача 50. Retention-тепловая карта (помесячная) в SQL

Условие. Таблицы:

CREATE TABLE users (
    user_id      INT PRIMARY KEY,
    signed_up_at DATE NOT NULL
);

CREATE TABLE activity (
    user_id       INT NOT NULL,
    activity_date DATE NOT NULL,
    UNIQUE (user_id, activity_date)
);

Постройте помесячную retention-тепловую карту: строки — месяцы когорт, столбцы — месяцы жизни (0-11). Значение в ячейке — процент пользователей когорты, активных на N-м месяце. Результат должен быть в формате «одна строка на когорту» (pivot).

Решение.

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signed_up_at)::DATE AS cohort_month
    FROM users
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(*) AS cohort_size
    FROM cohorts
    GROUP BY cohort_month
),
monthly_active AS (
    SELECT DISTINCT
        c.cohort_month,
        c.user_id,
        (EXTRACT(YEAR FROM a.activity_date)  - EXTRACT(YEAR FROM c.cohort_month)) * 12
        + EXTRACT(MONTH FROM a.activity_date) - EXTRACT(MONTH FROM c.cohort_month) AS month_number
    FROM cohorts c
    JOIN activity a USING (user_id)
    WHERE a.activity_date >= c.cohort_month
),
retention AS (
    SELECT
        ma.cohort_month,
        ma.month_number,
        cs.cohort_size,
        COUNT(DISTINCT ma.user_id) AS active_users
    FROM monthly_active ma
    JOIN cohort_sizes cs USING (cohort_month)
    WHERE ma.month_number BETWEEN 0 AND 11
    GROUP BY ma.cohort_month, ma.month_number, cs.cohort_size
)
SELECT
    cohort_month,
    cohort_size,
    MAX(CASE WHEN month_number = 0  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m0,
    MAX(CASE WHEN month_number = 1  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m1,
    MAX(CASE WHEN month_number = 2  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m2,
    MAX(CASE WHEN month_number = 3  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m3,
    MAX(CASE WHEN month_number = 4  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m4,
    MAX(CASE WHEN month_number = 5  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m5,
    MAX(CASE WHEN month_number = 6  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m6,
    MAX(CASE WHEN month_number = 7  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m7,
    MAX(CASE WHEN month_number = 8  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m8,
    MAX(CASE WHEN month_number = 9  THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m9,
    MAX(CASE WHEN month_number = 10 THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m10,
    MAX(CASE WHEN month_number = 11 THEN ROUND(100.0 * active_users / cohort_size, 1) END) AS m11
FROM retention
GROUP BY cohort_month, cohort_size
ORDER BY cohort_month;

Pivot в PostgreSQL выполняется через MAX(CASE WHEN ... END) — это стандартный приём, поскольку встроенного PIVOT в PostgreSQL нет. Каждый столбец m0-m11 представляет месяц жизни когорты. Результат визуально соответствует retention-тепловой карте: строка — когорта, столбец — возраст, значение — процент retention. Ячейки с NULL означают, что когорта ещё не достигла этого возраста.

Что проверяет: pivot через CASE WHEN + MAX, когортный retention, вычисление разницы в месяцах, CTE-пайплайн из 4+ шагов, формирование аналитического отчёта.

На что обращать внимание при решении

Несколько практических советов, которые помогут на реальном собеседовании.

Начинайте с декомпозиции. Прежде чем писать запрос, проговорите план: «Сначала я найду X, потом соединю с Y, потом отфильтрую по Z». Интервьюер оценивает ход мысли, а не скорость написания кода.

Используйте CTE, а не вложенные подзапросы. CTE (WITH ... AS) делают запрос читаемым. На собеседовании это особенно важно — интервьюер должен быстро понять вашу логику.

Защищайтесь от деления на ноль. NULLIF(x, 0) в знаменателе — хорошая привычка. Это показывает, что вы думаете о краевых случаях.

Не путайте WHERE и HAVING. WHERE фильтрует строки до группировки, HAVING — после. Если нужно отфильтровать по результату агрегатной функции, используйте HAVING.

Знайте разницу между ON и WHERE в LEFT JOIN. Условие в ON влияет на соединение, условие в WHERE фильтрует результат. Для LEFT JOIN это принципиально: перенос фильтра из ON в WHERE превращает его в INNER JOIN.

Помните про NULL. NULL — не значение, а отсутствие значения. NULL = NULL возвращает не TRUE, а NULL. COUNT(*) считает строки, COUNT(column) — не-NULL значения. Это частый источник ошибок.

FAQ

Сколько SQL-задач обычно дают на собеседовании аналитика?

Обычно 2–4 задачи за 40–60 минут. Начинают с простых (GROUP BY, JOIN), затем усложняют до оконных функций и когортного анализа. Важна не только правильность, но и скорость написания.

Какие темы SQL чаще всего спрашивают?

Топ-5: оконные функции (ROW_NUMBER, LAG/LEAD), JOIN разных типов, GROUP BY с HAVING, подзапросы и CTE, работа с датами (DATE_TRUNC, INTERVAL). На позиции продуктового аналитика часто просят построить воронку или посчитать retention.

Нужно ли знать диалект конкретной СУБД?

На большинстве собеседований принимают стандартный SQL или PostgreSQL-синтаксис. Если вы знаете PostgreSQL, этого достаточно. Уточните у рекрутера, если используется ClickHouse или BigQuery — там есть нюансы.

Как готовиться к SQL-задачам на собеседовании?

Решайте задачи руками, не копируя готовые решения. Начните с базовых (JOIN, GROUP BY), затем переходите к оконным функциям и когортам. Проговаривайте логику вслух — на собеседовании оценивают ход мысли, а не только финальный запрос.

Что дальше

30 задач в этой статье покрывают основные темы: от базового GROUP BY до sessionization, LATERAL JOIN и рекурсивных CTE. На собеседовании могут попасться и другие: работа с JSON, оптимизация запросов, материализованные представления. Чем больше задач вы прорешаете руками, тем увереннее будете себя чувствовать.

50 задач по SQL для собеседования аналитика

Как устроены SQL-задачи на собеседованиях

Простой уровень

Задача 1. Количество заказов по статусу

Задача 2. Пользователи с более чем тремя заказами

Задача 3. Список товаров без заказов

Задача 4. Средний чек по месяцам

Задача 5. Категория с максимальной выручкой

Средний уровень

Задача 6. Выручка за текущий и предыдущий месяц

Задача 7. Топ-3 товара в каждой категории по продажам

Задача 8. Пользователи, которые делали заказы два месяца подряд

Задача 9. Нарастающий итог выручки

Задача 10. Сегментация пользователей по активности

Сложный уровень

Задача 11. Retention Day 1 по когортам

Задача 12. Цепочка событий — конверсия воронки

Задача 13. Поиск аномалий — дни с выручкой выше двух стандартных отклонений

Задача 14. Среднее время между заказами пользователя

Задача 15. Пользователи с растущей суммой заказов три месяца подряд

Задача 16. Скользящее среднее выручки за 7 дней

Задача 17. Медианная зарплата по отделам

Задача 18. Генерация серии дат через рекурсивный CTE

Задача 19. Топ-2 товара в каждой категории через LATERAL JOIN

Задача 20. Поворот данных с CASE WHEN (кросс-таблица)

Задача 21. Нарастающий итог с обнулением по месяцам

Задача 22. Поиск пропущенных дней активности

Задача 23. Пары пользователей с одинаковыми покупками

Задача 24. Пользователи, которые заходили, но не покупали

Задача 25. Список товаров в одной строке через STRING_AGG

Задача 26. Разница между ROW_NUMBER, RANK и DENSE_RANK

Задача 27. Условная агрегация с FILTER

Задача 28. Определение пользовательских сессий по событиям

Задача 29. В каком перцентиле находится пользователь

Задача 30. Обнаружение N+1 паттерна в логах запросов

Экспертный уровень

Задача 31. Воронка с строгим порядком событий

Задача 32. Сессионизация потока событий

Задача 33. Рост год к году по категориям

Задача 34. Средневзвешенное значение

Задача 35. Первое вхождение N подряд идущих дней активности

Задача 36. Анализ потребительской корзины

Задача 37. Расчёт Net Promoter Score (NPS)

Задача 38. Среднее время от регистрации до первой покупки

Задача 39. Обнаружение дублирующихся транзакций

Задача 40. Рекурсивный CTE для организационной иерархии

Задача 41. Построение таблицы признаков для прогноза оттока

Задача 42. Модели атрибуции: first-touch, last-touch, линейная

Задача 43. Инкрементальная выручка: до и после запуска фичи

Задача 44. RANGE vs ROWS в оконных функциях

Задача 45. Задача «островов» — поиск непрерывных диапазонов дат

Задача 46. Расчёт LTV из транзакционных данных

Задача 47. Определение power users (топ-1% по нескольким метрикам)

Задача 48. Последовательность событий: A затем B в течение 24 часов

Задача 49. Динамический когортный анализ: retention по источнику

Задача 50. Retention-тепловая карта (помесячная) в SQL

На что обращать внимание при решении

FAQ

Сколько SQL-задач обычно дают на собеседовании аналитика?

Какие темы SQL чаще всего спрашивают?

Нужно ли знать диалект конкретной СУБД?

Как готовиться к SQL-задачам на собеседовании?

Что дальше

Ещё по теме