## ЗАДАЧА

Есть таблица истории просмотров фильмов в онлайн-кинотеатре.

Два поля: Пользователь, Фильм

Множество строк: тысячи пользователей и фильмов.

Нужно сделать SQL, который бы реализовал логику алгоритма рекомендаций
фильмов к просмотру, которые пользователи еще не смотрели.

Логику рекомендаций предлагайте на свое усмотрение.

На выходе нужна таблица: Пользователь, Рекомендованный фильм, Рейтинг
рекомендации

## РЕШЕНИЕ

Система рекомендаций будет построена на принципе коллаборативной
фильтрации. Коллаборативная фильтрация позволяет предсказать интересы
пользователя на базе интересов другого пользователя. Основная идея
состоит в том, что если пользователи А и Б выбирают какой-то фильм, то
высока вероятность схожести их интересов и по другим фильмам.

В рамках данной задачи схожесть интересов пользователей определяется как
N% общих просмотренных фильмов. То есть если у пользователя А N% общих
просмотренных фильмов с пользователем Б, то мы можем рекомендовать
пользователю А фильмы, которые посмотрел пользователь Б. Рейтинг
рекомендации построен индивидуально для каждого пользователя на
основании количества просмотров фильма у пользователей со схожими
интересами.

**База данных **

В качестве базы данных используется таблица likes, которая имеет два
столбца – id пользователя (user\_id) и id фильма (movie\_id):
```
create table likes (
user_id int,
movie_id int
);
```
Пример базы данных

  | **user_id **    | **movie_id **  |
  |-----------------| ---------------|
  | 1               | 4              |
  | 1               | 8              |
  | 2               | 2              |
  | 2               | 3              |
  | 2               | 1              |
  | 2               | 6              |
  | 2               | 7              |
  | 2               | 9              |
  | 2               | 11             |
  | 2               | 12             |
  | 3               | 13             |
  | 3               | 6              |
  | 3               | 8              |
  | 3               | 3              |
  | 3               | 11             |
  | 3               | 2              |
  | 4               | 1              |
  | 4               | 7              |
  | 4               | 8              |
  | 4               | 11             |
  | 4               | 3              |
  | 5               | 1              |
  | 5               | 8              |
  | 5               | 7              |
  | 5               | 2              |
  | 5               | 3              |

SQL-запрос (PostgreSQL)
```
with users as (
/* 2. Таблица users содержит сгруппированную информацию о пользователях,
которые имеют >= 10% общих просмотренных фильмов */
select
distinct user1_id,
user2_id
from
/* 1. Кросс-джойн таблица following для всех пользователей и фильмов
со столбцом common_interest позволяет отследить какие фильмы
были просмотрены обоими пользователями */
(select
cus.user_id as user1_id,
ous.user_id as user2_id,
cus.movie_id as movie1_id,
ous.movie_id as movie2_id,
(cus.movie_id = ous.movie_id) as common_interest
from
likes as ous
join likes as cus
on cus.user_id <> ous.user_id) following
group by
user1_id, user2_id
having
((count(*) filter (where "common_interest")::float) / (count(*)::float)) >=
0.1
), user_likes as (
/* 3. Таблица user_likes содержит информацию о пользователях, фильмах
и количестве просмотров фильмов пользователей с общими интересами,
включая самого пользователя */
select users.user1_id, likes.movie_id, count(likes.user_id) as like_count
from users
left join likes on users.user2_id = likes.user_idgroup by users.user1_id, likes.movie_id
)
/* 4. Таблица user_likes фильтруется таким образом, чтобы
не рекомендовать пользователю уже просмотренные им фильмы.
Выводится результат рекомендации для каждого пользователя в финальном виде.
Значения отсортированы по убыванию id пользователя */
select
user_likes.user1_id as "Пользователь",
user_likes.movie_id as "Рекомендованный фильм",
user_likes.like_count as "Рейтинг рекомендации"
from user_likes
left join likes
on user_likes.user1_id = likes.user_id and user_likes.movie_id =
likes.movie_id
where likes.movie_id is null
order by user_likes.user1_id, user_likes.like_count desc
```

Результат

 | **Пользователь **  | **Рекомендованный фильм **  | **Рейтинг рекомендации ** |
 | -------------------| ----------------------------| --------------------------|
 | 1                  | 3                           | 2                         |
 | 1                  | 7                           | 2                         |
 | 1                  | 2                           | 1                         |
 | 1                  | 11                          | 1                         |
 | 2                  | 8                           | 2                         |
 | 3                  | 1                           | 2                         |
 | 3                  | 7                           | 2                         |
 | 4                  | 2                           | 3                         |
 | 4                  | 6                           | 2                         |
 | 4                  | 4                           | 1                         |
 | 4                  | 9                           | 1                         |
 | 4                  | 12                          | 1                         |
 | 4                  | 13                          | 1                         |
 | 5                  | 11                          | 3                         |
 | 5                  | 6                           | 2                         |
 | 5                  | 9                           | 1                         |
 | 5                  | 4                           | 1                         |
 | 5                  | 12                          | 1                         |
 | 5                  | 13                          | 1                         |

Решение на SQL Fiddle:
[*http://sqlfiddle.com/\#!17/3b4915/21/0*](http://sqlfiddle.com/#!17/3b4915/21/0)