This part looked manageable until I realized 'active in last 7 days' and 'at cap' are two different populations and I was conflating them.
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.
Pro tip: Explicitly handle the 'active user' definition before writing any SQL — at Netflix scale, whether 'active' means 'received at least one impression in the window' versus 'has a non-null user record' can swing the exceedance percentage dramatically, and calling this out signals production-level thinking.
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.
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 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).
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).
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.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
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.
Pro tip: Mention edge cases like users who haven't yet hit the cap (next eligible timestamp is 'now'), ties in timestamps, and timezone normalization — Netflix operates globally, so clarifying whether the 7-day window is a rolling 168-hour window or a calendar-week boundary signals real-world production thinking.
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.
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.
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.
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].
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.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
The edge cases listed in the problem statement are basically a checklist of ways your query can silently return wrong numbers.
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.
Pro tip: Explicitly call out that 'impressions straddling the campaign end date' introduces a temporal ambiguity — an impression served on day 7 may generate a conversion on day 8 — and propose a clear policy (e.g., include conversions within a fixed attribution window post-impression) rather than leaving it undefined, which signals production-level thinking.
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.
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.
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.
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.
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.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.