Completion rate by content type
mediumCompletion rate by content type
Netflix SQL Interview Question
Netflix's product team wants to compare how often people finish movies with how often they finish episodes of a series. A view counts as finished when the person watched at least 90% of the title's runtime.
For each content_type, find the percentage of views that were finished, as completion_rate_pct, rounded to 1 decimal place. Use all views of that content type as the total.
Asked of
- Data Analyst
- Product Analyst
- Data Scientist
- BI Analyst
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
| content_type | completion_rate_pct |
|---|---|
| Movie | 75 |
| Series | 66.7 |
Explanation
There are four movie views in the example. One person watched only 60 of Midnight Heist's 118 minutes, which is less than 90%, so that view is not finished. The other three movie views were finished, which gives 75.0%. Four of the six series views were finished, which gives 66.7%.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Netflix
Difficulty
medium
Topic
conditional logic
Your query
Starting the SQL engine...