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

Почему стоит прочитать эту статью?

Я участвовал в SQL-собеседованиях с обеих сторон около 20 раз за один поиск работы. Сценарий всегда одинаков: кандидаты знают SELECT, JOIN, могут написать WHERE. Но когда интервьюер просит удалить дубликаты, найти пропущенные строки или сессионировать поток событий — всё рушится. Не из-за сложности синтаксиса, а потому что кандидаты не понимают, что представляет каждая строка, прежде чем писать код.

32% вопросов на собеседованиях по Data Engineering проверяют GROUP BY, 29% — INNER JOIN, 21% — PARTITION BY, 15% — ROW_NUMBER. Эти 12 задач покрывают примерно 80% того, что реально спрашивают. Освоив их, вы опередите большинство кандидатов в live coding.

1. Клиенты, потратившие более $500

Задача: Дана таблица orders с колонками customer_id, order_date, amount. Найдите всех клиентов, чьи суммарные траты превышают $500. Верните customer_id и total_spent, отсортированные по сумме по убыванию.

SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 500
ORDER BY total_spent DESC;

Почему это важно: 70% кандидатов путают WHERE и HAVING. Порядок выполнения SQL: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Вы не можете отфильтровать агрегат в WHERE, потому что агрегат ещё не вычислен. Написание SUM(amount) > 500 в WHERE вызовет ошибку в PostgreSQL. Интервьюер проверяет не знание HAVING, а понимание того, когда выполняется каждое предложение.

2. Ловушка двойного счёта

Задача: Даны таблицы orders(order_id, customer_id, order_date) и order_items(item_id, order_id, product_id, quantity, price). Найдите для каждого клиента общую выручку и количество заказов. Один заказ может содержать несколько товаров.

WITH order_totals AS (
  SELECT order_id, customer_id, SUM(quantity * price) AS order_revenue
  FROM orders
  JOIN order_items USING (order_id)
  GROUP BY order_id, customer_id
)
SELECT customer_id, COUNT(*) AS num_orders, SUM(order_revenue) AS total_revenue
FROM order_totals
GROUP BY customer_id;

Почему это важно: 80% кандидатов проваливают задачу на агрегацию на собеседовании в Meta из-за двойного счёта. Если соединить orders с order_items и сразу сделать COUNT(DISTINCT order_id) вместе с SUM(quantity * price), количество может выглядеть правильно, но соединение произошло на неправильном уровне детализации. Решение — CTE: агрегируйте на каждом уровне отдельно, затем соединяйте результаты. Сильные кандидаты используют CTE для защиты; слабые — пропускают и удивляются, почему выручка в 3 раза выше.

3. Последняя запись по клиенту

Задача: Дана таблица customer_updates с колонками customer_id, updated_at, email, status. Верните только самую новую строку для каждого клиента.

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM customer_updates
)
SELECT customer_id, updated_at, email, status
FROM ranked
WHERE rn = 1;

Почему это важно: Этот паттерн встречается в 80% собеседований аналитиков и DE. Ловушка: кандидаты пытаются использовать GROUP BY с MAX(updated_at), а затем понимают, что не могут получить email и status без их агрегации. Оконные функции сохраняют всю строку, вычисляя статистику по группам. Интервьюер может спросить: «Что будет, если две строки имеют одинаковый updated_at?» ROW_NUMBER недетерминирован без дополнительной сортировки. Всегда добавляйте столбец для разрешения связей.

4. Детерминированное удаление дубликатов с разрешением связей

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

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id 
    ORDER BY updated_at DESC, ctid DESC  -- в продакшене используйте суррогатный ключ/последовательность
  ) AS rn
  FROM customer_updates
)
SELECT customer_id, updated_at, email, status
FROM ranked
WHERE rn = 1;

Почему это важно: Большинство кандидатов пишут ROW_NUMBER из задачи 3 и останавливаются. В реальном продакшене добавляют вторичную сортировку: ORDER BY updated_at DESC, source_sequence_number DESC, чтобы гарантировать одинаковый результат при каждом запуске. Без неё два выполнения на разных инстансах могут вернуть разные строки. ROW_NUMBER детерминирован в присвоении номеров, но недетерминирован в выборе строки при связях в ORDER BY. Назовите критерий разрешения связей, иначе запрос нестабилен.

5. Клиенты без заказов (анти-соединение)

Задача: Даны таблицы customers(customer_id, name) и orders(order_id, customer_id, order_date). Найдите всех клиентов, которые никогда не делали заказов.

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

Почему это важно: Анти-соединения показывают, понимаете ли вы пропуски. LEFT JOIN с IS NULL находит строки в A, у которых нет совпадения в B. Интервьюеры проверяют это, потому что «какие пользователи зарегистрировались, но не совершили конверсию» — тот же запрос. Следующий вопрос: «А NOT EXISTS сработает?» Да, и он семантически безопаснее, если ключи соединения могут содержать NULL. Современные планировщики обрабатывают оба варианта одинаково, выбор за ясностью.

6. NOT IN vs NOT EXISTS с NULL

Задача: Используя те же таблицы, объясните, почему этот запрос возвращает ноль строк, если в таблице orders есть NULL в customer_id:

-- Неправильно: возвращает 0 строк, если хоть один order имеет NULL customer_id
SELECT customer_id, name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- Правильно: NOT EXISTS корректно обрабатывает NULL
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Почему это важно: Это тихий убийца на SQL-собеседованиях. Если подзапрос содержит хотя бы один NULL, NOT IN возвращает ноль строк, потому что любое сравнение с NULL даёт UNKNOWN. Запрос выполняется без ошибки, возвращает результат, но результат неверный. Кандидаты, которые по умолчанию используют NOT EXISTS, сигнализируют, что уже обжигались в продакшене. Те, кто использует NOT IN — ещё нет.

7. Рекурсивный CTE: оргструктура

Задача: Дана таблица employees(employee_id, name, manager_id). Найдите всех сотрудников (прямых и косвенных подчинённых) под employee_id = 1. Верните employee_id, name и уровень глубины.

WITH RECURSIVE org AS (
  SELECT employee_id, name, manager_id, 0 AS depth
  FROM employees
  WHERE employee_id = 1
  UNION ALL
  SELECT e.employee_id, e.name, e.manager_id, org.depth + 1
  FROM employees e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT employee_id, name, depth
FROM org
ORDER BY depth, name;

Почему это важно: JPMorgan прямо спрашивает это. Рекурсивные CTE отделяют кандидатов, понимающих начальный член, рекурсивный член и условия завершения, от тех, кто угадывает. Рекурсивный шаг останавливается, когда возвращает ноль строк; если в данных есть циклические ссылки, получите бесконечный цикл. Упомяните это без подсказки — покажете опыт. Иронично, что рекурсивные CTE редко встречаются в продакшене, так как большинство иерархий неглубокие (3-5 уровней). Интервьюеры проверяют способность думать рекурсивно, а не то, будете ли вы это использовать.

8. Self-Join для последовательных дней входа

Задача: Дана таблица logins(user_id, login_date) (без дубликатов: одна строка на пользователя в день). Найдите пользователей, которые входили как минимум 3 дня подряд. Верните user_id и дату начала каждой серии.

WITH islands AS (
  SELECT user_id, login_date,
         login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)::int AS grp
  FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, COUNT(*) AS streak_length
FROM islands
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Почему это важно: Это классическая задача «пробелы и острова» в обличье продукта. Трюк с ROW_NUMBER минус дата работает, потому что последовательные даты дают одну и ту же константу при вычитании последовательного целого. Непоследовательные даты разрывают константу, создавая новую группу. Интервьюеры любят это: кандидаты, не видевшие паттерна, не могут его придумать под давлением; те, кто видел, должны объяснить, почему это работает. Следующий вопрос: «Что если login_date содержит дубликаты?» Ваш подход сломается. Сначала удалите дубликаты или используйте DENSE_RANK вместо ROW_NUMBER.

9. ROW_NUMBER vs RANK vs DENSE_RANK

Задача: Дана таблица sales(salesperson_id, region, revenue). Проранжируйте продавцов внутри региона по выручке. Покажите разницу в результатах всех трёх функций при наличии совпадений.

SELECT salesperson_id, region, revenue,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn,
       RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS drnk
FROM sales;
-- При revenue=500: ROW_NUMBER даёт 2,3 (произвольно); RANK даёт 2,2 затем пропуск до 4; DENSE_RANK даёт 2,2 затем 3

Почему это важно: 80% собеседований по Data Engineering включают вопросы по оконным функциям, но кандидаты чувствуют себя наименее подготовленными. Различие семантическое, не производительное; все три выполняются с похожей скоростью. ROW_NUMBER — для произвольного разрешения связей (удаление дубликатов). RANK — когда пропуски после связей допустимы (соревновательные баллы). DENSE_RANK — когда нужны непрерывные ранги. Реальный инцидент: дашборд рейтинга продуктов показывал позиции 1,2,3,4,5,6,6,8, потому что RANK пропустил 7 после связи. Руководство полдня разбиралось, пока кто-то не понял, что функция верна, но бизнесу нужен DENSE_RANK. Выбор не в данных, а в том, что нужно бизнесу.

10. Накопительный итог с правильным окном

Задача: Дана таблица transactions(account_id, txn_date, amount). Вычислите накопительный баланс по счёту, упорядоченный по дате транзакции. Затем объясните, почему LAST_VALUE часто даёт неожиданные результаты.

SELECT account_id, txn_date, amount,
       SUM(amount) OVER (
         PARTITION BY account_id 
         ORDER BY txn_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM transactions;

Почему это важно: Окно по умолчанию при наличии ORDER BY — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, которое обрабатывает связи как группу. ROWS обрабатывает каждую строку индивидуально. Для накопительных итогов это различие важно, когда две транзакции имеют одну дату. Крайний случай: LAST_VALUE без ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING возвращает текущую строку, а не последнюю в разделе. 70% первых попыток ошибаются. Интервьюеры проверяют спецификации окна, чтобы отделить тех, кто запомнил OVER(PARTITION BY...), от тех, кто понимает, как работают границы окна.

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

Задача: Дана таблица seat_reservations(seat_number) с занятыми местами (целые числа, необязательно последовательные). Найдите все пропуски (незарезервированные диапазоны). Верните начальное и конечное место каждого пропуска.

WITH numbered AS (
  SELECT seat_number,
         ROW_NUMBER() OVER (ORDER BY seat_number) AS rn
  FROM seat_reservations
),
gaps AS (
  SELECT seat_number - rn AS grp, MIN(seat_number) AS start_seat, MAX(seat_number) AS end_seat
  FROM numbered
  GROUP BY seat_number - rn
)
SELECT start_seat, end_seat
FROM gaps
WHERE start_seat < end_seat;
-- Дополнительно: найти все отсутствующие места в диапазоне
WITH all_seats AS (
  SELECT generate_series(1, (SELECT MAX(seat_number) FROM seat_reservations)) AS seat_number
)
SELECT seat_number AS missing_seat
FROM all_seats
WHERE seat_number NOT IN (SELECT seat_number FROM seat_reservations);

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

12. Сессионизация событий

Задача: Дана таблица events(user_id, event_time) с отметками времени событий. Сессия определяется как последовательность событий одного пользователя, где интервал между событиями не превышает 30 минут. Для каждой сессии найдите user_id, start_time, end_time и количество событий.

WITH with_lag AS (
  SELECT user_id, event_time,
         LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM events
),
session_starts AS (
  SELECT user_id, event_time,
         CASE WHEN prev_time IS NULL OR event_time - prev_time > INTERVAL '30 minutes'
              THEN 1 ELSE 0 END AS is_new_session
  FROM with_lag
),
session_groups AS (
  SELECT user_id, event_time,
         SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING) AS session_id
  FROM session_starts
)
SELECT user_id,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end,
       COUNT(*) AS events_count
FROM session_groups
GROUP BY user_id, session_id
ORDER BY user_id, session_start;

Почему это важно: Сессионизация — типичная задача в аналитике поведения пользователей. 90% кандидатов пытаются использовать циклы или процедурный подход. Оконные функции позволяют решить её элегантно и эффективно. Ключевые элементы: LAG для определения начала сессии, накопительная сумма для группировки сессий, финальная агрегация. Интервьюеры проверяют умение комбинировать оконные функции и понимание временных интервалов.

Что делать прямо сейчас

Выберите 3 задачи, которые показались самыми сложными, и напишите их вручную в редакторе. Не копируйте — набирайте каждую букву. Затем закройте решение и попробуйте воспроизвести по памяти. Повторяйте, пока не сможете объяснить каждую строку кода. После этого пройдите живое собеседование с реальным интервьюером на платформе Algolit — у нас есть тренажёр с автоматической проверкой и обратной связью.