Нужно написать запрос, который бы реализовал логику алгоритма рекомендаций фильмов, которые пользователи еще не смотрели.
На выходе должна получиться таблица: Пользователь, Рекомендованный фильм, Рейтинг рекомендации.
Что проверяет такая задача?
🔵Базовое знание SQL.
🔵Работу с алгоритмами рекомендаций
🔵Работу с агрегацией и моделированием данных.
Решение
1. Построим систему рекомендаций на принципе коллаборативной фильтрации.
Если пользователи А и Б выбирают какой-то фильм, то высока вероятность схожести их интересов и по другим фильмам.
2. Схожесть интересов пользователей определим как N%общих просмотренных фильмов.
То есть если у пользователя А N% общих просмотренных фильмов с пользователем Б, то мы можем рекомендовать пользователю А фильмы, которые посмотрел пользователь Б.
3. Рейтинг рекомендации построим индивидуально на основании количества просмотров фильма у пользователей со схожими интересами.
Подробное решение и результат описали в карточках.
Код может выглядеть следующим образом
with users as (
select
distinct user1_id,
user2_id
from
(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 (
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_id
group by users.user1_id, likes.movie_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
Маленький комментарий по задачи от нашего аналитика Надежды
Задача сложная, а решение неоднозначное и далеко от идеала. От того еще интереснее почитать ваши варианты в комментариях!
@leftjoin_career
#разбор_тестового