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

Найти пропущенные значения в последовательности id и пользователей без единого заказа.

Поиск «дыр» в последовательности и «строк без связанных» — две задачи на отсутствие данных, обе решаются через сравнение или анти-джойн.

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

Есть таблица с id 1,2,4,5,7 — каких чисел не хватает? Идея: число n — начало пропуска, если n+1 в таблице нет, но дальше значения ещё есть. Удобный приём — LEAD: сравнить значение со следующим. Если разрыв больше 1 — между ними дыра.

CREATE TABLE seq (id INTEGER PRIMARY KEY);
INSERT INTO seq (id) VALUES (1), (2), (4), (5), (7), (8);

WITH pairs AS (
    SELECT id AS cur,
           LEAD(id) OVER (ORDER BY id) AS nxt
    FROM seq
)
SELECT cur + 1 AS propusk_ot,
       nxt - 1 AS propusk_do
FROM pairs
WHERE nxt - cur > 1            -- между cur и nxt есть разрыв
ORDER BY cur;

Вывод:

3|3
6|6

Разрывы: между 2 и 4 пропущена 3 (диапазон 3..3), между 5 и 7 — 6 (диапазон 6..6). LEAD заглядывает в следующее значение, а условие nxt - cur > 1 ловит дыру. Так находят пропущенные номера счетов, билетов, заказов.

Пользователи без заказов

Это анти-джойн из раздела про JOIN, и его очень любят спрашивать. «Кто не сделал ни одного заказа» — через NOT EXISTS:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Аня'), ('Борис'), ('Вера'), ('Гена');

CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER);
INSERT INTO orders (user_id) VALUES (1), (1), (3);

SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
)
ORDER BY u.name;

Вывод:

Борис
Гена

Заказы есть у id 1 (Аня) и 3 (Вера). Без заказов остались Борис и Гена — их и вернул NOT EXISTS. Эквивалентно работает LEFT JOIN ... WHERE o.id IS NULL.

Связанный вопрос: кто заказывал в этом месяце, но не в прошлом

Развитие темы — «ушедшие» или «новые» пользователи. Это два анти-джойна между подмножествами. Логика та же: NOT EXISTS по соответствующему срезу. Понимание анти-джойна закрывает целый класс таких вопросов.

Итог

Пропуски в последовательности: LEAD + условие следующее - текущее > 1.
Строки без связанных (пользователи без заказов): NOT EXISTS или LEFT JOIN ... IS NULL.
Оба — задачи «на отсутствие»; ключ к ним — анти-джойн и сравнение соседей.