Coding questions · Meta
Aggregate Netflix metrics in SQL
This question evaluates a candidate's proficiency in SQL data aggregation and construction of cumulative metrics for streaming analytics, including handling alignment of records when identifiers may appear in only one dataset.
Question
Netflix video-streaming analytics SQL:
Write a simple aggregation (e.g., total watch-time per day).
Build a cumulative metric: today’s metric = today + yesterday, but IDs can exist in only one side; use a FULL JOIN to align.
Daily total watch-time
From the Netflix-style watch_events table, compute the total watch-time per calendar date. Return one row per date with columns watch_date and total_watch_seconds, ordered by watch_date ascending.
Table watch_events
| column | type | constraints |
|---|---|---|
| event_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| content_id | INTEGER | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| watch_seconds | INTEGER | NOT NULL |
Sample data:
| content_id | event_id | started_at | user_id | watch_seconds |
|---|---|---|---|---|
| 101 | 1 | 2025-01-01 08:15:00 | 1 | 1800 |
| 102 | 2 | 2025-01-01 09:00:00 | 1 | 1200 |
| 101 | 3 | 2025-01-01 20:20:00 | 2 | 2400 |
| 103 | 4 | 2025-01-01 12:00:00 | 3 | 3600 |
| 101 | 5 | 2025-01-02 07:30:00 | 1 | 1500 |
| 104 | 6 | 2025-01-02 19:45:00 | 2 | 600 |
| 105 | 7 | 2025-01-02 21:10:00 | 2 | 900 |
| 106 | 8 | 2025-01-02 10:05:00 | 4 | 2700 |
| 107 | 9 | 2025-01-02 14:30:00 | 5 | 1200 |
Expected output
Total watch-time per date across all users and content.
| total_watch_seconds | watch_date |
|---|---|
| 9000 | 2025-01-01 |
| 6900 | 2025-01-02 |
Hints
- Convert started_at to a date to form the per-day buckets.
- SUM watch_seconds and GROUP BY the date column.
Two-day per-user cumulative watch-time with FULL JOIN
For a given 'today' date, compute each user's two-day watch-time equal to today's watch-time plus yesterday's watch-time. Users may appear on only one of the two days, so use a FULL OUTER JOIN to align per-user totals for the two dates. Using today = 2025-01-02, return user_id and two_day_watch_seconds.
Table watch_events
| column | type | constraints |
|---|---|---|
| event_id | INTEGER | PRIMARY KEY, NOT NULL |
| user_id | INTEGER | NOT NULL |
| content_id | INTEGER | NOT NULL |
| started_at | TIMESTAMP | NOT NULL |
| watch_seconds | INTEGER | NOT NULL |
Sample data:
| content_id | event_id | started_at | user_id | watch_seconds |
|---|---|---|---|---|
| 101 | 1 | 2025-01-01 08:15:00 | 1 | 1800 |
| 102 | 2 | 2025-01-01 09:00:00 | 1 | 1200 |
| 101 | 3 | 2025-01-01 20:20:00 | 2 | 2400 |
| 103 | 4 | 2025-01-01 12:00:00 | 3 | 3600 |
| 101 | 5 | 2025-01-02 07:30:00 | 1 | 1500 |
| 104 | 6 | 2025-01-02 19:45:00 | 2 | 600 |
| 105 | 7 | 2025-01-02 21:10:00 | 2 | 900 |
| 106 | 8 | 2025-01-02 10:05:00 | 4 | 2700 |
| 107 | 9 | 2025-01-02 14:30:00 | 5 | 1200 |
Expected output
Two-day cumulative watch-time per user for today = 2025-01-02, summing 2025-01-02 and 2025-01-01 and including users present on only one day via FULL OUTER JOIN.
| two_day_watch_seconds | user_id |
|---|---|
| 4500 | 1 |
| 3900 | 2 |
| 3600 | 3 |
| 2700 | 4 |
| 1200 | 5 |
Hints
- First aggregate to per-user, per-day totals from watch_events.
- Parameterize the target date (today) and define yesterday as today - INTERVAL '1 day'.
- Use a FULL OUTER JOIN between today and yesterday user-level totals and COALESCE nulls to zero.