Write complex SQL for streaming funnels
Company: Disney
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
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.
Overview: 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.
Read the full Disney Data Scientist interview experience this question came from
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.
Tables
users(user_id VARCHAR(10), created_at TIMESTAMP, country VARCHAR(2), platform VARCHAR(20))
events(event_ts TIMESTAMP, user_id VARCHAR(10), event_type VARCHAR(32), show_id VARCHAR(10), session_id VARCHAR(20), revenue_cents INT, campaign_id VARCHAR(20))
shows(show_id VARCHAR(10), title VARCHAR(100))
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:
1. 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).
2. Find the earliest watch_start event for that user occurring at or after the signup timestamp.
3. 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.
Tables
users(user_id VARCHAR(10), created_at TIMESTAMP, country VARCHAR(2), platform VARCHAR(20))
events(event_ts TIMESTAMP, user_id VARCHAR(10), event_type VARCHAR(32), show_id VARCHAR(10), session_id VARCHAR(20), revenue_cents INT, campaign_id VARCHAR(20))
shows(show_id VARCHAR(10), title VARCHAR(100))
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.
Tables
users(user_id VARCHAR(10), created_at TIMESTAMP, country VARCHAR(2), platform VARCHAR(20))
events(event_ts TIMESTAMP, user_id VARCHAR(10), event_type VARCHAR(32), show_id VARCHAR(10), session_id VARCHAR(20), revenue_cents INT, campaign_id VARCHAR(20))
shows(show_id VARCHAR(10), title VARCHAR(100))
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.
Tables
users(user_id VARCHAR(10), created_at TIMESTAMP, country VARCHAR(2), platform VARCHAR(20))
events(event_ts TIMESTAMP, user_id VARCHAR(10), event_type VARCHAR(32), show_id VARCHAR(10), session_id VARCHAR(20), revenue_cents INT, campaign_id VARCHAR(20))
shows(show_id VARCHAR(10), title VARCHAR(100))
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.
Tables
users(user_id VARCHAR(10), created_at TIMESTAMP, country VARCHAR(2), platform VARCHAR(20))
events(event_ts TIMESTAMP, user_id VARCHAR(10), event_type VARCHAR(32), show_id VARCHAR(10), session_id VARCHAR(20), revenue_cents INT, campaign_id VARCHAR(20))
shows(show_id VARCHAR(10), title VARCHAR(100))
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.