Most-watched title in each country
hardMost-watched title in each country
Netflix SQL Interview Question
Netflix shows a Top 10 row that is different in every country. The content team wants to know which title was watched the most in each country.
For each country, find the title_name with the most total minutes watched, and that total as total_minutes. Each country has one clear top title, so you do not need to handle ties.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- Analytics Engineer
- Data Engineer
viewing_activityTable16 rows
| Column Name | Type |
|---|---|
| view_id | BIGINT |
| profile_id | BIGINT |
| title_id | BIGINT |
| country | VARCHAR |
| watch_date | DATE |
| minutes_watched | BIGINT |
titlesTable6 rows
| Column Name | Type |
|---|---|
| title_id | BIGINT |
| title_name | VARCHAR |
| content_type | VARCHAR |
| genre | VARCHAR |
| runtime_minutes | BIGINT |
viewing_activityExample Input
| view_id | profile_id | title_id | country | watch_date | minutes_watched |
|---|---|---|---|---|---|
| 1 | 1001 | 1 | United States | 2024-08-01 | 118 |
| 2 | 1002 | 4 | United States | 2024-08-01 | 58 |
| 3 | 1003 | 4 | United States | 2024-08-02 | 30 |
| 4 | 1004 | 3 | United States | 2024-08-02 | 45 |
| 5 | 1005 | 1 | Brazil | 2024-08-02 | 60 |
| 6 | 1006 | 6 | Brazil | 2024-08-03 | 52 |
| 7 | 1007 | 6 | Brazil | 2024-08-03 | 52 |
| 8 | 1008 | 2 | Brazil | 2024-08-03 | 92 |
| 9 | 1009 | 5 | India | 2024-08-04 | 101 |
| 10 | 1010 | 3 | India | 2024-08-04 | 20 |
titlesExample Input
| title_id | title_name | content_type | genre | runtime_minutes |
|---|---|---|---|---|
| 1 | Midnight Heist | Movie | Thriller | 118 |
| 2 | Ocean Deep | Movie | Documentary | 92 |
| 3 | Kitchen Wars | Series | Reality | 45 |
| 4 | The Last Colony | Series | Sci-Fi | 58 |
| 5 | Laugh Track | Movie | Comedy | 101 |
| 6 | Crown of Ash | Series | Drama | 52 |
Example Output
| country | title_name | total_minutes |
|---|---|---|
| Brazil | Crown of Ash | 104 |
| India | Laugh Track | 101 |
| United States | Midnight Heist | 118 |
Explanation
In Brazil, Crown of Ash was watched twice for 52 minutes each, a total of 104 minutes. That beats Ocean Deep, even though its single 92-minute view was the longest in Brazil. The ranking is based on total minutes, not on individual views.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Netflix
Difficulty
hard
Topic
window functions
Your query
Starting the SQL engine...