Разбор SQL тестового задания аналитика

Тестовое на SQL для аналитика почти всегда сводится к одному сценарию: дают таблицу транзакций (или заказов) и просят посчитать траты клиента за период, топ-N по обороту и разбивку по месяцам. Именно так устроено реальное задание МТС на продуктового аналитика — таблица transact с четырьмя колонками и три вопроса подряд. Ниже — полный разбор такого задания: от схемы данных до финальных выводов, с рабочим кодом на PostgreSQL, альтернативами и главное — как объяснить решение вслух, потому что чистый запрос без комментария на собеседовании стоит вдвое меньше.

Разберём на конкретном кейсе, который реально дают в российских компаниях. Формулировки и схему я собрал из открытых тестовых заданий (МТС, Тинькофф) — так что вы тренируетесь не на выдуманном примере, а на том, что реально прилетает в почту после первого созвона.

Реальное задание: таблица транзакций

Вот дословная постановка (по мотивам тестового МТС на продуктового аналитика). Дана одна таблица transact:

Данные — с 2021 года по «сегодня». Три подзадачи, сложность растёт:

• Посчитать траты каждого клиента за последние 30 дней от текущей даты.
• Для топ-100 клиентов по тратам за всю историю посчитать помесячную разбивку трат.
• Прикинуть, как считать retention для этих клиентов (и какие вопросы задать продукту перед реализацией).

Выглядит просто. Но именно на такой «простоте» валятся: путают 30 дней и текущий месяц, тянут топ-100 неоптимально, забывают про клиентов без транзакций и не проговаривают допущения. Разберём по шагам.

Мини-датасет, на котором будем проверять логику руками:

id_client | id_tran | tran_time           | sum_tran
101       | 1       | 2026-06-10 09:12:00 | 1200.00
101       | 2       | 2026-06-25 14:03:00 |  800.00
101       | 3       | 2026-05-02 11:00:00 | 3000.00
102       | 4       | 2026-06-28 20:41:00 | 5000.00
102       | 5       | 2026-04-15 08:30:00 |  450.00
103       | 6       | 2026-06-30 23:59:00 |  150.00

Если «сегодня» — 2026-07-05, то окно «последние 30 дней» — это 2026-06-05, 2026-07-05). Транзакция клиента 101 от 2 мая туда не попадает, от 10 и 25 июня — попадают. Держите этот пример в голове: он поймает 90% ошибок.

SELECT
    id_client,
    SUM(sum_tran) AS spend_30d,
    COUNT(*)      AS tx_count_30d
FROM transact
WHERE tran_time >= now() - INTERVAL '30 days'
  AND tran_time <  now()
GROUP BY id_client
ORDER BY spend_30d DESC;

Потренировать именно такие запросы с автопроверкой на настоящем PostgreSQL можно в [SQL-тренажёре — там 545 задач, и фильтр по датам с оконными функциями — отдельный большой блок.

Шаг 2: топ-100 клиентов и помесячная разбивка

Тут задача из двух частей: сначала найти топ-100 по обороту за всё время, потом для них — суммы по месяцам. Наивный путь — посчитать топ-100, выгрузить id, подставить в IN (...). Не делайте так: во-первых, руками не подставишь 100 id, во-вторых, это лишний проход. CTE решает всё одним запросом.

WITH client_totals AS (
    SELECT
        id_client,
        SUM(sum_tran) AS total_spend
    FROM transact
    GROUP BY id_client
),
top100 AS (
    SELECT id_client
    FROM client_totals
    ORDER BY total_spend DESC
    LIMIT 100
)
SELECT
    t.id_client,
    date_trunc('month', t.tran_time)::date AS month,
    SUM(t.sum_tran)                        AS monthly_spend
FROM transact t
JOIN top100 USING (id_client)
GROUP BY t.id_client, date_trunc('month', t.tran_time)
ORDER BY t.id_client, month;

Логика читается сверху вниз: client_totals считает оборот на клиента, top100 берёт сотню лучших, финальный SELECT джойнит исходную таблицу только с этой сотней и режет по месяцам через date_trunc. USING (id_client) — короткая запись для джойна по одноимённой колонке.

Ловушка с ничьей на границе. Если у клиентов на 100-м и 101-м месте одинаковый оборот, LIMIT 100 возьмёт кого-то одного недетерминированно. На собеседовании об этом стоит сказать вслух — покажет, что вы думаете про edge cases. Если нужен честный отбор «всех, кто дотянул до порога», используйте оконную функцию:

WITH ranked AS (
    SELECT
        id_client,
        SUM(sum_tran) AS total_spend,
        DENSE_RANK() OVER (ORDER BY SUM(sum_tran) DESC) AS rnk
    FROM transact
    GROUP BY id_client
)
SELECT id_client, total_spend, rnk
FROM ranked
WHERE rnk <= 100;

Про производительность. Если таблица большая, а нужна именно помесячная агрегация, можно посчитать месячные суммы сразу и наложить оконный SUM(...) OVER (PARTITION BY id_client) для тотала — это один проход вместо двух агрегаций. Но читаемость падает, и на собеседовании я бы сначала показал версию через CTE, а оптимизацию проговорил как «если упрёмся в perf, перепишу так». Правильный порядок: сначала корректно и понятно, потом быстро.

Шаг 3: обогащаем ответ — накопительный итог и доля

Настоящий тест — это не «дай цифру», а «покажи, что понимаешь данные». Помесячную разбивку почти всегда просят дополнить: накопительный итог по клиенту и доля месяца в его годовом обороте. Здесь оконные функции незаменимы.

WITH monthly AS (
    SELECT
        id_client,
        date_trunc('month', tran_time)::date AS month,
        SUM(sum_tran)                         AS monthly_spend
    FROM transact
    WHERE tran_time >= date '2026-01-01'
    GROUP BY id_client, date_trunc('month', tran_time)
)
SELECT
    id_client,
    month,
    monthly_spend,
    SUM(monthly_spend) OVER (
        PARTITION BY id_client ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    ROUND(
        100.0 * monthly_spend
        / SUM(monthly_spend) OVER (PARTITION BY id_client),
        1
    ) AS pct_of_year
FROM monthly
ORDER BY id_client, month;

Что здесь важно проговорить:

• SUM() OVER (PARTITION BY id_client ORDER BY month ...) с рамкой UNBOUNDED PRECEDING до текущей строки — это накопительный итог внутри клиента. Без ORDER BY оконная сумма посчитает весь итог по партиции, а не нарастающий.
• SUM() OVER (PARTITION BY id_client) без ORDER BY — это годовой оборот клиента как знаменатель для доли. Одна и та же функция, две разные рамки — распространённая точка путаницы.
• 100.0, а не 100 — иначе в PostgreSQL при делении целых потеряете дробную часть. Мелочь, которая ломает результат тихо.

Формула доли: $\text{pct} = \dfrac{\text{monthly\_spend}}{\sum \text{monthly\_spend по клиенту}} \times 100$. Проговорить её на словах — плюс к впечатлению.

Если хочется добавить месяц-к-месяцу динамику, LAG даёт предыдущее значение:

LAG(monthly_spend) OVER (PARTITION BY id_client ORDER BY month) AS prev_month

и рост считается как (monthly_spend - prev_month) / prev_month. Про деление на ноль в первом месяце (prev_month = NULL) тоже стоит вспомнить вслух — NULLIF(prev_month, 0) спасает.

Альтернативы и типичные ошибки

Клиенты без транзакций. Если по условию нужны все клиенты (включая тех, у кого за 30 дней ничего не было), GROUP BY по transact их потеряет — их там просто нет. Тогда нужен LEFT JOIN от таблицы клиентов к транзакциям и COALESCE(SUM(sum_tran), 0). В нашем задании таблицы клиентов нет, но на собеседовании фраза «а если нужны нулевые клиенты, я бы взял LEFT JOIN от справочника» показывает, что вы видите дальше формулировки.

BETWEEN для дат-со-временем. tran_time BETWEEN '2026-06-01' AND '2026-06-30' молча теряет весь последний день, потому что '2026-06-30' разворачивается в полночь 00:00:00. Правильно — полуинтервал >= '2026-06-01' AND < '2026-07-01'. Это ошибка №1 в тестовых по транзакциям.

Коррелированный подзапрос вместо оконки. Топ-N на клиента через (SELECT COUNT(*) FROM ... WHERE ...) < N работает, но это O(n²) — на каждой строке отдельный подсчёт. Оконный ROW_NUMBER() делает то же за один проход. Если увидите в своём решении коррелированный подзапрос по большой таблице — почти всегда его можно заменить оконной функцией.

Округление денег. numeric для сумм — не float. float копит ошибку, и SUM по миллиону строк уедет на копейки. В тестовом на это редко смотрят, но если колонка изначально numeric — не приводите её к float без причины.

Отработать эти паттерны на живых данных удобнее всего в тренажёре SQL и на продуктовых кейсах — там 618 разборов, где за запросом всегда стоит бизнес-вопрос, а не абстрактная таблица.

Как подать это на собеседовании

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

1. Уточните допущения до первой строчки кода. «30 дней — это скользящее окно от now или календарный месяц? Нужны ли клиенты без транзакций? Топ-100 — если ничья на границе, брать всех или ровно 100?» Два-три таких вопроса сразу ставят вас выше кандидата, который молча пишет SELECT *. В тестовом МТС третья часть прямо просит «сформулировать вопросы к продукту» — это встроено в оценку намеренно.

2. Проговорите структуру решения до синтаксиса. «Сначала CTE с оборотом на клиента, потом отбор топ-100, потом джойн обратно и разбивка по месяцам через date_trunc.» Интервьюер должен понять план раньше, чем вы напишете первый WITH.

3. Комментируйте выбор инструмента. Не «я использую DENSE_RANK», а «беру DENSE_RANK, потому что при равном обороте на 100-м месте хочу вернуть всех претендентов, а не случайного одного». Обоснование важнее самой функции.

4. Покажите, что думаете про производительность — но в нужный момент. Сначала корректный и читаемый запрос. Потом: «на проде эта таблица большая, я бы убедился, что есть индекс по tran_time, и не приводил бы его к date в WHERE, чтобы не потерять index scan». Преждевременная оптимизация в ущерб читаемости — тоже минус.

5. Закончите выводом, а не таблицей. «Видно, что топ-100 клиентов дают X% оборота, у большинства траты сконцентрированы в 2-3 месяцах — стоит проверить сезонность и посмотреть retention отдельно.» Аналитик, который из цифр вытаскивает гипотезу, ценится выше того, кто просто отдаёт результат запроса.

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

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

Какие SQL-конструкции чаще всего проверяют в тестовом на аналитика?

Оконные функции (ROW_NUMBER, RANK, DENSE_RANK, LAG/LEAD, SUM/AVG OVER), CTE (WITH), агрегации с GROUP BY, фильтрация по датам и джойны — включая LEFT JOIN для сохранения строк без совпадений. На middle/senior 70-80% практических задач так или иначе завязаны на оконные функции: топ-N на группу, накопительный итог, скользящее среднее, доля от целого, month-over-month динамика. Если уверенно владеете оконками и CTE — большинство тестовых на транзакции решаются за 15-20 минут.

Как правильно посчитать «последние 30 дней» в SQL?

Через полуинтервал по timestamp: tran_time >= now() - INTERVAL '30 days' AND tran_time < now(). Ключевых момента три. Не используйте BETWEEN с датами-строками — он теряет последний день, потому что дата без времени разворачивается в полночь. Не приводите tran_time к ::date в WHERE — это ломает индекс и превращает range scan в полный проход по таблице. И уточните у интервьюера, «30 дней» — это скользящее окно от текущего момента (now()) или полные календарные сутки (CURRENT_DATE); по умолчанию для транзакций берут now().

Чем RANK отличается от DENSE_RANK и ROW_NUMBER?

Что делать, если не успеваю дописать запрос за отведённое время?

Проговаривайте план целиком, даже если код не закончен. Скелет из CTE с комментариями «здесь считаю оборот, здесь беру топ-100, здесь разбиваю по месяцам» показывает ход мысли лучше, чем половина рабочего запроса без объяснений. Начинайте с самого простого корректного варианта, а усложнения (накопительный итог, доля, обработка ничьих) добавляйте по мере времени и проговаривайте вслух то, что не успели написать. Проверяющий оценивает мышление, а не скорость печати — незаконченное, но структурно верное решение с внятными допущениями почти всегда лучше поспешного запроса с багом в датах.