Calculate Weekly, Monthly Watch Hours for Paid Users

video_view_logs

+---------+----------+----------------+------------+------------+
| user_id | video_id | watched_seconds| watch_date | device_type|
+---------+----------+----------------+------------+------------+
| 101     |   55     | 360            | 2023-08-14 | mobile     |
| 102     |   60     | 240            | 2023-08-15 | desktop    |
| 101     |   58     | 1800           | 2023-08-20 | tablet     |
| 103     |   55     | 600            | 2023-08-21 | mobile     |
| 104     |   72     | 900            | 2023-08-22 | smart_tv   |

Scenario

You have a raw event table storing every video view. Product managers need a report of total hours watched per week and per month, segmented by device_type and filtered to paid users only.

Question

Write an efficient SQL query (or equivalent pandas code) that returns weekly and monthly aggregated watch hours meeting the above conditions. Explain any windowing or date-trunc techniques you use.

Hints

Convert seconds to hours, DATE_TRUNC or to_period for grouping, filter before aggregation to minimize scan size.

Total watch hours per week and month by device type for paid users

You are given a raw event table video_view_logs that records every individual video view (one row per view).

Product managers need a watch-time report that rolls up viewing activity into both weekly and monthly buckets, split by device_type, and restricted to paid users only. For this exercise, treat user_id 101 and 103 as the paid users (in production you would join against a paid_users table; here you can hard-code these two ids, e.g. via a VALUES CTE).

Write a single PostgreSQL query that returns the total watch hours per bucket. Requirements:

• Produce one row per (period_type, period_start, device_type).
• period_type is the literal text 'week' or 'month'. Each qualifying view contributes to both a weekly and a monthly bucket, so a single view appears in two output rows (one week, one month).
• period_start is the truncated start date of the bucket:
  • for 'week', the Monday of the ISO week, computed with DATE_TRUNC('week', watch_date);
  • for 'month', the first day of the month, computed with DATE_TRUNC('month', watch_date). Cast the result to date.
• total_watch_hours is SUM(watched_seconds) for that bucket converted to hours (divide by 3600) and rounded to 2 decimal places.

Return the columns in this order: period_type, period_start, device_type, total_watch_hours.

Sort the result with all 'week' rows first, then all 'month' rows; within each group order by period_start ascending, then device_type ascending.

Table video_view_logs

column	type	constraints
user_id	INTEGER	NOT NULL
video_id	INTEGER	NOT NULL
watched_seconds	INTEGER	NOT NULL
watch_date	DATE	NOT NULL
device_type	VARCHAR(50)	NOT NULL

Sample data:

device_type	user_id	video_id	watch_date	watched_seconds
mobile	101	55	2023-08-14	360
desktop	102	60	2023-08-15	240
tablet	101	58	2023-08-20	1800
mobile	103	55	2023-08-21	600
smart_tv	104	72	2023-08-22	900

Expected output

One row per (period_type, period_start, device_type) for paid users (101, 103) only; weeks first then months, ordered by period_start then device_type; total_watch_hours = SUM(watched_seconds)/3600 rounded to 2 dp.

device_type	period_start	period_type	total_watch_hours
mobile	2023-08-14	week	0.10
tablet	2023-08-14	week	0.50
mobile	2023-08-21	week	0.17
mobile	2023-08-01	month	0.27
tablet	2023-08-01	month	0.50

Hints

1. Filter to paid users first (a small VALUES CTE works), then aggregate, so you scan fewer rows.
2. Compute two GROUP BY aggregations — one bucketed by DATE_TRUNC('week', ...) and one by DATE_TRUNC('month', ...) — and stack them with UNION ALL.
3. Postgres rejects an expression (like CASE) in an ORDER BY placed right after UNION ALL; wrap the union in a CTE/subquery and order in the outer SELECT.