Summary
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.
Questions Asked(3)
This part looked manageable until I realized 'active in last 7 days' and 'at cap' are two different populations and I was conflating them.
Suggested Approach
Start by clarifying the schema relationships and defining the 7-day rolling window relative to the given timestamp, then build a CTE that counts per-user, per-campaign impressions within that window. Layer on top of that the frequency cap value (either hardcoded or joined from a campaigns table) to classify users into the three requested buckets and compute the exceedance percentage.
Clarify Schema & Definitions
Confirm the relevant columns (user_id, campaign_id, impression_timestamp, cap value) and pin down key definitions: what constitutes the 7-day window boundary relative to the given timestamp, and what 'active user' means for the denominator of the exceedance percentage.
Build the Rolling Impression Count CTE
Write a CTE that filters the impressions table to rows where impression_timestamp falls within (reference_timestamp - 7 days, reference_timestamp], then GROUP BY user_id and campaign_id to get each user's current impression count per campaign.
Join the Frequency Cap & Classify Users
Join the impression counts to the campaigns table (or inline the cap constant) to compute cap_delta = cap_value - impression_count, then use CASE WHEN logic to label each user as 'at_cap' (delta = 0), 'one_below' (delta = 1), or 'would_exceed' (delta <= 0, i.e., already at or over cap, so one more impression pushes them over).
Aggregate Per-Campaign Metrics
GROUP BY campaign_id and use conditional COUNT or SUM(CASE WHEN ...) to produce columns for users_at_cap, users_one_below, and users_who_would_exceed, then compute the exceedance percentage as ROUND(100.0 * users_who_would_exceed / NULLIF(total_active_users, 0), 2).
Validate & Discuss Edge Cases
Walk through edge cases such as users with zero impressions in the window (should they appear?), campaigns with no impressions at all, timezone handling for the timestamp boundary, and whether the cap is inclusive or exclusive — demonstrating awareness of real-world data quality issues.
Key Points to Mention
Probably the trickiest of the three.
Suggested Approach
Model the problem as a sliding window frequency cap where, for each user-campaign pair, you sort impressions by timestamp and identify when the oldest impression exits the 7-day window relative to the cap limit. The next eligible timestamp is simply the oldest impression's timestamp plus 7 days (plus one millisecond/second depending on granularity), computed only when the user has already hit the frequency cap threshold.
Clarify Assumptions & Constraints
Confirm the frequency cap value (e.g., max N impressions per 7-day rolling window), the timestamp granularity (seconds vs. milliseconds), and whether '7 days' means exactly 168 hours or a calendar boundary. Also confirm whether the output should only include capped users or all user-campaign pairs.
Filter & Rank Impressions Within the Window
For each user-campaign pair, filter impressions to those within the last 7 days, then use a window function (e.g., ROW_NUMBER or RANK partitioned by user_id and campaign_id, ordered by timestamp) to rank impressions chronologically. This sets up identification of the Nth oldest impression.
Identify the Cap-Triggering (Oldest) Impression
Select the impression ranked at position (count - cap_limit + 1) — i.e., the oldest impression that, once it rolls out of the window, will bring the user back under the cap. If total impressions are fewer than the cap, the user is already eligible and next_eligible_ts is the current timestamp.
Compute Next Eligible Timestamp
Add exactly 7 days (604800 seconds) to the identified oldest impression's timestamp to get next_eligible_ts, representing the moment that impression exits the rolling window. Handle the boundary condition carefully — use strict inequality so the window is (ts, ts + 7 days].
Validate & Discuss Optimizations
Walk through a concrete example with sample data to verify correctness, then discuss scalability — for production at Netflix scale, mention partitioning by user_id, using efficient sorted structures or pre-aggregated impression counts, and potential use of approximate methods if exact counts are too expensive.
Key Points to Mention
The edge cases listed in the problem statement are basically a checklist of ways your query can silently return wrong numbers.
Suggested Approach
Start by clearly defining the data pipeline: identify impressions that exceed the per-user frequency cap within the 7-day window, then join those impressions to click and conversion events while carefully handling the edge cases (zero-click users, multi-conversion chains, and impressions near the campaign end date). Compute ROI as total attributed revenue from over-cap impressions divided by the count of those impressions, using a well-defined attribution model (e.g., last-touch or fractional) to assign revenue back to specific impressions.
Define the Frequency Cap Threshold & Window
Clarify the frequency cap definition (e.g., max N impressions per user per campaign within the 7-day window) and confirm whether the window is rolling or fixed. Use a ROW_NUMBER() or cumulative count partitioned by user_id and campaign_id ordered by impression timestamp to flag impressions beyond the cap.
Isolate Over-Cap Impressions
Filter the impressions table to rows where the cumulative impression rank exceeds the cap threshold and the impression timestamp falls within the 7-day window. Handle the campaign end-date edge case by deciding whether to include impressions served on the final day and defining the downstream attribution cutoff.
Attribute Clicks and Conversions
Join over-cap impressions to click events and then to conversion events using a defined attribution model (e.g., last-touch within a 24-hour window). Use a LEFT JOIN to retain impressions with no clicks or conversions, assigning them zero revenue, and handle multiple conversions per click by summing all conversion revenue tied to that click chain.
Aggregate Revenue per Campaign
Group by campaign_id and sum the attributed revenue across all over-cap impressions, then divide by the count of over-cap impressions to get the per-impression ROI. Ensure the denominator counts impressions (not users or clicks) to match the metric definition.
Validate & Sanity-Check Results
Cross-check totals against known campaign-level revenue figures, verify that zero-revenue impressions are included in the denominator, and confirm no double-counting of conversions across multiple over-cap impressions for the same user. Flag campaigns with very low over-cap impression counts as statistically unreliable.
Key Points to Mention
Discussion(1)
Sign in to join the discussion.
The duplicate revenue thing from joining before aggregating is a classic and you caught it, but the fix has a subtle wrinkle worth spelling out. You want to sum conversion revenue at the conversion grain grouped by click_id before you join to impressions, otherwise each conversion row multiplies against every impression row it touches. So: aggregate conversions to click-level revenue first, join that to clicks, then join clicks to impressions. The over-cap ranking is the part that actually slowed me down conceptually. You can't just do one global ROW_NUMBER per user-campaign partition ordered by timestamp and call rows above cap 'over-cap', because the cap is a rolling 7-day window and which impressions count toward it shifts depending on when each impression was served. Strictly speaking you'd need to recompute the in-window count at each impression's own timestamp, which in standard SQL means a self-join or a correlated subquery counting prior impressions within 7 days of each row. That gets expensive. In practice at a Netflix phone screen I'd name that problem explicitly and propose the self-join approach, then mention that in Spark or with a range frame window function you can do it more cleanly. Showing you understand why the naive ROW_NUMBER is wrong matters more than having the perfect syntax ready. Also don't forget impressions after the campaign end date: filter those out before you do any of the cap ranking, not after, or your row numbers shift.