-- Способ 1: NOT EXISTS, предпочтительный и самый читаемый
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Способ 2: LEFT JOIN + IS NULL, классический антиджойн
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;         -- фильтр только по NOT NULL-колонке правой таблицы

-- Способ 3: EXCEPT, когда нужны только идентификаторы
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

-- Ловушка, так не писать:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);   -- пусто, если есть NULL

Построчно

• SELECT 1 в EXISTS ничего не вычисляет: планировщику важно только «есть ли хоть одна строка». Что писать, SELECT * или SELECT 1, дело вкуса, разницы в плане нет.
• Условие связи o.customer_id = c.id делает подзапрос коррелированным, и как раз поэтому PostgreSQL может превратить его в Anti Join: один проход вместо подзапроса на каждую строку.
• В варианте с LEFT JOIN фильтр WHERE o.id IS NULL должен указывать на колонку, которая в самой таблице orders не бывает NULL, обычно на первичный ключ. Если взять o.comment IS NULL, в выборку попадут ещё и клиенты с заказами без комментария.

Почему NOT IN ломается

SQL живёт в трёхзначной логике. id NOT IN (1, 2, NULL) раскрывается в id <> 1 AND id <> 2 AND id <> NULL. Последнее сравнение даёт UNKNOWN, а TRUE AND UNKNOWN = UNKNOWN, и строка не проходит фильтр WHERE. Итог: если в подзапросе есть хотя бы один NULL, результат пуст для всех строк, причём без ошибки и без предупреждения. А колонка orders.customer_id вполне может быть nullable (гостевые заказы).

Второй минус известен меньше: NOT IN с подзапросом PostgreSQL в Anti Join не превращает вовсе - мешает семантика NULL - и выполняет как подплан: хешированный, если результат влез в work_mem, иначе построчный. «Починить» можно через WHERE customer_id IS NOT NULL в подзапросе, но правильный вывод другой: для антиджойна используем NOT EXISTS, а NOT IN оставляем для списков констант.

-- 1. Найти: какие email задублированы и сколько раз
SELECT lower(email) AS email, count(*) AS n, min(id) AS keep_id, array_agg(id) AS all_ids
FROM users
GROUP BY lower(email)
HAVING count(*) > 1
ORDER BY n DESC;

-- 2. Посмотреть сами строки-дубликаты целиком
SELECT * FROM users
WHERE lower(email) IN (SELECT lower(email) FROM users GROUP BY lower(email) HAVING count(*) > 1)
ORDER BY lower(email), id;

-- 3. Удалить, оставив в каждой группе самую раннюю (по created_at, при равенстве по меньшему id)
DELETE FROM users u
USING (
    SELECT id,
           row_number() OVER (PARTITION BY lower(email) ORDER BY created_at, id) AS rn
    FROM users
) d
WHERE u.id = d.id AND d.rn > 1;

-- 4. Закрепить результат, чтобы проблема не вернулась
CREATE UNIQUE INDEX CONCURRENTLY users_email_uniq ON users (lower(email));

Построчно

• PARTITION BY lower(email) группирует по нормализованному значению. Дубликаты почти всегда выглядят как Ivan@mail.ru и ivan@mail.ru, и без lower() ты их не найдёшь.
• ORDER BY created_at, id внутри окна задаёт, какая запись выживет. Решает тут бизнес, а не техника: оставить самую старую (сохранить исходный id) или самую свежую (сохранить актуальные данные). Тай-брейкер id обязателен, иначе при одинаковом created_at выбор недетерминирован.
• DELETE ... USING даёт join в DELETE (синтаксис PostgreSQL). Подзапрос вычисляется один раз, а на больших объёмах это важно.
• Уникальный индекс строим по lower(email), чтобы он совпадал с логикой дедупликации, и с CONCURRENTLY, чтобы не блокировать запись на проде.

Альтернативы и почему они хуже

-- Через ctid: работает без суррогатного ключа, но некрасиво и небезопасно при UPDATE
DELETE FROM users a USING users b
WHERE a.email = b.email AND a.ctid > b.ctid;
-- ctid: физический адрес версии строки, он меняется при UPDATE и VACUUM FULL.
-- Годится только для одноразовой чистки, но не для хранения ссылок.

-- Через min(id): проще, но нельзя выбрать «оставить последнюю» и медленнее на больших таблицах
DELETE FROM users
WHERE id NOT IN (SELECT min(id) FROM users GROUP BY email);
-- Плюс здесь снова NOT IN: попади в список NULL, и не удалится ничего, причём без ошибки.

-- Самый безопасный вариант для очень больших таблиц: пересоздание
CREATE TABLE users_new AS
SELECT DISTINCT ON (lower(email)) * FROM users ORDER BY lower(email), created_at, id;
-- затем переключаем таблицы в одной транзакции

-- Вариант A: топ-3 категории всего, хватит LIMIT
SELECT c.id, c.name, sum(oi.price * oi.qty) AS revenue
FROM order_items oi
JOIN orders     o ON o.id = oi.order_id
JOIN categories c ON c.id = oi.category_id
WHERE o.status = 'paid'
  AND o.created_at >= date_trunc('month', now()) - interval '1 month'
  AND o.created_at <  date_trunc('month', now())
GROUP BY c.id, c.name
ORDER BY revenue DESC
LIMIT 3;

-- Вариант B: топ-3 категории в каждом регионе, нужно окно
WITH revenue AS (
    SELECT o.region_id,
           c.id   AS category_id,
           c.name AS category_name,
           sum(oi.price * oi.qty) AS revenue
    FROM order_items oi
    JOIN orders     o ON o.id = oi.order_id
    JOIN categories c ON c.id = oi.category_id
    WHERE o.status = 'paid'
      AND o.created_at >= date_trunc('month', now()) - interval '1 month'
      AND o.created_at <  date_trunc('month', now())
    GROUP BY o.region_id, c.id, c.name
),
ranked AS (
    SELECT *,
           row_number() OVER (PARTITION BY region_id ORDER BY revenue DESC, category_id) AS rn
    FROM revenue
)
SELECT region_id, category_id, category_name, revenue
FROM ranked
WHERE rn <= 3
ORDER BY region_id, rn;

Построчно

• Границы периода задавай полуинтервалом >= начало AND < следующее начало. Это единственный корректный способ: BETWEEN с '2026-07-31 23:59:59' теряет последнюю секунду, а с '2026-08-01' захватывает лишнюю строку ровно в полночь.
• Условие по дате ставь на колонку, а не на функцию от неё. WHERE date_trunc('month', o.created_at) = ... убивает индекс и, если таблица партиционирована, ещё и pruning.
• o.status = 'paid' нужен почти всегда: выручку считают по оплаченным, а не по созданным заказам. Скажи это вслух: на этом держится половина впечатления от ответа.
• row_number() нумерует внутри PARTITION BY region_id, сортировка по revenue DESC с тай-брейкером category_id для детерминизма.
• Фильтр WHERE rn <= 3 живёт во внешнем запросе: оконные функции вычисляются после WHERE, поэтому внутри того же уровня фильтровать по rn нельзя.

Альтернативы и почему они хуже

-- Вариант A: оконная функция. Один проход по orders.
WITH ranked AS (
    SELECT o.*,
           row_number() OVER (PARTITION BY o.customer_id
                              ORDER BY o.created_at DESC, o.id DESC) AS rn
    FROM orders o
)
SELECT id, customer_id, created_at, total
FROM ranked
WHERE rn <= 5
ORDER BY customer_id, rn;

-- Вариант B: LATERAL делает для каждого клиента точечный index scan на 5 строк.
SELECT c.id AS customer_id, o.id, o.created_at, o.total
FROM customers c
CROSS JOIN LATERAL (
    SELECT o.id, o.created_at, o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.created_at DESC, o.id DESC
    LIMIT 5
) o
ORDER BY c.id, o.created_at DESC;

-- Индекс, без которого ни один вариант не работает хорошо:
CREATE INDEX orders_cust_created_idx ON orders (customer_id, created_at DESC, id DESC);

Построчно

• PARTITION BY customer_id начинает нумерацию заново для каждого клиента.
• ORDER BY created_at DESC, id DESC ставит свежие первыми; id нужен как тай-брейкер, иначе два заказа с одинаковой меткой времени будут нумероваться случайным образом и результат станет нестабильным между запусками.
• WHERE rn <= 5 обязан быть уровнем выше: окно вычисляется после WHERE.
• В варианте B CROSS JOIN LATERAL позволяет подзапросу ссылаться на c.id из внешней таблицы, в этом и смысл LATERAL. Если нужны и клиенты без заказов, пиши LEFT JOIN LATERAL ... ON true.

Что выбрать: важная развилка

В цифрах: если у 1000 клиентов по 10 000 заказов, оконный вариант отсортирует 10 млн строк, чтобы выбросить 9 995 000. LATERAL с индексом (customer_id, created_at DESC) сделает 1000 index scan-ов по 5 строк. Выходят секунды против миллисекунд. Если же клиентов миллион и у каждого по 3 заказа, выигрывает окно: один проход дешевле миллиона nested loop.

-- Правильно: оконная функция, один проход по таблице
SELECT id, name, dept_id, salary, dept_avg
FROM (
    SELECT e.id, e.name, e.dept_id, e.salary,
           avg(e.salary) OVER (PARTITION BY e.dept_id) AS dept_avg
    FROM employees e
) t
WHERE salary > dept_avg
ORDER BY dept_id, salary DESC;

-- Так обычно пишут: коррелированный подзапрос
SELECT e.* FROM employees e
WHERE e.salary > (
    SELECT avg(salary) FROM employees WHERE dept_id = e.dept_id
);

-- Третий вариант: JOIN с агрегатом. Читается лучше окна, работает почти так же.
SELECT e.id, e.name, e.dept_id, e.salary, a.dept_avg
FROM employees e
JOIN (
    SELECT dept_id, avg(salary) AS dept_avg
    FROM employees GROUP BY dept_id
) a ON a.dept_id = e.dept_id
WHERE e.salary > a.dept_avg;

Построчно

• avg(salary) OVER (PARTITION BY dept_id) пишется без ORDER BY внутри окна. Добавишь ORDER BY и получишь не среднее по отделу, а нарастающее среднее: рамка по умолчанию станет «от начала раздела до текущей строки» (вместе с равными ей по ORDER BY). Ошибка тихая и очень частая.
• Оконная функция не схлопывает строки: каждая строка сохраняется, к ней просто приписывается агрегат её раздела. Поэтому salary и dept_avg можно сравнить в одной строке.
• Фильтр снова уходит во внешний запрос (окно вычисляется после WHERE).
• Подзапрос можно оформить как CTE: так читается лучше, а в PostgreSQL 12+ он инлайнится и плана не портит.

Почему коррелированный подзапрос хуже

Формально современный планировщик PostgreSQL иногда умеет его развернуть, но полагаться на это нельзя: в общем случае подзапрос выполняется на каждую строку внешней таблицы. Для 100 000 сотрудников в 50 отделах это 100 000 агрегаций вместо 50. В плане это видно как SubPlan с большим loops, и время равно actual time × loops, а не тому, что показано в узле.

-- Вниз по дереву: все подчинённые сотрудника $1, на любую глубину
WITH RECURSIVE subordinates AS (
    -- Якорь: с кого начинаем
    SELECT e.id, e.name, e.manager_id, 1 AS depth,
           ARRAY[e.id] AS path
    FROM employees e
    WHERE e.id = $1

    UNION ALL

    -- Рекурсивная часть: те, чей менеджер уже найден на прошлом шаге
    SELECT e.id, e.name, e.manager_id, s.depth + 1,
           s.path || e.id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
    WHERE NOT e.id = ANY(s.path)    -- защита от цикла
      AND s.depth < 50              -- страховка на всякий случай
)
SELECT repeat('  ', depth - 1) || name AS tree, id, depth, path
FROM subordinates
ORDER BY path;

-- Вверх по дереву: вся цепочка руководителей конкретного сотрудника
WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE id = $1
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.lvl + 1
    FROM employees e JOIN chain c ON e.id = c.manager_id
    WHERE c.lvl < 50
)
SELECT * FROM chain ORDER BY lvl;

-- Число подчинённых на каждом уровне: тот же WITH RECURSIVE subordinates,
-- только финальный SELECT другой (отдельным запросом CTE не виден)
SELECT depth, count(*) FROM subordinates GROUP BY depth ORDER BY depth;

Как это выполняется на самом деле

1. Выполняется якорь, результат кладётся в рабочую таблицу и в итоговую.
2. Рекурсивная часть выполняется, но subordinates внутри неё означает только результат предыдущей итерации, а не весь накопленный результат. На этой тонкости и ловят.
3. Новые строки добавляются к итогу и становятся рабочей таблицей следующего шага.
4. Шаги повторяются, пока очередной не вернёт ноль строк.

Защита от циклов - три способа

• Массив пути path + WHERE NOT e.id = ANY(s.path) точнее всех: отсекает именно повторное посещение и заодно даёт готовый путь для сортировки дерева.
• Ограничение глубины WHERE depth < 50 - грубо, но надёжно, и защищает ещё и от «слишком глубокого» легального дерева.
• Синтаксис CYCLE (PostgreSQL 14+): ... CYCLE id SET is_cycle USING path. Это стандартный SQL, он делает то же самое декларативно.

-- Вариант 1: DENSE_RANK даёт второе по величине значение (правильный дефолт)
SELECT DISTINCT salary
FROM (SELECT salary, dense_rank() OVER (ORDER BY salary DESC) AS dr FROM employees) t
WHERE dr = 2;

-- Вариант 2: подзапрос с MAX работает без оконных функций, годится и для MySQL 5.x
SELECT max(salary) FROM employees
WHERE salary < (SELECT max(salary) FROM employees);

-- Вариант 3: коррелированный подзапрос ищет «значение, больше которого ровно одно значение»
SELECT DISTINCT e.salary FROM employees e
WHERE (SELECT count(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e.salary) = 1;

-- Вариант 4: если нужна вторая строка, а не второе значение
SELECT id, name, salary
FROM (SELECT e.*, row_number() OVER (ORDER BY salary DESC, id) AS rn FROM employees e) t
WHERE rn = 2;

-- Для N-го места: dense_rank() = N. Второй вариант так не обобщается.

Разбор вариантов

Что проверяют на самом деле

• Уточняешь ли требования. Кандидат, который сразу пишет LIMIT 1 OFFSET 1, не задумываясь о дубликатах, отвечает хуже того, кто спросил «а если максимум встречается дважды?».
• Понимаешь ли разницу rank / dense_rank / row_number. Ради неё вопрос и задают.
• Что вернётся, если сотрудников меньше двух. Оконные варианты вернут ноль строк, вариант с max() - одну строку со значением NULL. Разница критична для кода: QueryRow().Scan() в первом случае отдаст sql.ErrNoRows, во втором - успешно просканирует NULL и упадёт, если приёмник int, а не sql.NullInt64.
• Зачем вообще запрет на LIMIT. Ограничение искусственное: его ставят, чтобы вытащить из тебя оконные функции. Скажи это прямо: «в продакшене я бы написал ORDER BY salary DESC LIMIT 1 OFFSET 1 с индексом по salary, но раз нельзя - вот вариант через dense_rank».