Given the schema and sample data below, write SQL to answer the three tasks. Use standard SQL (window functions allowed). Assume timestamps are UTC.
Tables:
users(user_id INT, signup_date DATE)
content(content_id INT, title TEXT, director_id INT)
directors(director_id INT, name TEXT)
views(user_id INT, content_id INT, start_time TIMESTAMP, end_time TIMESTAMP, seconds_watched INT)
experiments(user_id INT, exp_id TEXT, variant CHAR(1), exposure_time TIMESTAMP)
cancellations(user_id INT, cancel_time TIMESTAMP)
Sample rows (minimal):
users
+---------+-------------+
| user_id | signup_date |
+---------+-------------+
| 1 | 2022-07-20 |
| 2 | 2022-07-25 |
| 3 | 2022-08-02 |
| 4 | 2022-08-10 |
+---------+-------------+
content
+------------+-------------+-------------+
| content_id | title | director_id |
+------------+-------------+-------------+
| 10 | Movie1 | 100 |
| 11 | Movie2 | 100 |
| 12 | DocuA | 101 |
+------------+-------------+-------------+
directors
+-------------+-----------+
| director_id | name |
+-------------+-----------+
| 100 | Dir Alpha |
| 101 | Dir Beta |
+-------------+-----------+
views
+---------+------------+---------------------+---------------------+----------------+
| user_id | content_id | start_time | end_time | seconds_watched|
+---------+------------+---------------------+---------------------+----------------+
| 1 | 10 | 2022-08-05 12:05:00 | 2022-08-05 13:35:00 | 5400 |
| 1 | 11 | 2022-08-06 09:00:00 | 2022-08-06 09:45:00 | 2700 |
| 2 | 12 | 2022-08-06 20:00:00 | 2022-08-06 20:20:00 | 1200 |
| 3 | 10 | 2022-08-10 08:00:00 | 2022-08-10 08:30:00 | 1800 |
| 4 | 11 | 2022-08-15 21:00:00 | 2022-08-15 21:05:00 | 300 |
+---------+------------+---------------------+---------------------+----------------+
experiments
+---------+--------+---------+---------------------+
| user_id | exp_id | variant | exposure_time |
+---------+--------+---------+---------------------+
| 1 | HP123 | A | 2022-08-05 11:00:00 |
| 2 | HP123 | B | 2022-08-06 19:50:00 |
| 3 | HP123 | A | 2022-08-10 07:55:00 |
| 4 | HP123 | B | 2022-08-15 20:30:00 |
+---------+--------+---------+---------------------+
cancellations
+---------+---------------------+
| user_id | cancel_time |
+---------+---------------------+
| 2 | 2022-08-20 10:00:00 |
+---------+---------------------+
Tasks:
A) For August 2022, by director, compute: unique viewers, total watch-hours, and median watch-time per viewer. Treat multiple views by the same user for the same director as one viewer; use the sum of seconds_watched per user per director to compute the per-viewer median.
B) For experiment HP123, compute 7-day post-exposure cancellation rates by variant among users exposed before any cancellation (i.e., ignore exposures after cancel_time). Use exposure_time as day 0 and include cancellations with 0 < t <= 7 days.
C) For August 2022, return the top 3 titles by unique viewers per variant (A/B) among users who were exposed to HP123 before their first August view. Break ties by higher total watch-hours, then lexicographically by title.
Describe any assumptions you need (e.g., one active subscription per user), and write efficient SQL for each task.
Director-level August 2022 streaming metrics with medians
Using the tables defined below, write a SQL query to produce, for August 2022 (from 2022-08-01 00:00:00 to 2022-09-01 00:00:00, exclusive of the end), the following metrics by director:
- unique_viewers: number of distinct users who watched any title from that director in August 2022.
- total_watch_hours: total watch time across all such users and titles for that director, expressed in hours.
- median_watch_seconds: the median of per-viewer total watch time (in seconds) for that director.
Treat multiple views by the same user for the same director as one viewer; to compute the per-viewer median, first sum seconds_watched per (user, director) within August, then take the median across those per-user sums for each director.
Return one row per director, including the director_name and the three metrics. Use standard SQL; window functions are allowed.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|
| 2022-07-20 | 1 |
| 2022-07-25 | 2 |
| 2022-08-02 | 3 |
| 2022-08-10 | 4 |
Table content
| column | type | constraints |
|---|
| content_id | INT | PRIMARY KEY |
| title | VARCHAR(100) | NOT NULL |
| director_id | INT | NOT NULL |
Sample data:
| content_id | director_id | title |
|---|
| 10 | 100 | Movie1 |
| 11 | 100 | Movie2 |
| 12 | 101 | DocuA |
Table directors
| column | type | constraints |
|---|
| director_id | INT | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
Sample data:
| director_id | name |
|---|
| 100 | Dir Alpha |
| 101 | Dir Beta |
Table views
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| content_id | INT | NOT NULL |
| start_time | TIMESTAMP | NOT NULL |
| end_time | TIMESTAMP | NOT NULL |
| seconds_watched | INT | NOT NULL |
Sample data:
| content_id | end_time | seconds_watched | start_time | user_id |
|---|
| 10 | 2022-08-05 13:35:00 | 5400 | 2022-08-05 12:05:00 | 1 |
| 11 | 2022-08-06 09:45:00 | 2700 | 2022-08-06 09:00:00 | 1 |
| 12 | 2022-08-06 20:20:00 | 1200 | 2022-08-06 20:00:00 | 2 |
| 10 | 2022-08-10 08:30:00 | 1800 | 2022-08-10 08:00:00 | 3 |
| 11 | 2022-08-15 21:05:00 | 300 | 2022-08-15 21:00:00 | 4 |
Table experiments
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| exp_id | VARCHAR(20) | NOT NULL |
| variant | CHAR(1) | NOT NULL |
| exposure_time | TIMESTAMP | NOT NULL |
Sample data:
| exp_id | exposure_time | user_id | variant |
|---|
| HP123 | 2022-08-05 11:00:00 | 1 | A |
| HP123 | 2022-08-06 19:50:00 | 2 | B |
| HP123 | 2022-08-10 07:55:00 | 3 | A |
| HP123 | 2022-08-15 20:30:00 | 4 | B |
Table cancellations
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| cancel_time | TIMESTAMP | NOT NULL |
Sample data:
| cancel_time | user_id |
|---|
| 2022-08-20 10:00:00 | 2 |
Expected output
One row per director with the director name, the number of unique viewers in August 2022, the total watch-hours across those viewers and titles, and the median per-viewer watch time in seconds for that director.
| director_name | median_watch_seconds | total_watch_hours | unique_viewers |
|---|
| Dir Alpha | 1800 | 2.8333 | 3 |
| Dir Beta | 1200 | 0.3333 | 1 |
Hints
- First aggregate seconds_watched per (user, director) within August before computing the median.
- Use PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY user_total_seconds) over the per-user aggregates to get the median.
7-day post-exposure cancellation rate by experiment variant
Using the same schema, for experiment HP123, compute 7-day post-exposure cancellation rates by variant (A/B).
Include only users whose exposure to HP123 happened before any cancellation they may have (i.e., ignore exposures where exposure_time is on or after cancel_time). Treat exposure_time as day 0 and count a user as cancelled if their cancel_time satisfies 0 < cancel_time - exposure_time <= 7 days.
If a user has multiple eligible exposures to HP123, base the metric on their earliest eligible exposure. Return one row per variant with: variant, exposed_users, cancelled_within_7d_users, and cancellation_rate_7d (cancelled_within_7d_users / exposed_users).
Use standard SQL; window functions are allowed.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|
| 2022-07-20 | 1 |
| 2022-07-25 | 2 |
| 2022-08-02 | 3 |
| 2022-08-10 | 4 |
Table content
| column | type | constraints |
|---|
| content_id | INT | PRIMARY KEY |
| title | VARCHAR(100) | NOT NULL |
| director_id | INT | NOT NULL |
Sample data:
| content_id | director_id | title |
|---|
| 10 | 100 | Movie1 |
| 11 | 100 | Movie2 |
| 12 | 101 | DocuA |
Table directors
| column | type | constraints |
|---|
| director_id | INT | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
Sample data:
| director_id | name |
|---|
| 100 | Dir Alpha |
| 101 | Dir Beta |
Table views
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| content_id | INT | NOT NULL |
| start_time | TIMESTAMP | NOT NULL |
| end_time | TIMESTAMP | NOT NULL |
| seconds_watched | INT | NOT NULL |
Sample data:
| content_id | end_time | seconds_watched | start_time | user_id |
|---|
| 10 | 2022-08-05 13:35:00 | 5400 | 2022-08-05 12:05:00 | 1 |
| 11 | 2022-08-06 09:45:00 | 2700 | 2022-08-06 09:00:00 | 1 |
| 12 | 2022-08-06 20:20:00 | 1200 | 2022-08-06 20:00:00 | 2 |
| 10 | 2022-08-10 08:30:00 | 1800 | 2022-08-10 08:00:00 | 3 |
| 11 | 2022-08-15 21:05:00 | 300 | 2022-08-15 21:00:00 | 4 |
Table experiments
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| exp_id | VARCHAR(20) | NOT NULL |
| variant | CHAR(1) | NOT NULL |
| exposure_time | TIMESTAMP | NOT NULL |
Sample data:
| exp_id | exposure_time | user_id | variant |
|---|
| HP123 | 2022-08-05 11:00:00 | 1 | A |
| HP123 | 2022-08-06 19:50:00 | 2 | B |
| HP123 | 2022-08-10 07:55:00 | 3 | A |
| HP123 | 2022-08-15 20:30:00 | 4 | B |
Table cancellations
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| cancel_time | TIMESTAMP | NOT NULL |
Sample data:
| cancel_time | user_id |
|---|
| 2022-08-20 10:00:00 | 2 |
Expected output
One row per variant for HP123, showing how many users were exposed (with exposure before any cancellation), how many cancelled within 7 days of exposure, and the corresponding 7-day cancellation rate.
| cancellation_rate_7d | cancelled_within_7d_users | exposed_users | variant |
|---|
| 0 | 0 | 2 | A |
| 0 | 0 | 2 | B |
Hints
- Join experiments to cancellations to bring cancel_time onto each exposure, then discard exposures that occur on or after cancel_time.
- Use ROW_NUMBER() to pick one exposure per user and a CASE expression comparing cancel_time to exposure_time and exposure_time + 7 days to flag 7-day cancellations.
Top titles by unique viewers per variant with exposure-before-view constraint
For August 2022 (from 2022-08-01 00:00:00 to 2022-09-01 00:00:00, exclusive of the end), return the top 3 titles by unique viewers per variant (A/B) for experiment HP123, under this constraint:
- Only consider users whose exposure to HP123 occurred before their first view in August 2022 (i.e., exposure_time < their earliest August view start_time).
For those eligible users, consider all of their views in August 2022. For each (variant, title) pair:
- unique_viewers: count distinct users with at least one qualifying August view of that title.
- total_watch_hours: sum of seconds_watched for those views, converted to hours.
Within each variant, rank titles by:
- unique_viewers (descending), 2) total_watch_hours (descending), 3) title (ascending, lexicographic).
Return only the top 3 titles per variant, with columns variant, title, unique_viewers, and total_watch_hours. Use standard SQL; window functions are allowed. State any assumptions you make (for example, at most one HP123 variant per user) in your explanation, but keep the query itself correct for the given sample data.
Table users
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| signup_date | DATE | NOT NULL |
Sample data:
| signup_date | user_id |
|---|
| 2022-07-20 | 1 |
| 2022-07-25 | 2 |
| 2022-08-02 | 3 |
| 2022-08-10 | 4 |
Table content
| column | type | constraints |
|---|
| content_id | INT | PRIMARY KEY |
| title | VARCHAR(100) | NOT NULL |
| director_id | INT | NOT NULL |
Sample data:
| content_id | director_id | title |
|---|
| 10 | 100 | Movie1 |
| 11 | 100 | Movie2 |
| 12 | 101 | DocuA |
Table directors
| column | type | constraints |
|---|
| director_id | INT | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
Sample data:
| director_id | name |
|---|
| 100 | Dir Alpha |
| 101 | Dir Beta |
Table views
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| content_id | INT | NOT NULL |
| start_time | TIMESTAMP | NOT NULL |
| end_time | TIMESTAMP | NOT NULL |
| seconds_watched | INT | NOT NULL |
Sample data:
| content_id | end_time | seconds_watched | start_time | user_id |
|---|
| 10 | 2022-08-05 13:35:00 | 5400 | 2022-08-05 12:05:00 | 1 |
| 11 | 2022-08-06 09:45:00 | 2700 | 2022-08-06 09:00:00 | 1 |
| 12 | 2022-08-06 20:20:00 | 1200 | 2022-08-06 20:00:00 | 2 |
| 10 | 2022-08-10 08:30:00 | 1800 | 2022-08-10 08:00:00 | 3 |
| 11 | 2022-08-15 21:05:00 | 300 | 2022-08-15 21:00:00 | 4 |
Table experiments
| column | type | constraints |
|---|
| user_id | INT | NOT NULL |
| exp_id | VARCHAR(20) | NOT NULL |
| variant | CHAR(1) | NOT NULL |
| exposure_time | TIMESTAMP | NOT NULL |
Sample data:
| exp_id | exposure_time | user_id | variant |
|---|
| HP123 | 2022-08-05 11:00:00 | 1 | A |
| HP123 | 2022-08-06 19:50:00 | 2 | B |
| HP123 | 2022-08-10 07:55:00 | 3 | A |
| HP123 | 2022-08-15 20:30:00 | 4 | B |
Table cancellations
| column | type | constraints |
|---|
| user_id | INT | PRIMARY KEY |
| cancel_time | TIMESTAMP | NOT NULL |
Sample data:
| cancel_time | user_id |
|---|
| 2022-08-20 10:00:00 | 2 |
Expected output
Top 3 titles per variant (A/B) for HP123, considering only users exposed before their first August view. Each row shows the variant, title, number of unique viewers in August, and total watch-hours for that (variant, title) combination.
| title | total_watch_hours | unique_viewers | variant |
|---|
| Movie1 | 2 | 2 | A |
| Movie2 | 0.75 | 1 | A |
| DocuA | 0.3333 | 1 | B |
| Movie2 | 0.0833 | 1 | B |
Hints
- First identify each user's earliest August view and then keep only HP123 exposures that occurred before that timestamp.
- After filtering to eligible users, aggregate August views by (variant, title), then use ROW_NUMBER() with a PARTITION BY variant to select the top 3 titles per variant based on the requested ordering.