Calculate Weekly, Monthly Watch Hours for Paid Users
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates a data scientist's ability to perform time-based aggregations, unit conversion, filtering by user segment, and dimensional segmentation (e.g., device_type) on raw event tables.
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.
Tables
video_view_logs(user_id INTEGER, video_id INTEGER, watched_seconds INTEGER, watch_date DATE, device_type VARCHAR(50))
Hints
- Filter to paid users first (a small VALUES CTE works), then aggregate, so you scan fewer rows.
- Compute two GROUP BY aggregations — one bucketed by DATE_TRUNC('week', ...) and one by DATE_TRUNC('month', ...) — and stack them with UNION ALL.