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.
Quick Answer: 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.