К содержимому
Войти

Python Road Map

📊 SQL для бэкендера

На собеседовании бэкендеру почти всегда дают SQL-задачу: две-три таблицы и просьба написать запрос прямо в редакторе. Проверяют не память на синтаксис, а понимание, в каком порядке база выполняет запрос и что происходит с NULL. На работе это те же вопросы: почему отчёт потерял половину пользователей и откуда в выборке дубли. Все примеры ниже запущены на PostgreSQL 16, в других базах синтаксис почти тот же

🧠 Как это устроено

Порядок выполнения

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

  1. WITH — вычисляются CTE
  2. FROM и JOIN — собирается набор строк
  3. WHERE — отбрасываются строки
  4. GROUP BY — строки сворачиваются в группы, считаются агрегаты
  5. HAVING — отбрасываются группы
  6. оконные функции
  7. SELECT — вычисляются выходные столбцы и псевдонимы
  8. DISTINCT, затем UNION и соседи
  9. ORDER BY
  10. LIMIT и OFFSET

Из этого списка выводятся ответы на несколько частых вопросов. Псевдоним из SELECT нельзя использовать в WHERE: на шаге 3 его ещё нет, и Postgres ответит column "dbl" does not exist. В ORDER BY можно, он идёт после. Postgres разрешает псевдоним и в GROUP BY, но это его расширение, а в HAVING выражение придётся повторить. Оконную функцию нельзя фильтровать в WHERE того же запроса по той же причине, нужен подзапрос или CTE

Виды JOIN

Все примеры дальше идут на двух таблицах. В users четыре человека: у Глеба нет заказов, у Веры не указан город. В orders семь заказов, у одного нет user_id, у Бориса один заказ отменён

JOINЧто вернётСтрок в примере
INNER JOINтолько пары, где условие совпало6
LEFT JOINвсе строки слева, справа NULL, если пары нет7
RIGHT JOINзеркально LEFT, на практике его переписывают в LEFT7
FULL JOINвсе строки обеих таблиц8
CROSS JOINкаждая строка с каждой, 4 × 7 = 2828

JOIN умножает строки. Если у пользователя три заказа, он появится в результате трижды, и sum по колонке из users вырастет втрое. Так в отчётах появляются завышенные цифры

GROUP BY и HAVING

После GROUP BY в SELECT можно писать только столбцы группировки и агрегаты. Остальное Postgres не пропустит: column "orders.status" must appear in the GROUP BY clause or be used in an aggregate function. Исключение одно: если сгруппировали по первичному ключу, остальные столбцы этой таблицы можно выводить, база знает, что они однозначны

WHERE фильтрует строки до группировки, HAVING — группы после. Условие на столбец группировки в HAVING формально работает, но база сначала сгруппирует строки, которые потом выкинет

Подзапросы и CTE

CTE (WITH name AS (...)) — именованный подзапрос, который читается сверху вниз. Раньше его называли «барьером оптимизации»: до PostgreSQL 12 CTE всегда вычислялся отдельно, и фильтр из внешнего запроса внутрь не проникал. С 12-й версии нерекурсивный CTE без побочных эффектов, использованный один раз, встраивается во внешний запрос, и план совпадает с обычным подзапросом. Вернуть старое поведение можно словом MATERIALIZED

explain (costs off)
with p as (select * from orders where status = 'paid')
select * from p where user_id = 1;
-- Seq Scan on orders
--   Filter: ((status = 'paid'::text) AND (user_id = 1))

С as materialized в плане появится CTE Scan on p: сначала все оплаченные заказы, потом фильтр по пользователю. На маленькой таблице разницы нет, на большой это потерянный индекс

WITH RECURSIVE обходит деревья: оргструктуру, категории, комментарии с ответами

with recursive sub as (
  select id, name, 1 as depth from employees where id = 2
  union all
  select e.id, e.name, s.depth + 1
  from employees e join sub s on e.manager_id = s.id
)
select name, depth from sub;

Первая часть — стартовая строка, вторая присоединяет подчинённых к уже найденным, пока новые строки появляются

Оконные функции

Агрегат сворачивает строки, оконная функция считает по группе, но оставляет каждую строку. partition by делит строки на окна, order by внутри задаёт порядок

select user_id, id, amount,
  row_number() over w as rn,
  rank()       over w as rnk,
  dense_rank() over w as drnk
from orders
where user_id is not null
window w as (partition by user_id order by amount desc);
user_idamountrnrnkdrnk
11500111
11500211
1500332

row_number нумерует подряд и при равных значениях порядок между ними не определён. rank даёт равным одно место и пропускает следующее, dense_rank не пропускает. Ещё пригодятся sum() over (order by ...) для накопительного итога и lag() или lead(), чтобы взять значение из предыдущей или следующей строки

🛠️ На практике

Задачи ниже повторяются на собеседованиях чаще остальных

Пользователи без заказов. Через NOT EXISTS или LEFT JOIN ... WHERE o.id IS NULL. Оба запроса вернули Глеба

select name from users u
where not exists (select 1 from orders o where o.user_id = u.id);

Топ-N в каждой группе. Два самых крупных заказа каждого пользователя: пронумеровать в окне, отфильтровать снаружи. Второй столбец в order by окна делает результат стабильным при равных суммах

with ranked as (
  select o.*, row_number() over (partition by user_id order by amount desc, id) as rn
  from orders o
)
select user_id, id, amount from ranked where rn <= 2;

Для одной строки на группу в Postgres есть короче: select distinct on (user_id) ... order by user_id, created_at desc вернёт последний заказ каждого пользователя

Вторая по величине сумма. select max(amount) from orders where amount < (select max(amount) from orders) или select distinct amount ... order by amount desc offset 1 limit 1. Без distinct второй вариант вернёт ту же максимальную сумму, если она встречается дважды

Дубли. group by user_id, amount having count(*) > 1 находит повторы. В примере их два: у Ани два заказа по 1500 и у Бориса два по 700

Несколько счётчиков за один проход. count(*) filter (where status = 'paid') и count(*) filter (where status = 'cancelled') в одном select вместо двух запросов или sum(case when ...)

SQL · Накопительный итог и разница с прошлым заказом

select created_at, amount,
  sum(amount) over (order by created_at, id) as running,
  amount - lag(amount) over (order by created_at, id) as diff
from orders
where status = 'paid';
--  created_at | amount | running | diff
--  2026-10-01 |    500 |     500 |
--  2026-10-03 |   1500 |    2000 | 1000
--  2026-10-05 |    700 |    2700 | -800

⚠️ Где ошибаются

Фильтр по правой таблице в WHERE после LEFT JOIN. Хотели посчитать оплаченные заказы у всех пользователей и написали left join orders o on o.user_id = u.id where o.status = 'paid'. У Глеба заказов нет, o.status равен NULL, условие не выполнилось, и Глеб пропал: LEFT JOIN молча стал INNER. Условие на правую таблицу ставят в ON: on o.user_id = u.id and o.status = 'paid', тогда Глеб остаётся с нулём

*`count() вместо count(o.id) после LEFT JOIN.** count(*) считает строки, и у Глеба будет 1, хотя заказов ноль. count(o.id) пропускает NULL` и даёт честный 0

NOT IN с NULL в подзапросе. where id not in (select user_id from orders) вернул пустой результат, хотя у Глеба заказов нет. В orders есть строка с user_id = NULL, а 4 <> NULL даёт не true, а NULL, и всё условие тоже NULL. Помогает NOT EXISTS или where user_id is not null в подзапросе

Сравнение с NULL через = и <>. where city <> 'Москва' вернул только Бориса: у Веры город NULL, и сравнение не дало true. Если NULL надо считать обычным значением, пиши city is distinct from 'Москва', тогда вернётся и Вера. where city = null не найдёт ничего никогда, нужно is null

Средние и NULL. avg(x) по значениям 10, NULL, 20 даёт 15: агрегаты пропускают NULL. Если отсутствие значения должно считаться нулём, это решение бизнеса, и его пишут явно: avg(coalesce(x, 0)) даст 10

🎯 Вопросы с собеседований

Чем WHERE отличается от HAVING?

WHERE отбрасывает строки до группировки и не видит агрегатов. HAVING отбрасывает группы после GROUP BY, в нём можно писать sum(amount) > 1000. Условие на столбец группировки, вроде user_id = 1, лучше ставить в WHERE: база не будет группировать лишние строки

Чем INNER JOIN отличается от LEFT JOIN?

INNER JOIN оставляет только пары, где условие совпало. LEFT JOIN оставляет все строки левой таблицы, а у кого пары нет, в столбцах правой будет NULL. Отсюда типичная ошибка: условие на правую таблицу в WHERE выкидывает эти строки, и LEFT становится INNER

Почему псевдоним из SELECT нельзя использовать в WHERE?

WHERE выполняется раньше SELECT, и в этот момент псевдонима ещё нет. Порядок такой: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Поэтому в ORDER BY псевдоним работает, а в WHERE выражение нужно повторить или вынести в подзапрос

Чем отличаются row_number, rank и dense_rank?

Все три нумеруют строки внутри окна. row_number даёт уникальные номера подряд даже при равных значениях. rank даёт равным одно место и пропускает следующие: 1, 1, 3. dense_rank не пропускает: 1, 1, 2. Для «топ-3 в каждой группе» берут row_number, если нужно ровно три строки, и dense_rank, если нужны три уровня значений

Что вернёт NOT IN, если в подзапросе есть NULL?

Ни одной строки. x NOT IN (1, NULL) раскрывается в x <> 1 AND x <> NULL, а сравнение с NULL даёт NULL, поэтому всё условие не бывает true. Безопасный вариант — NOT EXISTS, он работает построчно и на NULL не ломается

Чем CTE отличается от подзапроса и замедляет ли он запрос?

По результату ничем, CTE читается проще и может быть рекурсивным. До PostgreSQL 12 CTE всегда вычислялся отдельно и мешал оптимизатору. С 12-й версии CTE, на который ссылаются один раз, встраивается во внешний запрос, и план тот же. Если CTE использован дважды, он по умолчанию материализуется, это можно поменять через NOT MATERIALIZED

Как найти сотрудников, которые получают больше своего руководителя?

Соединить таблицу саму с собой: from employees e join employees m on m.id = e.manager_id where e.salary > m.salary. Здесь e сотрудник, а m его руководитель. Сотрудники без руководителя выпадут из INNER JOIN, и это правильно, сравнивать их не с кем

Чем UNION отличается от UNION ALL?

UNION убирает дубли, для этого базе нужно отсортировать или захешировать весь результат. UNION ALL просто склеивает наборы и работает быстрее. Если дублей быть не может или они нужны, бери UNION ALL

📚 Что почитать