Disney·Data Scientist·Take-home Assignment
- Build a daily impression-to-click-to-signup-to-subscription funnel for each day in a date range, with time-bounded attribution windows at each stage (click within 1 day of impression, signup within 3 days of click, subscription within 7 days of signup). Output daily counts and stage-to-stage conversion rates.
- For users who signed up in August 2025, compute the median time in minutes from signup to first watch_start, broken out by signup date and acquisition channel (organic vs. paid based on whether campaign_id is null).
- Identify suspicious ad campaigns in the last 7 days where the click-through rate exceeds 80%, there are at least 100 impressions, and the number of distinct users is 5 or fewer. Return campaign details including CTR and distinct user count.
- Deduplicate events by collapsing identical (user_id, event_type, show_id) combinations that occur within a 5-second window, keeping only the earliest timestamp. Produce a clean deduplicated events CTE.
- Compute a 28-day rolling count of unique viewers per show using watch_start events, for each day across August and September 2025. Return the top 3 shows by this rolling metric on a specific target date, breaking ties by most recent daily unique viewers.
“This one took me longer than it should have.”