Calculate Weekly, Monthly Hours Watched by Premium Users

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at Amazon during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Quick Overview