Calculate Weekly, Monthly Hours Watched by Premium Users

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.

Weekly and Monthly Watched Hours by Premium Users

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.

Table watch_events

column	type	constraints
user_id	INTEGER
video_id	VARCHAR
watched_minutes	INTEGER
watched_at	TIMESTAMP
subscription	VARCHAR

Sample data:

subscription	user_id	video_id	watched_at	watched_minutes
premium	101	v89	2023-04-03 10:22:05	30
free	102	v12	2023-04-04 08:10:00	12
premium	101	v77	2023-04-09 14:18:20	55
premium	103	v51	2023-04-11 20:00:00	20
free	102	v23	2023-05-01 09:00:12	42

Expected output

Weekly and monthly watched hours for premium users, with formatted period starts.

period_start	period_type	user_id	watched_hours
2023-04-01 00:00:00	month	101	1.42
2023-04-03 00:00:00	week	101	1.42
2023-04-01 00:00:00	month	103	0.33
2023-04-10 00:00:00	week	103	0.33

Hints

1. Filter to premium subscriptions before aggregating.
2. Use DATE_TRUNC for week and month buckets.
3. UNION ALL the weekly and monthly aggregates.