Условие

Дана таблица likes(user_id, movie_id) — история просмотров фильмов в онлайн-кинотеатре. Тысячи пользователей и фильмов. Напишите SQL, реализующий простую логику рекомендаций: «фильмы, которые вы ещё не смотрели, но смотрели похожие пользователи».

На выходе: пользователь, рекомендованный фильм, рейтинг рекомендации.

Решение

Подход

Простая item-based коллаборативная фильтрация: считаем «похожих» пользователей через долю общих фильмов (≥ 10%). Затем для каждого пользователя берём фильмы, просмотренные похожими, считаем сколько раз каждый фильм встретился у похожих и фильтруем те, что уже смотрел сам пользователь.

Реализация

WITH cross_pairs AS (
  -- Все пары пользователей и совпадения по фильму
  SELECT
    a.user_id  AS u1,
    b.user_id  AS u2,
    (a.movie_id = b.movie_id) AS common
  FROM likes a
  JOIN likes b ON a.user_id <> b.user_id
),
similar_users AS (
  SELECT u1, u2
  FROM cross_pairs
  GROUP BY u1, u2
  HAVING COUNT(*) FILTER (WHERE common)::float / COUNT(*) >= 0.1
),
candidate_movies AS (
  SELECT
    s.u1                            AS user_id,
    l.movie_id,
    COUNT(*)                        AS rating
  FROM similar_users s
  JOIN likes l ON l.user_id = s.u2
  GROUP BY s.u1, l.movie_id
)
SELECT c.user_id        AS "Пользователь",
       c.movie_id       AS "Рекомендованный фильм",
       c.rating         AS "Рейтинг рекомендации"
FROM candidate_movies c
LEFT JOIN likes seen
  ON seen.user_id = c.user_id AND seen.movie_id = c.movie_id
WHERE seen.movie_id IS NULL          -- фильм ещё не смотрели
ORDER BY c.user_id, c.rating DESC;

Подводные камни

Сложность кросс-джойна — O(n²·k). На реальных данных нужно ограничить хотя бы количеством общих фильмов (COUNT(common) >= 5) или использовать WHERE a.movie_id = b.movie_id для построения только реально пересекающихся пар.
Доля 10% — гиперпараметр. Малое число общих фильмов даёт ложные «совпадения» (user смотрел только 1 фильм, и тот же смотрел другой — 100% похожих).
Фильмы, которые посмотрели все соседи, могут быть просто популярны, а не релевантны — лучше делить на популярность фильма (TF-IDF логика).
Холодный старт: для нового пользователя без истории рекомендаций не будет.

Эталонный ответ

1) Найти похожих пользователей через долю общих фильмов; 2) собрать фильмы похожих и посчитать частоту; 3) исключить уже просмотренные.

CloudReports: SQL-рекомендации фильмов через коллаборативную фильтрацию