SQL-задачи с собеседования middle-аналитика: 12 разборов

Как отвечать вслух перед редактором SQL

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

Если интервьюер просит второе по величине, уточните: вторая строка или второе различное значение? Если просит возврат, уточните: на следующий календарный день или в течение 24 часов? Если просит медиану, уточните: по броням, билетам или людям? Каркас устного ответа на аналитическом интервью помогает держать эту дисциплину; здесь дальше — именно SQL-практика.

Все даты в примерах относятся к локальному московскому времени базы без смещения. app_events начинается 1 июля 2026-го, срез заканчивается 11 августа в 18:00. Поэтому ряд задач сознательно ограничен полными июльскими окнами. Из других материалов полезны карта продвинутого SQL и базовые задачи.

1. Вторая различная цена брони

Условие. Среди броней 1 июля найдите максимальную и вторую различную сумму. Вопрос не про вторую строку после сортировки: две брони с одинаковым максимумом не должны дать «вторую цену», равную первой. Одна строка исходника — бронь, поэтому сначала ограничиваем book_date, затем берём максимум и максимум строго ниже него.

Что проверяют. Понимание ничьих и разницы между LIMIT 1 OFFSET 1 по строкам и вторым значением. Ответ — 1 204 500 ₽ и 947 500 ₽. Неверный max(total_amount) без условия повторит 1 204 500 ₽. Если спросят дальше: что вернуть, если все цены одинаковы? Здесь second_price будет NULL — это честнее фиктивного нуля.

Уточнение вслух: если интервьюер говорит «вторая бронь», порядок должен включать устойчивый ключ book_ref, и ответом станет строка. Если он говорит «вторая цена», ключи броней уже не важны, а DISTINCT-семантика обязательна. Вариант через dense_rank тоже работает, но CTE с максимумом здесь короче и показывает, как решать задачу без окна.

WITH b AS (
  SELECT total_amount FROM bookings
  WHERE book_date >= TIMESTAMP '2026-07-01' AND book_date < TIMESTAMP '2026-07-02'
), maximum AS (SELECT max(total_amount) AS max_price FROM b)
SELECT max_price,
       (SELECT max(total_amount) FROM b WHERE total_amount < max_price) AS second_price
FROM maximum;

import pandas as pd

b = pd.read_parquet('bookings.parquet', columns=['book_date', 'total_amount'])
b['book_date'] = pd.to_datetime(b['book_date'])
day = b.loc[b.book_date.ge(pd.Timestamp('2026-07-01')) & b.book_date.lt(pd.Timestamp('2026-07-02'))]
prices = sorted(day.total_amount.astype(float).unique(), reverse=True)
print(int(prices[0]), int(prices[1]))

2. Медиана шести броней без готовой функции

Условие. Возьмите первые шесть броней 1 июля по book_ref и найдите медиану без percentile_cont. Выбор первых шести — фиксированное учебное подмножество, а не оценка всей даты. После сортировки цен две центральные строки имеют значения 52 900 и 172 700 ₽, поэтому медиана — 112 800 ₽.

Что проверяют. При чётном n нужно оставить обе середины, а не одну. row_number нумерует цены, count(*) OVER () даёт n, условия через n/2.0 выбирают позиции 3 и 4. Ошибка — посчитать медиану по всем броням 1 июля и получить 56 000 ₽: это другой вопрос. Если спросят дальше: как обрабатывать NULL? Сначала явно исключите отсутствующие цены и пересчитайте n. Разбор CONT и DISC показывает, что означает интерполяция.

Запрос возвращает middle_rows=2 — это отдельный контроль, а не декоративная колонка. При нечётном n он должен стать 1. Если получилось 0 или 3, ошиблись в границе рангов. Pandas-блок ниже печатает сами центральные цены 52 900 и 172 700 ₽; их среднее можно проверить вручную. Так вы доказываете результат без доверия к одной встроенной функции.

WITH six AS (
  SELECT book_ref, total_amount FROM bookings
  WHERE book_date >= TIMESTAMP '2026-07-01' AND book_date < TIMESTAMP '2026-07-02'
  ORDER BY book_ref LIMIT 6
), ranked AS (
  SELECT total_amount, row_number() OVER (ORDER BY total_amount, book_ref) AS rn,
         count(*) OVER () AS n FROM six
)
SELECT count(*) AS middle_rows, avg(total_amount) AS median_amount
FROM ranked WHERE rn >= n/2.0 AND rn <= n/2.0+1;

import pandas as pd

b = pd.read_parquet('bookings.parquet', columns=['book_ref', 'book_date', 'total_amount'])
b['book_date'] = pd.to_datetime(b['book_date'])
six = b.loc[b.book_date.ge(pd.Timestamp('2026-07-01')) & b.book_date.lt(pd.Timestamp('2026-07-02'))].sort_values('book_ref').head(6)
values = six.total_amount.astype(float).sort_values().reset_index(drop=True)
print(len(values), int(values.iloc[2]), int(values.iloc[3]), int(values.median()))

3. Вернулся ли участник на следующий день

Условие. Среди участников с app_open 1–10 июля найдите разных активных людей и тех, у кого после хотя бы одного активного дня был app_open на следующий календарный день. Одна строка промежуточного набора — участник × дата, поэтому сначала DISTINCT, затем самосоединение по member_id и дню + 1.

Что проверяют. Зрелость окна: для активности 10 июля нужно видеть 11 июля. Поэтому исходный CTE читает события до 12 июля, а якорные даты ограничены 10-м. Ответ: 9 851 активный и 2 831 с хотя бы одной парой соседних дней. Ошибка — ограничить обе стороны 10 июля и тихо объявить все открытия последнего дня невозвратами. Если спросят дальше: доля по каждой дате потребует другой знаменатель и сохранения даты в GROUP BY.

Пара соседних календарных дат — не то же самое, что интервал между событиями до 24 часов. Открытие в 23:58 и следующее в 00:03 попадут в D1 по календарю, хотя прошло пять минут. На собеседовании это хороший момент назвать определение retention до того, как писать JOIN. Здесь намеренно считается «хотя бы один возврат после любого дня», а не среднее дневных долей.

WITH days AS (
  SELECT DISTINCT member_id, event_time::date AS day
  FROM app_events
  WHERE event_name='app_open' AND member_id IS NOT NULL
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-12'
)
SELECT count(DISTINCT a.member_id) AS active,
       count(DISTINCT b.member_id) AS returned_next_day
FROM days a LEFT JOIN days b
  ON b.member_id=a.member_id AND b.day=a.day+INTERVAL '1 day'
WHERE a.day < DATE '2026-07-11';

4. Повторная покупка за 30 дней

Условие. Для клиентов, чья первая наблюдаемая purchase пришлась на 1–5 июля, найдите долю с ещё одной покупкой в пределах 30 суток. Повторные доставки одного события убираем по паре клиент-время; затем lead(event_time) берёт следующую покупку клиента. Это не задача о повторной брони участника: здесь источник — журнал приложения и первая наблюдаемая покупка клиента.

Что проверяют. Нельзя сравнивать покупку 5 июля с неполным хвостом: до среза 11 августа прошло больше 30 суток, окно зрелое. Ответ — 5 726 клиентов в когорте и 679 повторивших в течение 30 суток. Неверно фильтровать события по 5 июля до LEAD: тогда следующая покупка позже 5-го исчезнет. Если спросят дальше: «в течение 30 календарных дней» и «30 × 24 часа» требуют разных границ.

Отдельно назовите ограничение источника. Первая покупка в журнале означает первую наблюдаемую с 1 июля, а не первую в жизни клиента: до начала журнала могли быть другие сделки. Доля повторов по такой когорте полезна для упражнения, но для нового покупателя в продукте нужна более длинная история или флаг первого заказа из надёжного источника.

WITH purchases AS (
  SELECT DISTINCT client_id, event_time FROM app_events WHERE event_name='purchase'
), ordered AS (
  SELECT client_id, event_time,
         lead(event_time) OVER (PARTITION BY client_id ORDER BY event_time) AS next_time,
         row_number() OVER (PARTITION BY client_id ORDER BY event_time) AS rn
  FROM purchases
)
SELECT count(*) AS cohort,
       count(*) FILTER (WHERE next_time <= event_time + INTERVAL '30 days') AS repeated_30d
FROM ordered
WHERE rn=1 AND event_time >= TIMESTAMP '2026-07-01'
  AND event_time < TIMESTAMP '2026-07-06';

5. Серия дней подряд без дублирования дат

Условие. Найдите самую длинную серию календарных дней с app_open у участника 605 до 15 июля. В один день он открывал приложение несколько раз; сначала сводим к уникальным датам, иначе row_number будет расти внутри одной даты и разорвёт настоящий остров.

Что проверяют. При последовательных датах day - row_number() остаётся постоянным. Серия 7–10 июля длиной 4 дня — максимальная на этом отрезке. Ошибка — считать восемь открытий восемью днями и получить ложную длину. Если спросят дальше: для серии не по календарю, а по условию «пауза не больше суток» нужен LAG и накопительная сумма флагов. Окна LAG/LEAD — вход в эту технику.

Здесь дата имеет тип DATE, и в PostgreSQL из неё можно вычесть целое число дней. В другом движке типовая арифметика может отличаться; не переносите выражение без проверки. Сам принцип острова не зависит от конкретной записи: одинаковое смещение после нумерации объединяет подряд идущие даты. Проверка min(day), max(day), count(*) показывает и длину, и фактические границы.

WITH days AS (
  SELECT DISTINCT event_time::date AS day FROM app_events
  WHERE member_id=605 AND event_name='app_open'
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-15'
), numbered AS (
  SELECT day, day - row_number() OVER (ORDER BY day)::integer AS island FROM days
)
SELECT min(day) AS first_day, max(day) AS last_day, count(*) AS days_count
FROM numbered GROUP BY island
ORDER BY days_count DESC, first_day LIMIT 1;

6. Сессии по паузе в 30 минут

Условие. Для всех событий участника 605 с 1 по 10 июля включительно, то есть в границах [1 июля, 11 июля), посчитайте число сессий. Первая строка всегда начинает сессию; каждая пауза строго больше 30 минут открывает новую. Сортируем по времени и event_id, чтобы равные секунды имели устойчивый порядок.

Что проверяют. LAG сравнивает соседние события одного человека, а не максимальное время во всей таблице. В отрезке 18 событий и 8 начал сессий. Ошибка — писать >= INTERVAL '30 minutes': на ровной границе смысл меняется; договоритесь, включается ли она. Если спросят дальше: удалите повторные доставки до нарезки и оцените чувствительность числа сессий к порогу 15/60 минут.

Число сессий равно числу строк с флагом начала, потому что каждая такая строка открывает ровно одну новую группу. Для полноценной витрины понадобится накопительная сумма флага по времени, которая присвоит событиям номера сессий. Этот запрос намеренно о числе групп, а не о длительности: длительность нельзя вывести из одного count(*) без первого и последнего времени каждой группы.

WITH ordered AS (
  SELECT event_time, event_id,
         lag(event_time) OVER (ORDER BY event_time, event_id) AS prev_time
  FROM app_events
  WHERE member_id=605 AND event_time >= TIMESTAMP '2026-07-01'
    AND event_time < TIMESTAMP '2026-07-11'
)
SELECT count(*) AS events,
       count(*) FILTER (WHERE prev_time IS NULL OR event_time-prev_time > INTERVAL '30 minutes') AS sessions
FROM ordered;

7. Первое и последнее касание

Условие. В том же окне участника 605 найдите первое и последнее событие с временем, не предполагая, что min(event_name) имеет отношение к хронологии. Два row_number сортируют в прямом и обратном порядке; фильтр берёт названия событий в нужных строках.

Что проверяют. Первый контакт — push_open 3 июля в 12:00:22, последний — view_offer 10 июля в 21:33:06. Ошибка — независимо считать min(event_time) и min(event_name), получив поля из разных строк. Если спросят дальше: первое касание каждого клиента требует PARTITION BY client_id, а путь событий можно собрать string_agg с явным ORDER BY. Разбор путей показывает следующую ступень.

Не превращайте «первое событие в выбранном окне» в «первое касание в жизни». Журнал начинается 1 июля, а выбранный участник появляется 3-го; до начала наблюдения могла быть другая история. Для атрибуции нужен явно заданный lookback и политика неизвестного предшествующего пути. Это ограничение источника остаётся даже при идеальном ORDER BY.

WITH ranked AS (
  SELECT event_name, event_time,
         row_number() OVER (ORDER BY event_time,event_id) AS first_rn,
         row_number() OVER (ORDER BY event_time DESC,event_id DESC) AS last_rn
  FROM app_events WHERE member_id=605
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-11'
)
SELECT max(event_name) FILTER (WHERE first_rn=1) AS first_event,
       max(event_name) FILTER (WHERE last_rn=1) AS last_event,
       max(event_time) FILTER (WHERE first_rn=1) AS first_time,
       max(event_time) FILTER (WHERE last_rn=1) AS last_time
FROM ranked;

8. Значение на дату по SCD2

Условие. Какой уровень лояльности действовал у участника 605 10 июля в полдень? В таблице истории одна строка — интервал действия уровня. Условие полуоткрытое: valid_from <= t и t < valid_to; для открытого интервала valid_to равен NULL.

Что проверяют. Ответ — basic, интервал начался 1 июля и остаётся открытым. Неверно брать строку с максимальным valid_from без условия «до t»: будущая смена перепишет прошлое. Если спросят дальше: что делать с двумя пересекающимися интервалами? Не выбирать один молча; проверять инвариант истории и выводить конфликт как дефект данных. Для производственного JOIN с фактами время факта должно быть явно выбрано.

Полуоткрытая граница [valid_from, valid_to) делает смену уровня однозначной: в момент нового valid_from старая запись уже не действует. NULL в valid_to здесь означает отсутствие записанного конца, а не бесконечное доказательство актуальности. Если в истории есть будущая запись, открытый интервал может пересекаться с ней; для массовой витрины проверяйте это до присоединения покупок.

SELECT tier, valid_from, valid_to
FROM loyalty_tier_history
WHERE member_id=605
  AND valid_from <= TIMESTAMP '2026-07-10 12:00:00'
  AND (valid_to > TIMESTAMP '2026-07-10 12:00:00' OR valid_to IS NULL);

9. Подытог по платформам

Условие. На событиях search и purchase 4–5 июля выведите число строк по платформам и общий итог. Нужны детали и одна общая строка, поэтому ROLLUP(platform); GROUPING(platform) помечает итог независимо от того, встретился ли настоящий NULL в платформе.

Что проверяют. Платформы дают 5 851, 3 774 и 3 816 строк; общая строка — 13 441. Ошибка — суммировать уже возвращённые четыре строки ещё раз: получится 26 882, потому что итог сам содержит детали. Если спросят дальше: добавить тип события как второе поле ROLLUP и назвать уровни иерархии. Отдельный разбор ROLLUP и CUBE показывает, почему порядок колонок важен.

Попросите показать в результате is_total, даже если финальная таблица должна иметь только подписи. Он документирует, какую строку нужно исключить при суммировании деталей. Если в источнике когда-нибудь появится событие с пустой платформой, его группа и общий итог оба будут иметь NULL в platform; различать их по одному значению будет уже невозможно.

SELECT platform, GROUPING(platform) AS is_total, count(*) AS events
FROM app_events
WHERE event_name IN ('search','purchase')
  AND event_time >= TIMESTAMP '2026-07-04' AND event_time < TIMESTAMP '2026-07-06'
GROUP BY ROLLUP(platform)
ORDER BY GROUPING(platform),platform;

10. FULL JOIN двух списков клиентов

Условие. За 1–3 июля сверить клиентов с search и клиентов с purchase: кто есть в обоих множествах, только искал, только покупал. Каждая сторона сначала становится списком разных client_id, иначе многократный поиск размножит совпавшие строки. Результат — 3 495 в обоих, 7 864 только в поиске, 7 только в покупке.

Что проверяют. FULL JOIN должен сохранить обе непарные зоны. Условие по покупке в WHERE после соединения уничтожит 7 864 поисковых клиента и сделает вид, что расхождения нет. Если спросят дальше: покупка без поиска в этом окне не обязательно баг: поиск мог быть до 1 июля, на другом устройстве или не попасть в журнал. Сверка FULL JOIN разбирает границы такого вывода.

Проверка множеств: сумма трёх зон равна размеру объединения двух списков. Если после JOIN строк больше, почти всегда одна сторона осталась неуникальной по client_id; если меньше, WHERE превратил соединение в фильтр. В pandas merge(how="outer", indicator=True) воспроизводит эти три числа, но для настоящих пустых ключей его поведение отличается от SQL и требует отдельной оговорки.

WITH s AS (
  SELECT DISTINCT client_id FROM app_events WHERE event_name='search'
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
), p AS (
  SELECT DISTINCT client_id FROM app_events WHERE event_name='purchase'
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
)
SELECT count(*) FILTER (WHERE s.client_id IS NOT NULL AND p.client_id IS NOT NULL) AS matched,
       count(*) FILTER (WHERE s.client_id IS NOT NULL AND p.client_id IS NULL) AS search_only,
       count(*) FILTER (WHERE s.client_id IS NULL AND p.client_id IS NOT NULL) AS purchase_only
FROM s FULL JOIN p USING (client_id);

import pandas as pd

e = pd.read_parquet('app_events.parquet', columns=['event_time', 'event_name', 'client_id'])
e['event_time'] = pd.to_datetime(e['event_time'])
start, end = pd.Timestamp('2026-07-01'), pd.Timestamp('2026-07-04')
e = e.loc[e.event_time.ge(start) & e.event_time.lt(end)]
s = e.loc[e.event_name.eq('search'), ['client_id']].drop_duplicates()
p = e.loc[e.event_name.eq('purchase'), ['client_id']].drop_duplicates()
zones = s.merge(p, on='client_id', how='outer', indicator=True)['_merge'].value_counts()
print(int(zones['both']), int(zones['left_only']), int(zones['right_only']))

11. Падение недели и разложение по платформам

Условие. Сравните число разных доставленных покупок 13–19 и 20–26 июля по платформам. Дубли доставки убраны через DISTINCT client_id, platform, event_time, границы недель полуоткрыты. Android вырос с 3 191 до 3 266, web — с 2 578 до 2 694, а iOS упал с 2 420 до 2 041.

Что проверяют. Общая сумма упала лишь с 8 189 до 8 001, то есть на 188 покупок. Без сегментов легко пропустить iOS-провал на 379. Ошибка — сравнить неполные недели на краях журнала или считать сырые дубли. Если спросят дальше: до вывода об эффекте проверьте качество iOS-событий, долю ошибок и изменение состава пользователей; недельный разрез не доказывает причину.

Это разложение по платформам, а не объяснение причин. Android добавил 75 покупок, web — 116, вместе 191; iOS потерял 379, общий итог изменился на −188. Арифметика сходится, поэтому таблица не теряет платформу. Но она не нормирована на активную аудиторию и не разделяет органический спрос от технической ошибки. Следующий запрос должен быть диагностическим, а не ещё одним процентом поверх тех же чисел.

WITH purchases AS (
  SELECT DISTINCT client_id,platform,event_time FROM app_events
  WHERE event_name='purchase'
    AND event_time >= TIMESTAMP '2026-07-13' AND event_time < TIMESTAMP '2026-07-27'
)
SELECT platform,
       count(*) FILTER (WHERE event_time < TIMESTAMP '2026-07-20') AS before_week,
       count(*) FILTER (WHERE event_time >= TIMESTAMP '2026-07-20') AS after_week
FROM purchases GROUP BY platform ORDER BY platform;

12. Почему уникальные по дням не складываются

Условие. За 1–3 июля сравните сумму дневных уникальных клиентов с search и число разных клиентов всего периода. Каждый день человек может искать снова. Дневная сумма — 12 697, уникальных за три дня — 11 359; разница 1 338 — повторный учёт при сложении дневных групп.

Что проверяют. DISTINCT привязан к уровню группировки. Неправильный запрос sum(day_clients) в строке «всего» не эквивалентен count(DISTINCT client_id) на исходных трёх днях. Исправление — пересчитать уникальных на требуемом уровне. Если спросят дальше: для DAU, WAU, MAU отчёт должен сохранять три разных знаменателя; математически суммировать их в одну «аудиторию» нельзя.

Разница 1 338 — не число «повторных пользователей» в простом смысле: один и тот же клиент мог войти в дневную сумму два или три раза. Для числа людей, активных больше одного дня, потребуется отдельный слой client_id → count(DISTINCT day). Здесь разница измеряет лишние включения при суммировании, а не размер группы людей с повтором.

WITH e AS (
  SELECT event_time::date AS day, client_id FROM app_events
  WHERE event_name='search'
    AND event_time >= TIMESTAMP '2026-07-01' AND event_time < TIMESTAMP '2026-07-04'
)
SELECT (SELECT sum(n) FROM (SELECT count(DISTINCT client_id) AS n FROM e GROUP BY day) d) AS daily_sum,
       (SELECT count(DISTINCT client_id) FROM e) AS period_clients;

Частые вопросы и выбор следующей задачи

Нужно ли помнить все функции? Полезнее объяснить зерно и проверку: одно неверное соединение испортит даже правильно написанный percentile_cont. Синтаксис можно восстановить, если ясно, что должно быть в строке ответа.

Когда нужен pandas? Для той же локальной проверки: три примера выше воспроизводят числа SQL в ноутбуке. Но pandas merge сопоставляет пустые ключи иначе, поэтому при сверке настоящих данных это отдельная проверка.

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

С чего продолжить? Если сбивает вторая цена или медиана — начните с окон. Если путаются зоны FULL JOIN и подытоги — проверьте зерно и GROUPING. Если спорите о возврате — запишите момент конца наблюдения. Кнопка урока после статьи ведёт в практику по N-му значению и медиане.