Calculate Weekly, Monthly Hours Watched by Premium Users
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
watch_events
+-----------+----------+----------------+-----------------------+----------------+
| user_id | video_id | watched_minutes| watched_at | subscription |
+-----------+----------+----------------+-----------------------+----------------+
| 101 | v89 | 30 | 2023-04-03 10:22:05 | premium |
| 102 | v12 | 12 | 2023-04-04 08:10:00 | free |
| 101 | v77 | 55 | 2023-04-09 14:18:20 | premium |
| 103 | v51 | 20 | 2023-04-11 20:00:00 | premium |
| 102 | v23 | 42 | 2023-05-01 09:00:12 | free |
+-----------+----------+----------------+-----------------------+----------------+
##### Scenario
Streaming platform wants weekly and monthly total video hours watched by premium users to monitor engagement trends.
##### Question
Given the watch_events table, write SQL that returns for each user_id the total watched_minutes converted to hours aggregated by week and by month, filtered where subscription = 'premium'.
##### Hints
Use DATE_TRUNC or EXTRACT for week/month, SUM(watched_minutes)/60 AS watched_hours, and GROUP BY.
Overview: This question evaluates a candidate's ability to perform time-based aggregation and data manipulation in SQL and Python, including grouping, filtering by subscription status, and converting minute-level metrics into hours.
For premium streaming users, aggregate watched minutes into weekly and monthly watched hours.
Use `watch_events` and include only rows where `subscription = 'premium'`. Return one row per `(user_id, period_type, period_start)` with these columns:
- `user_id`
- `period_type`: either `week` or `month`
- `period_start`: the `DATE_TRUNC` start of that week or month, formatted as `YYYY-MM-DD HH24:MI:SS`
- `watched_hours`: total watched minutes divided by 60, rounded to 2 decimals
Order by `user_id`, then `period_start`, then `period_type`.
Tables
watch_events(user_id INTEGER, video_id VARCHAR, watched_minutes INTEGER, watched_at TIMESTAMP, subscription VARCHAR)
Hints
- Filter to premium subscriptions before aggregating.
- Use DATE_TRUNC for week and month buckets.