Netflix·Data Scientist·Technical Phone Screen
Jul 2026
Netflix data scientist interview that was basically one giant SQL problem split into three parts, all around ad frequency capping. The schema wasn't complicated but the logic got hairy fast, especially the rolling window stuff and the ROI calculation for impressions beyond cap.
- Given a schema with users, campaigns, impressions, clicks, and conversions, write SQL to compute per-campaign frequency cap metrics at a specific timestamp: how many users are exactly at the 7-day rolling cap, how many are one below it, and what percentage of active users would exceed the cap if each received one more impression right now.
- For every user-campaign pair seen in the last 7 days, compute the next eligible timestamp when serving another impression would not breach the frequency cap, because the oldest in-window impression has rolled out of the window.
- For a specific 7-day window, compute per-campaign ROI for impressions that were served beyond the frequency cap: total revenue attributed to those over-cap impressions divided by the count of over-cap impressions. Handle users with no clicks or conversions, multiple conversions per click, and impressions straddling the campaign end date.
“This part looked manageable until I realized 'active in last 7 days' and 'at cap' are two different populations and I was conflating them.”