Задачи по SQL с решениями: 8 типовых с собеседования

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

Решения на PostgreSQL. Данные:

users

orders

1. Три самых дорогих заказа

Условие. Вывести id и сумму трёх самых дорогих заказов.

SELECT id, amount
FROM orders
ORDER BY amount DESC
LIMIT 3;

Ответ: 11 (2400), затем два заказа по 1200 — 12 и 13.

Что проверяют. Разминка, но с подвохом на внимательность: у двух заказов сумма одинаковая, и какой из них окажется третьим, база решает сама. Если результат должен быть воспроизводимым, сортировку надо доопределить — ORDER BY amount DESC, id. На проде эта мелочь всплывает как «отчёт каждый раз разный», хотя данные не менялись.

2. Клиенты без единого заказа

Условие. Найти клиентов, которые ни разу не оформляли заказ.

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Ответ: Вера.

Что проверяют. Понимание антиджойна. Типичная ошибка — взять INNER JOIN и удивиться пустому результату: он по определению выбрасывает строки без пары, то есть ровно тех, кого ищем. Второй вариант ответа — NOT EXISTS, он равноценен. А вот NOT IN (SELECT user_id FROM orders) — ловушка: если в user_id окажется NULL, запрос вернёт пустоту. Подробности в разборе LEFT JOIN.

3. Средний чек по городам

Условие. Средний чек по городу, считать только оплаченные заказы.

SELECT u.city, round(avg(o.amount), 2) AS avg_amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.city;

Что проверяют. Место фильтра. WHERE o.status = 'paid' отсекает отменённый заказ Бориса до группировки — это то, что нужно. Если вынести условие в HAVING, оно применится к уже посчитанным группам, и отменённый заказ успеет испортить среднее. Правило: WHERE фильтрует строки, HAVING — готовые группы. Разбор целиком, вместе с ошибкой «column must appear in the GROUP BY clause», в статье про GROUP BY и HAVING.

Следом обычно просят «оставить только города со средним выше 1000» — вот здесь как раз нужен HAVING avg(o.amount) > 1000, потому что фильтр относится к результату агрегата. В ответе останется одна Москва.

4. Второй по величине заказ

Условие. Найти вторую по величине сумму заказа.

SELECT DISTINCT amount
FROM orders
ORDER BY amount DESC
OFFSET 1 LIMIT 1;

Ответ: 1200.

Что проверяют. Слышишь ли разницу между «вторая по величине сумма» и «вторая строка в отсортированном списке». Без DISTINCT ответ был бы тоже 1200, но по случайности — если бы два самых дорогих заказа совпали по сумме, запрос без DISTINCT вернул бы ту же максимальную сумму и выдал бы её за вторую.

Тот же результат через окно, и он масштабируется на «второй в каждой категории»:

SELECT amount FROM (
  SELECT amount, dense_rank() OVER (ORDER BY amount DESC) AS rnk
  FROM orders
) t WHERE rnk = 2;

Здесь важен именно dense_rank(): row_number() пронумеровал бы одинаковые суммы разными номерами и дал бы неверный ответ.

5. Найти дубли

Условие. Найти подозрительные пары «клиент + сумма», встречающиеся больше одного раза, — кандидаты на двойное списание.

SELECT user_id, amount, count(*) AS cnt
FROM orders
GROUP BY user_id, amount
HAVING count(*) > 1;

Ответ: клиент 2, сумма 1200, два заказа.

Что проверяют. Умение группировать по составному ключу и фильтровать агрегат через HAVING. Частая ошибка — попытка написать WHERE count(*) > 1: WHERE выполняется до группировки, счётчика в этот момент ещё нет, и запрос падает.

6. Последний заказ каждого клиента

Условие. Для каждого клиента вывести его самый свежий заказ.

SELECT id, user_id, amount, created_at
FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

Что проверяют. Знание окон и порядка выполнения запроса. Подзапрос здесь обязателен: оконные функции считаются после WHERE, поэтому написать WHERE row_number() OVER (...) = 1 нельзя — запрос не выполнится.

Классический альтернативный ответ — соединение с подзапросом MAX(created_at) по клиенту. Он тоже верен, но ломается при равных датах: вернёт обе строки вместо одной.

7. Выручка нарастающим итогом

Условие. По дням вывести выручку и накопленный итог, только по оплаченным.

SELECT created_at AS day,
       sum(amount) AS day_revenue,
       sum(sum(amount)) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders
WHERE status = 'paid'
GROUP BY created_at
ORDER BY created_at;

Что проверяют. Сразу три вещи: что окно можно ставить поверх агрегата (sum(sum(...)) — не опечатка, внутренний схлопывает день, внешний идёт по дням), что ORDER BY внутри OVER превращает сумму в накопительную, и что рамку лучше задать явно. Про разницу ROWS и RANGE — в статье про оконные функции.

8. Прирост выручки месяц к месяцу

Условие. Помесячная выручка и абсолютный прирост к предыдущему месяцу.

SELECT date_trunc('month', created_at)::date AS month,
       sum(amount) AS revenue,
       sum(amount) - lag(sum(amount)) OVER (ORDER BY date_trunc('month', created_at)) AS growth
FROM orders
WHERE status = 'paid'
GROUP BY month
ORDER BY month;

Что проверяют. Работу с датами плюс lag(). У первого месяца предыдущего нет, поэтому NULL — и это правильный ответ, а не дырка в данных. Если по продукту ноль уместнее, lag(sum(amount), 1, 0) подставит его сам.

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

Что дальше

Эти восемь задач закрывают ядро того, что спрашивают на секции SQL: фильтрация, группировка, соединения, окна. Дальше идут подзапросы, CTE и оптимизация — но их обычно спрашивают уже после того, как убедились, что базовое ты не путаешь.

Разобранные здесь запросы стоит написать руками с автопроверкой: чтение решения и его набор с нуля — разные навыки, и на собеседовании проверяют второй.