Coding questions · Disney

Write complex SQL for streaming funnels

This question evaluates a candidate's ability to write complex ANSI-SQL for event-level funnel analysis, including temporal windowing, user attribution, multi-stage aggregation, and handling of event schemas.

You are analyzing a Disney/Hulu product funnel with events captured in a single table. Write ANSI-SQL (Snowflake/BigQuery compatible) to answer the sub-questions. Use 'today' = 2025-09-01.

Schema

users(user_id STRING, created_at TIMESTAMP, country STRING, platform STRING)
events(event_ts TIMESTAMP, user_id STRING, event_type STRING in ['impression','click','signup','start_subscription','watch_start','watch_end'], show_id STRING NULL, session_id STRING, revenue_cents INT NULL, campaign_id STRING NULL)
shows(show_id STRING, title STRING)

Small sample tables

users
+---------+---------------------+---------+----------+
| user_id | created_at | country | platform |
+---------+---------------------+---------+----------+
| u1 | 2025-08-10 12:00:00 | US | iOS |
| u2 | 2025-08-25 09:00:00 | US | Web |
| u3 | 2025-08-30 08:30:00 | CA | Android |
| u4 | 2025-08-31 21:00:00 | US | iOS |
+---------+---------------------+---------+----------+

events
+---------------------+--------+------------------+--------+-----------+---------------+-------------+
| event_ts | user_id| event_type | show_id| session_id| revenue_cents | campaign_id |
+---------------------+--------+------------------+--------+-----------+---------------+-------------+
| 2025-08-26 10:01:00 | u1 | impression | s1 | sA | NULL | c10 |
| 2025-08-26 10:02:00 | u1 | click | s1 | sA | NULL | c10 |
| 2025-08-26 10:05:00 | u1 | signup | NULL | sA | NULL | NULL |
| 2025-08-27 11:00:00 | u1 | start_subscription| NULL | sA | 7999 | NULL |
| 2025-08-30 09:00:00 | u2 | impression | s2 | sB | NULL | c10 |
| 2025-08-30 09:01:00 | u2 | click | s2 | sB | NULL | c10 |
| 2025-08-31 12:00:00 | u2 | signup | NULL | sB | NULL | NULL |
| 2025-09-01 08:00:00 | u3 | impression | s1 | sC | NULL | c99 |
| 2025-09-01 08:00:02 | u3 | impression | s1 | sC | NULL | c99 |
| 2025-09-01 08:03:00 | u3 | click | s1 | sC | NULL | c99 |
| 2025-09-01 08:20:00 | u3 | watch_start | s1 | sC | NULL | NULL |
| 2025-09-01 08:50:00 | u3 | watch_end | s1 | sC | NULL | NULL |
| 2025-08-31 20:00:00 | u4 | impression | s3 | sD | NULL | NULL |
| 2025-09-01 07:30:00 | u4 | click | s3 | sD | NULL | NULL |
| 2025-09-01 07:40:00 | u4 | signup | NULL | sD | NULL | NULL |
+---------------------+--------+------------------+--------+-----------+---------------+-------------+

shows
+--------+------------------+
| show_id| title |
+--------+------------------+
| s1 | The Bear |
| s2 | Only Murders |
| s3 | The Kardashians |
+--------+------------------+

Tasks a) Build a daily impression→click→signup→start_subscription funnel for each day d in [2025-08-26, 2025-09-01]. A user counts in a stage if: click occurs within 1 day of their impression on day d; signup within 3 days of that click; start_subscription within 7 days of signup. Attribute the funnel to the impression day. Output: day, impressions, clicks_w1d, signups_w3d, subs_w7d, and stage-to-stage rates. b) For users who signed up in August 2025, compute per signup_date and acquisition_channel (campaign_id NULL=organic, else paid) the median minutes from signup to first watch_start. c) Flag suspicious campaigns in the last 7 days relative to today (window [2025-08-26, 2025-09-01]) where CTR > 0.80, impressions ≥ 100, and distinct users ≤ 5. Return campaign_id, impressions, clicks, ctr, distinct_users. d) Deduplicate events: for identical (user_id, event_type, show_id) within a 5-second window, keep the earliest event_ts and drop the rest. Produce a de-duplicated events CTE. e) Bonus: For each day in August–September 2025, compute a 28-day rolling unique viewers per show_id using watch_start. On 2025-09-01, return the top 3 shows by that 28-day rolling unique viewers, breaking ties by most recent daily unique viewers. Provide final SQL for each sub-part, and explain index/partition choices and how your solution scales on billions of rows.

Daily Multi-Stage Subscription Funnel (Impression → Click → Signup → Subscription)

You are analyzing a Disney/Hulu product funnel with events captured in a single table. Build a daily impression → click → signup → start_subscription funnel for each day d in the range from 2025-08-26 to 2025-09-01 (inclusive).

A user counts in a stage for a given impression day d if:

They have at least one impression on day d.
They have a click within 1 calendar day of that impression day (click_date between d and d + 1 day).
They have a signup within 3 calendar days of that click.
They have a start_subscription within 7 calendar days of that signup.

Each user should be counted at most once per impression day, and the funnel is attributed to the impression day.

Output columns:

day (DATE)
impressions (distinct users with impression on that day)
clicks_w1d (distinct users whose clicks occur within 1 day of their impression day)
signups_w3d (distinct users whose signups occur within 3 days of their click)
subs_w7d (distinct users whose start_subscription occurs within 7 days of their signup)
click_rate (clicks_w1d / impressions)
signup_rate (signups_w3d / clicks_w1d)
sub_rate (subs_w7d / signups_w3d)

Write ANSI-style SQL (Snowflake/BigQuery compatible) to produce this funnel table for 2025-08-26 to 2025-09-01. Additionally, briefly describe what partitioning/clustering strategy on the events table would help this query scale to billions of rows.

Table `users`

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
created_at	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL
platform	VARCHAR(20)	NOT NULL

Sample data:

country	created_at	platform	user_id
US	2025-08-10 12:00:00	iOS	u1
US	2025-08-25 09:00:00	Web	u2
CA	2025-08-30 08:30:00	Android	u3
US	2025-08-31 21:00:00	iOS	u4

Table `events`

column	type	constraints
event_ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(10)	NOT NULL
event_type	VARCHAR(32)	NOT NULL
show_id	VARCHAR(10)
session_id	VARCHAR(20)	NOT NULL
revenue_cents	INT
campaign_id	VARCHAR(20)

Sample data:

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:00:02	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Table `shows`

column	type	constraints
show_id	VARCHAR(10)	PRIMARY KEY
title	VARCHAR(100)	NOT NULL

Sample data:

show_id	title
s1	The Bear
s2	Only Murders
s3	The Kardashians

Expected output

For each date between 2025-08-26 and 2025-09-01, counts of distinct users at each stage of the funnel, attributed to their impression day, plus stage-to-stage conversion rates.

click_rate	clicks_w1d	day	impressions	signup_rate	signups_w3d	sub_rate	subs_w7d
1	1	2025-08-26	1	1	1	1	1
NULL	0	2025-08-27	0	NULL	0	NULL	0
NULL	0	2025-08-28	0	NULL	0	NULL	0
NULL	0	2025-08-29	0	NULL	0	NULL	0
1	1	2025-08-30	1	1	1	0	0
1	1	2025-08-31	1	1	1	0	0
1	1	2025-09-01	1	0	0	NULL	0

Hints

Start by building a per-user, per-impression-day table that links each impression to the earliest downstream click, signup, and subscription within the allowed windows.
Aggregate distinct users per impression day and compute conversion rates with CASE expressions to avoid division by zero.

Median Time from Signup to First Watch by Acquisition Channel

For users who signed up in August 2025, compute the median time (in minutes) from signup to their first watch_start event.

Define acquisition_channel as:

'organic' if the campaign_id associated with the signup session is NULL,
'paid' otherwise.

For each signup event in August 2025:

Determine the campaign_id by looking back within the same session for the most recent event with a non-NULL campaign_id at or before the signup time (e.g., impression or click in that session).
Find the earliest watch_start event for that user occurring at or after the signup timestamp.
Compute the difference in minutes between signup_ts and that first watch_start.

Then, for each combination of signup_date (DATE of signup_ts) and acquisition_channel, compute the median minutes_to_first_watch.

Output: signup_date, acquisition_channel, median_minutes_to_first_watch.

Write a PostgreSQL query and briefly describe how you would partition/cluster events to make this query efficient on very large datasets.

Table `users`

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
created_at	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL
platform	VARCHAR(20)	NOT NULL

Sample data:

country	created_at	platform	user_id
US	2025-08-10 12:00:00	iOS	u1
US	2025-08-25 09:00:00	Web	u2
CA	2025-08-30 08:30:00	Android	u3
US	2025-08-31 21:00:00	iOS	u4

Table `events`

column	type	constraints
event_ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(10)	NOT NULL
event_type	VARCHAR(32)	NOT NULL
show_id	VARCHAR(10)
session_id	VARCHAR(20)	NOT NULL
revenue_cents	INT
campaign_id	VARCHAR(20)

Sample data:

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:00:02	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Table `shows`

column	type	constraints
show_id	VARCHAR(10)	PRIMARY KEY
title	VARCHAR(100)	NOT NULL

Sample data:

show_id	title
s1	The Bear
s2	Only Murders
s3	The Kardashians

Expected output

For each signup date in August 2025 and acquisition_channel (paid vs organic), the median minutes from signup to first watch_start. In this sample data, both August signups are attributed to paid and have subsequent watch_start events.

acquisition_channel	median_minutes_to_first_watch	signup_date
paid	115	2025-08-26
paid	60	2025-08-31

Hints

First, isolate August 2025 signup events and tie each signup to an acquisition campaign by looking at prior events in the same session.
Use a windowed percentile function such as PERCENTILE_CONT to compute the median over the per-signup minutes_to_first_watch values.

Suspicious High-CTR Campaigns in a Fixed 7-Day Window

Flag suspicious ad campaigns in the 7-day window from 2025-05-26 to 2025-06-01 (inclusive).

For each non-NULL campaign_id in that date range, compute:

impressions: count of events with event_type = 'impression',
clicks: count of events with event_type = 'click',
ctr: clicks / impressions,
distinct_users: COUNT(DISTINCT user_id) across all events for that campaign_id in the window.

Return only campaigns where:

ctr > 0.80,
impressions >= 100,
distinct_users <= 5.

Output: campaign_id, impressions, clicks, ctr, distinct_users.

Use explicit dates in your WHERE clause (no NOW()/CURRENT_DATE). Also describe how you would leverage partitioning on event_ts and clustering on campaign_id to make this query efficient on very large tables.

Table `users`

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
created_at	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL
platform	VARCHAR(20)	NOT NULL

Sample data:

country	created_at	platform	user_id
US	2025-08-10 12:00:00	iOS	u1
US	2025-08-25 09:00:00	Web	u2
CA	2025-08-30 08:30:00	Android	u3
US	2025-08-31 21:00:00	iOS	u4

Table `events`

column	type	constraints
event_ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(10)	NOT NULL
event_type	VARCHAR(32)	NOT NULL
show_id	VARCHAR(10)
session_id	VARCHAR(20)	NOT NULL
revenue_cents	INT
campaign_id	VARCHAR(20)

Sample data:

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:00:02	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Table `shows`

column	type	constraints
show_id	VARCHAR(10)	PRIMARY KEY
title	VARCHAR(100)	NOT NULL

Sample data:

show_id	title
s1	The Bear
s2	Only Murders
s3	The Kardashians

Expected output

In the provided sample data there are no events between 2025-05-26 and 2025-06-01, so no campaigns meet the criteria. The result set is empty.

Hints

Aggregate by campaign_id over the fixed date range and compute impressions, clicks, and distinct users in one pass.
Filter the aggregated campaigns using a HAVING clause or an outer WHERE that applies the CTR, impressions, and distinct user thresholds.

Deduplicating Near-Duplicate Events in a 5-Second Window

Events are sometimes duplicated due to client retries. For identical (user_id, event_type, show_id) combinations occurring within a 5-second window, you want to keep only the earliest event_ts and drop the rest.

Produce a CTE called dedup_events that contains the de-duplicated events. An event should be dropped if it lies within 5 seconds of a previous event with the same (user_id, event_type, show_id); otherwise, it starts a new 5-second window and is kept.

Write SQL that defines dedup_events and then selects all columns from it. Explain how your approach behaves for sequences of 3+ events within and outside the 5-second window, and comment on how using an ordered window function over (user_id, event_type, show_id) scales.

Table `users`

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
created_at	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL
platform	VARCHAR(20)	NOT NULL

Sample data:

country	created_at	platform	user_id
US	2025-08-10 12:00:00	iOS	u1
US	2025-08-25 09:00:00	Web	u2
CA	2025-08-30 08:30:00	Android	u3
US	2025-08-31 21:00:00	iOS	u4

Table `events`

column	type	constraints
event_ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(10)	NOT NULL
event_type	VARCHAR(32)	NOT NULL
show_id	VARCHAR(10)
session_id	VARCHAR(20)	NOT NULL
revenue_cents	INT
campaign_id	VARCHAR(20)

Sample data:

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:00:02	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Table `shows`

column	type	constraints
show_id	VARCHAR(10)	PRIMARY KEY
title	VARCHAR(100)	NOT NULL

Sample data:

show_id	title
s1	The Bear
s2	Only Murders
s3	The Kardashians

Expected output

The de-duplicated events table removes the second impression for user u3 on show s1 that occurred 2 seconds after the first; all other events are retained.

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Hints

Order events by event_ts within each (user_id, event_type, show_id) and use LAG to compare each event to its predecessor.
Build groups whenever the gap from the previous event exceeds 5 seconds, then keep only the first event in each group with ROW_NUMBER.

Top Shows by 28-Day Rolling Unique Viewers on a Given Day

Using watch_start events as views, compute, for the date 2025-09-01:

rolling_28d_unique_viewers: the number of distinct users who generated a watch_start for a given show_id between 2025-09-01 - 27 days and 2025-09-01 (inclusive), and
daily_unique_viewers: the number of distinct users who generated a watch_start for that show_id on 2025-09-01 itself.

Return the top 3 shows by rolling_28d_unique_viewers, breaking ties by daily_unique_viewers (both in descending order). If a show has no views in the 28-day window, treat its counts as 0 but still include it in the ranking.

Output: show_id, title, rolling_28d_unique_viewers, daily_unique_viewers.

Write SQL to produce this result and briefly describe how partitioning on event_ts and clustering on show_id would help at scale.

Table `users`

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
created_at	TIMESTAMP	NOT NULL
country	VARCHAR(2)	NOT NULL
platform	VARCHAR(20)	NOT NULL

Sample data:

country	created_at	platform	user_id
US	2025-08-10 12:00:00	iOS	u1
US	2025-08-25 09:00:00	Web	u2
CA	2025-08-30 08:30:00	Android	u3
US	2025-08-31 21:00:00	iOS	u4

Table `events`

column	type	constraints
event_ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(10)	NOT NULL
event_type	VARCHAR(32)	NOT NULL
show_id	VARCHAR(10)
session_id	VARCHAR(20)	NOT NULL
revenue_cents	INT
campaign_id	VARCHAR(20)

Sample data:

campaign_id	event_ts	event_type	revenue_cents	session_id	show_id	user_id
c10	2025-08-26 10:01:00	impression	NULL	sA	s1	u1
c10	2025-08-26 10:02:00	click	NULL	sA	s1	u1
NULL	2025-08-26 10:05:00	signup	NULL	sA	NULL	u1
NULL	2025-08-26 12:00:00	watch_start	NULL	sA	s1	u1
NULL	2025-08-26 12:30:00	watch_end	NULL	sA	s1	u1
NULL	2025-08-27 11:00:00	start_subscription	7999	sA	NULL	u1
c10	2025-08-30 09:00:00	impression	NULL	sB	s2	u2
c10	2025-08-30 09:01:00	click	NULL	sB	s2	u2
NULL	2025-08-31 12:00:00	signup	NULL	sB	NULL	u2
NULL	2025-08-31 13:00:00	watch_start	NULL	sB	s2	u2
NULL	2025-08-31 13:45:00	watch_end	NULL	sB	s2	u2
NULL	2025-08-31 20:00:00	impression	NULL	sD	s3	u4
NULL	2025-09-01 07:30:00	click	NULL	sD	s3	u4
NULL	2025-09-01 07:40:00	signup	NULL	sD	NULL	u4
c99	2025-09-01 08:00:00	impression	NULL	sC	s1	u3
c99	2025-09-01 08:00:02	impression	NULL	sC	s1	u3
c99	2025-09-01 08:03:00	click	NULL	sC	s1	u3
NULL	2025-09-01 08:20:00	watch_start	NULL	sC	s1	u3
NULL	2025-09-01 08:50:00	watch_end	NULL	sC	s1	u3

Table `shows`

column	type	constraints
show_id	VARCHAR(10)	PRIMARY KEY
title	VARCHAR(100)	NOT NULL

Sample data:

show_id	title
s1	The Bear
s2	Only Murders
s3	The Kardashians

Expected output

On 2025-09-01, the 28-day window covers 2025-08-05 through 2025-09-01. Show s1 has watch_start events from u1 (2025-08-26) and u3 (2025-09-01) for 2 unique users; show s2 has u2 (2025-08-31) for 1 user; show s3 has no watch_start events. Daily unique viewers on 2025-09-01 are 1 for s1 and 0 for s2 and s3.

daily_unique_viewers	rolling_28d_unique_viewers	show_id	title
1	2	s1	The Bear
0	1	s2	Only Murders
0	0	s3	The Kardashians

Hints

First compute distinct viewers per show over the 28-day window and for the specific day 2025-09-01, then join those aggregates back to the shows table.
Use COALESCE to treat shows without any watch_start events as having 0 viewers rather than NULL, so they still appear in the ranking.

Daily Multi-Stage Subscription Funnel (Impression → Click → Signup → Subscription)

Table users

Table events

Table shows

Expected output

Hints

Median Time from Signup to First Watch by Acquisition Channel

Table users

Table events

Table shows

Expected output

Hints

Suspicious High-CTR Campaigns in a Fixed 7-Day Window

Table users

Table events

Table shows

Expected output

Hints

Deduplicating Near-Duplicate Events in a 5-Second Window

Table users

Table events

Table shows

Expected output

Hints

Top Shows by 28-Day Rolling Unique Viewers on a Given Day

Table users

Table events

Table shows

Expected output

Hints

Table `users`

Table `events`

Table `shows`

Table `users`

Table `events`

Table `shows`

Table `users`

Table `events`

Table `shows`

Table `users`

Table `events`

Table `shows`

Table `users`

Table `events`

Table `shows`