Digits
n Data
Netflix SQL Interview Question 01

Identify
VIP
Users

Identify the top 10 users with the highest total hours watched in the last 30 days.

Schema

Table
Setup

Column NameTypeDescription
activity_idintegerUnique watch activity identifier.
user_idintegerUser who watched content.
watch_datedateDate the user watched content.
hours_watcheddecimalHours watched in the activity.

Sample Data

Input
Output

Sample Input: watching_activity
activity_iduser_idwatch_datehours_watched
11012024-01-012.5
21022024-01-021.0
31012024-01-033.0
41032024-01-044.0
51022024-01-052.0
61042024-01-065.0
71012024-01-071.5
81052024-01-082.2
Expected Output
user_idtotal_hours
1017.0
1045.0
1034.0
1023.0
1052.2

SQL Editor

Run
Query

postgresql
Waiting for query

user_idtotal_hours
1017.0
1045.0
1034.0
1023.0
1052.2

Hints

Unlock
Clues

Hint 01: Start from watching_activity only.
Hint 02: Use SUM(hours_watched) grouped by user_id.
Hint 03: Order the totals descending and LIMIT 10.

Solution

Locked
Answer

Solution is locked until you decide to reveal it. Try the editor first, then open this when you want the reference answer.

SELECT
  user_id,
  SUM(hours_watched) AS total_hours
FROM watching_activity
WHERE watch_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
ORDER BY total_hours DESC
LIMIT 10;

Explanation

Step By
Step

01

Filter to the last 30 days of watch activity.

02

Aggregate hours_watched for each user_id.

03

Sort by total_hours descending and keep the first 10 rows.

Related Questions

Keep
Solving