Пользователи, смотревшие видео 1 и 3, но не видео 2

Задача с собеседования, которая проверяет тонкую вещь: условие не на отдельную строку, а на весь набор просмотров пользователя. Наивный WHERE тут не сработает.

Задача

Есть лог просмотров видео watch_content(user_id, video_id, date) — одна строка это один просмотр.

Выведи пользователей, которые смотрели видео 1 и видео 3, но не смотрели видео 2.

Схема данных

CREATE TABLE watch_content (user_id int, video_id int, date date);

Один пользователь смотрит несколько видео — значит, условие нужно проверять по всей его истории просмотров, а не по одной записи.

Решение

SELECT user_id
FROM watch_content
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE video_id = 1) > 0
   AND COUNT(*) FILTER (WHERE video_id = 3) > 0
   AND COUNT(*) FILTER (WHERE video_id = 2) = 0
ORDER BY user_id;

Разбор

Первое, что нужно понять: «смотрел 1 и 3, но не 2» — это условие на множество просмотров пользователя, а не на строку. Написать WHERE video_id IN (1, 3) не выйдет: одна строка не может одновременно быть и видео 1, и видео 3, а про отсутствие видео 2 по одной строке вообще ничего не сказать.

Поэтому группируем по пользователю и формулируем условие через HAVING. Конструкция COUNT(*) FILTER (WHERE video_id = 1) считает, сколько раз в группе встретилось видео 1. Если больше нуля — пользователь его смотрел. Три условия вместе: видео 1 есть (> 0), видео 3 есть (> 0), видео 2 нет (= 0). AND между ними требует выполнения всех сразу.

То же самое можно записать через SUM(CASE WHEN video_id = 1 THEN 1 ELSE 0 END) или через bool_or — суть одна: агрегатное условие на группу. На данных песочницы под условие попадают пользователи 3 и 4; те, кто смотрел видео 2, отсеиваются. Про то, почему условия на группы живут в HAVING, а не в WHERE, — в вопросе чем WHERE отличается от HAVING.

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

user_id
---
3
4