Типы операций в процессах без операций типа 2

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

Задача

Ты аналитик в банке и работаешь с данными о внутренних процессах и операциях. Есть три таблицы:

• t(id) — типы операций: 1, 2, 3.
• p(pid) — процессы: 1..4.
• o(oid, pid, id) — операции: какая операция в каком процессе и какого типа.

Данные: процесс 1 содержит операции типов 1, 2, 3; процесс 2 — типы 1, 3; процесс 3 — типы 2, 3; процесс 4 — три операции типа 1.

Выведи в разрезе типов операций количество процессов, в которых нет операций типа 2.

Схема данных

CREATE TABLE t (id int);
CREATE TABLE p (pid int);
CREATE TABLE o (oid int, pid int, id int);

Обрати внимание на процесс 4: у него три операции одного типа 1 — это ловушка на дубли при подсчёте.

Решение

SELECT o.id AS op_type, COUNT(DISTINCT o.pid) AS process_count
FROM o
WHERE o.pid IN (
  SELECT p.pid
  FROM p
  WHERE NOT EXISTS (
    SELECT 1 FROM o o2 WHERE o2.pid = p.pid AND o2.id = 2
  )
)
GROUP BY o.id
ORDER BY o.id;

Разбор

Задача распадается на два шага, и это её главный урок — не пытаться решить всё одним движением.

Шаг первый — найти процессы без операций типа 2. Это анти-джойн: «оставь процесс, для которого не существует операции типа 2». NOT EXISTS (SELECT 1 FROM o o2 WHERE o2.pid = p.pid AND o2.id = 2) возвращает процесс ровно тогда, когда внутри него нет ни одной строки с id = 2. Под условие подходят процессы 2 и 4. То же можно записать через NOT IN или LEFT JOIN ... IS NULL, но NOT EXISTS надёжнее — он не спотыкается о NULL.

Шаг второй — по операциям этих процессов сгруппировать по типу и посчитать процессы. И вот тут ловушка: у процесса 4 три операции типа 1, но процесс-то один. Если написать COUNT(*), он насчитает три и завысит ответ. Правильно — COUNT(DISTINCT o.pid): считаем уникальные процессы, а не строки операций.

Итог: тип 1 встречается в двух процессах (2 и 4), тип 3 — в одном (2). ORDER BY o.id делает вывод предсказуемым. Про то, когда выбрать подзапрос, CTE или временную таблицу для такой многошаговой логики, — в вопросе подзапрос, CTE или временная таблица.

Ожидаемый результат

op_type | process_count
--- | ---
1 | 2
3 | 1