Write SQL for streaming cohort metrics
Company: HBO
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Online Assessment
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.
Overview: This question evaluates proficiency in SQL-based data manipulation and analytics, focusing on cohort analysis, temporal filtering, aggregation, deduplication and experiment metric computation within the Data Manipulation (SQL/Python) domain and the ability to translate business metric definitions into queryable datasets.
Read the full HBO Data Scientist interview experience this question came from
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.
Tables
users(user_id INT, signup_date DATE)
content(content_id INT, title VARCHAR(100), director_id INT)
directors(director_id INT, name VARCHAR(100))
views(user_id INT, content_id INT, start_time TIMESTAMP, end_time TIMESTAMP, seconds_watched INT)
experiments(user_id INT, exp_id VARCHAR(20), variant CHAR(1), exposure_time TIMESTAMP)
cancellations(user_id INT, cancel_time TIMESTAMP)
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.
Tables
users(user_id INT, signup_date DATE)
content(content_id INT, title VARCHAR(100), director_id INT)
directors(director_id INT, name VARCHAR(100))
views(user_id INT, content_id INT, start_time TIMESTAMP, end_time TIMESTAMP, seconds_watched INT)
experiments(user_id INT, exp_id VARCHAR(20), variant CHAR(1), exposure_time TIMESTAMP)
cancellations(user_id INT, cancel_time TIMESTAMP)
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:
1) 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.
Tables
users(user_id INT, signup_date DATE)
content(content_id INT, title VARCHAR(100), director_id INT)
directors(director_id INT, name VARCHAR(100))
views(user_id INT, content_id INT, start_time TIMESTAMP, end_time TIMESTAMP, seconds_watched INT)
experiments(user_id INT, exp_id VARCHAR(20), variant CHAR(1), exposure_time TIMESTAMP)
cancellations(user_id INT, cancel_time TIMESTAMP)
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.
Community answers
Answer by usta
This is a more correct approach that covers the case when the user has multiple cancellations or assigned to multiple variants (i.e., a switchback experiment):
WITH earliest_eligible_exposures AS (
SELECT e.user_id, variant, MIN(exposure_time) as eligible_exposure_time
FROM experiments e LEFT JOIN cancellations c ON e.user_id = c.user_id
WHERE exp_id = 'HP123'
AND (cancel_time IS NULL OR exposure_time < cancel_time)
GROUP BY e.user_id, variant
)
SELECT
variant,
count(distinct eee.user_id) as exposed_users,
count(distinct case when cancel_time > eligible_exposure_time and cancel_time - eligible_exposure_time <= INTERVAL '7 days' then eee.user_id else NULL end) as cancelled_within_7d_users,
round(count(distinct case when cancel_time > eligible_exposure_time and cancel_time - eligible_exposure_time <= INTERVAL '7 days' then eee.user_id else NULL end) * 100.0 / nullif(count(distinct eee.user_id),0.0), 4) as cancellation_rate_7d
from earliest_eligible_exposures eee left join cancellations c on eee.user_id = c.user_id
group by variant
order by variant