Netflix Content Co-Occurrence
ASKED IN INTERVIEWNetflix
Problem Statement
Netflix’s recommendation engine often looks at "Co-occurrence"—the frequency with which two different shows are watched by the same user. This helps in building a content graph for recommendations.
Your task is to find the top pairs of shows that have been watched by the same users.
Rules:
- A "Pair" consists of two different shows watched by the same user_id.
- To avoid duplicate pairs and self-pairing, ensure show_id_1 < show_id_2.
- Only include pairs that have been watched together by at least 2 distinct users.
- Output columns: show_title_1, show_title_2, co_watch_count.
- Sort the results by co_watch_count in descending order, then show_title_1 in ascending order.
Table Schema:
- Viewing_History: view_id, user_id, show_id, view_date.
- Shows: show_id, title, genre, release_year.
- User_Profiles: user_id, country, subscription_plan.
Tests your understanding of
Self Join, Aggregation, Market Basket Analysis and Ranking
Input Tables
Viewing_History
| view_id(INTEGER) | user_id(INTEGER) | show_id(INTEGER) | view_date(DATE) |
|---|---|---|---|
| 1 | 1 | 101 | 2026-01-01 |
| 2 | 1 | 102 | 2026-01-02 |
| 3 | 2 | 101 | 2026-01-05 |
| 4 | 2 | 102 | 2026-01-06 |
| 5 | 3 | 101 | 2026-01-10 |
| 6 | 3 | 103 | 2026-01-11 |
| 7 | 4 | 101 | 2026-01-12 |
| 8 | 4 | 102 | 2026-01-13 |
Shows
| show_id(INTEGER) | title(VARCHAR) |
|---|---|
| 101 | Stranger Things |
| 102 | The Crown |
| 103 | The Witcher |
User_Profiles
| user_id(INTEGER) | country(VARCHAR) |
|---|---|
| 1 | USA |
Expected Output
| show_title_1(VARCHAR) | show_title_2(VARCHAR) | co_watch_count(INTEGER) |
|---|---|---|
| Stranger Things | The Crown | 3 |
Tags
HardASKED IN INTERVIEWSelf JoinAggregationMarket Basket AnalysisRanking
25-30 min
31%