Рекомендательная матрица user-item interactions

Условие задания

**Контекст:** Стриминговый сервис строит рекомендательную систему на основе collaborative filtering. Нужно построить матрицу схожести пользователей по их взаимодействиям с контентом, рассчитать косинусное сходство между парами пользователей и предложить контент, который смотрели похожие пользователи.

**Данные:**

[см. код в задании]

**Задание:**
1. Вычислите implicit rating для каждой пары user-content: rating × 0.5 + watch_pct/100 × 0.3 + (1 if liked) × 0.2
2. Для каждой пары пользователей, у которых >= 5 общих content_id, рассчитайте cosine similarity
3. Для заданного user_id найдите топ-5 похожих пользователей
4. Предложите контент, который смотрели похожие пользователи, но не смотрел целевой пользователь

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE content (
    id SERIAL PRIMARY KEY,
    title VARCHAR(300) NOT NULL,
    genre VARCHAR(100) NOT NULL,
    release_year INT NOT NULL
);

CREATE TABLE user_interactions (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    content_id INT REFERENCES content(id),
    interaction_type VARCHAR(30) NOT NULL,  -- 'view', 'like', 'add_to_list', 'rate'
    rating INT,                             -- 1–5, NULL если не rate
    watch_pct INT,                          -- 0–100, процент просмотра
    created_at TIMESTAMP NOT NULL
);

Темы

collaborative filtering cosine similarity CROSS JOIN матрица

Подсказки

• Cosine similarity = SUM(a_i * b_i) / (SQRT(SUM(a_i²)) * SQRT(SUM(b_i²)))
• Сначала соберите implicit_rating в CTE, затем SELF JOIN для пар пользователей
• EXCEPT или NOT EXISTS для исключения уже просмотренного контента

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Рекомендательная матрица user-item interactions"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: collaborative filtering, cosine similarity, CROSS JOIN, матрица.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания