Katkat
Back

Calculate Weekly, Monthly Hours Watched by Premium Users

AmazonData Scientist · Technical Screen
MediumData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

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.

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.