cswork / libraryOpen practice workspace

Coding questions · Netflix

Write SQL for rolling frequency caps

This question evaluates proficiency with rolling-window analytics, frequency cap logic, time-based attribution, and per-user/per-campaign aggregation for advertising data.

You work on ad serving with per-user rolling 7-day frequency caps at the campaign level. Assume "today" is 2025-09-01 and t0 = 2025-09-01 12:00:00 UTC. Write SQL (any dialect with window functions and common table expressions) to answer parts (A)-(C) exactly using the schema and sample data below.

Schema:

  • users(user_id INT PRIMARY KEY, household_id TEXT, country TEXT)
  • campaigns(campaign_id INT PRIMARY KEY, cap_7d INT, start_date DATE, end_date DATE)
  • impressions(impression_id INT PRIMARY KEY, ts TIMESTAMP, user_id INT, campaign_id INT)
  • clicks(click_id INT PRIMARY KEY, impression_id INT, ts TIMESTAMP)
  • conversions(conv_id INT PRIMARY KEY, click_id INT, ts TIMESTAMP, revenue DECIMAL(10,2))

Sample data (ASCII):

users
user_id | household_id | country
1 | A | US
2 | A | US
3 | B | CA
4 | C | US

campaigns
campaign_id | cap_7d | start_date | end_date
10 | 3 | 2025-08-15 | 2025-09-30
20 | 5 | 2025-08-20 | 2025-09-15

impressions
impression_id | ts | user_id | campaign_id
100 | 2025-08-26 10:00 | 1 | 10
101 | 2025-08-30 09:00 | 1 | 10
102 | 2025-08-31 09:10 | 1 | 10
103 | 2025-09-01 08:00 | 1 | 10
104 | 2025-08-28 12:00 | 2 | 10
105 | 2025-08-30 13:00 | 2 | 10
106 | 2025-08-26 14:00 | 3 | 20
107 | 2025-08-29 15:00 | 3 | 20
108 | 2025-08-30 16:00 | 3 | 20
109 | 2025-09-01 11:00 | 4 | 10

clicks
click_id | impression_id | ts
1000 | 101 | 2025-08-30 09:05
1001 | 102 | 2025-08-31 09:12
1002 | 108 | 2025-08-30 16:01
1003 | 103 | 2025-09-01 08:01

conversions
conv_id | click_id | ts | revenue
2000 | 1000 | 2025-08-30 10:00 | 5.00
2001 | 1001 | 2025-09-01 10:00 | 0.00
2002 | 1002 | 2025-09-01 12:00 | 20.00
2003 | 1003 | 2025-09-01 13:00 | 8.00

Assumptions: (i) Only impressions within a campaign’s [start_date, end_date] are valid; (ii) A rolling 7-day window at time t includes [t-7 days, t); (iii) An impression beyond the cap is the (cap_7d+1)-th, (cap_7d+2)-th, etc., within the 7-day window; (iv) Revenue is attributed to the impression via click->conversion chain; (v) If multiple conversions follow a click, sum their revenue.

Tasks: (A) For each campaign at t0, output: campaign_id, users_at_cap (count of users whose last-7-day impression count equals cap_7d), users_one_below_cap (count with last-7-day count = cap_7d-1), and pct_over_cap_if_one_more (percentage of active users in last 7 days who would exceed the cap if each received one more impression at t0). (B) For every (user_id, campaign_id) seen in the last 7 days, compute next_eligible_time: the earliest timestamp > t0 when serving an additional impression would not breach the 7-day cap because the oldest in-window impression has rolled out. Return user_id, campaign_id, next_eligible_time. (C) For 2025-08-25 12:00 <= ts < 2025-09-01 12:00, compute, per campaign, ROI_beyond_cap = (revenue from impressions beyond cap within the window) / (count of impressions beyond cap within the window). Return campaign_id, impressions_beyond_cap, revenue_beyond_cap, ROI_beyond_cap. Edge cases must be handled correctly: users with 0 clicks/conversions, multiple conversions per click, impressions straddling campaign end_date, and users with impressions both before and after the window boundary.

Rolling 7-day frequency caps at campaign level (Part A: cap-status summary at t0)

You work on ad serving with per-user rolling 7-day frequency caps at the campaign level.

Assume:

  • Fixed current date is 2025-06-01.
  • t0 = TIMESTAMP '2025-06-01 12:00:00' UTC.
  • A rolling 7-day window at time t includes [t - 7 days, t).
  • Only impressions within a campaign’s [start_date, end_date] (inclusive) are valid.

Task (A) For each campaign at t0, compute metrics using impressions in the last 7 days window [2025-05-25 12:00:00, 2025-06-01 12:00:00): Return:

  • campaign_id
  • users_at_cap: count of users whose last-7-day impression count equals cap_7d
  • users_one_below_cap: count of users whose last-7-day impression count equals cap_7d - 1
  • pct_over_cap_if_one_more: percentage of active users (users with >=1 valid impression in the last 7 days for that campaign) who would exceed the cap if they each received one additional impression at exactly t0. (A user would exceed if their current last-7-day count is >= cap_7d.)

Notes:

  • Consider each campaign independently.
  • Output percentages as a number from 0 to 100 (rounded to 2 decimals).

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
household_idVARCHAR(10)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryhousehold_iduser_id
USA1
USA2
CAB3
USC4

Table campaigns

columntypeconstraints
campaign_idINTPRIMARY KEY, NOT NULL
cap_7dINTNOT NULL
start_dateDATENOT NULL
end_dateDATENOT NULL

Sample data:

campaign_idcap_7dend_datestart_date
1032025-06-302025-05-15
2052025-06-152025-05-20

Table impressions

columntypeconstraints
impression_idINTPRIMARY KEY, NOT NULL
tsTIMESTAMPNOT NULL
user_idINTNOT NULL
campaign_idINTNOT NULL

Sample data:

campaign_idimpression_idtsuser_id
101002025-05-26 10:00:001
101012025-05-30 09:00:001
101022025-05-31 09:10:001
101032025-06-01 08:00:001
101042025-05-28 12:00:002
101052025-05-30 13:00:002
201062025-05-26 14:00:003
201072025-05-29 15:00:003
201082025-05-30 16:00:003
101092025-06-01 11:00:004

Table clicks

columntypeconstraints
click_idINTPRIMARY KEY, NOT NULL
impression_idINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

click_idimpression_idts
10001012025-05-30 09:05:00
10011022025-05-31 09:12:00
10021082025-05-30 16:01:00
10031032025-06-01 08:01:00

Table conversions

columntypeconstraints
conv_idINTPRIMARY KEY, NOT NULL
click_idINTNOT NULL
tsTIMESTAMPNOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

click_idconv_idrevenuets
1000200052025-05-30 10:00:00
1001200102025-06-01 10:00:00
10022002202025-06-01 11:30:00
1003200382025-06-01 13:00:00
1003200422025-06-02 09:00:00

Expected output

One row per campaign with counts of users at cap / one below cap, and the percent of active users who would be over-cap if they each received one additional impression at t0.

campaign_idpct_over_cap_if_one_moreusers_at_capusers_one_below_cap
1033.3301
20000

Hints

  1. Compute per-(user_id, campaign_id) impression counts in [t0-7d, t0).
  2. Active users are those with at least one valid impression in the window.
  3. A user would exceed after one more impression at t0 iff current_count >= cap_7d.

Rolling 7-day frequency caps: next eligible time per user-campaign

You work on ad serving with per-user rolling 7-day frequency caps at the campaign level.

Assume:

  • t0 = TIMESTAMP '2025-06-01 12:00:00' UTC.
  • A rolling 7-day window at time t includes [t - 7 days, t).
  • Only impressions within a campaign's inclusive [start_date, end_date] are valid.
  • An impression at timestamp x rolls out of the window at exactly x + INTERVAL '7 days'.

For every (user_id, campaign_id) with at least one valid impression in [2025-05-25 12:00:00, 2025-06-01 12:00:00), compute next_eligible_time: the earliest timestamp at or after t0 when serving one additional impression would not breach that campaign's cap_7d.

Return user_id, campaign_id, and next_eligible_time formatted as YYYY-MM-DD HH24:MI:SS, ordered by user_id, then campaign_id.

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
household_idVARCHAR(10)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryhousehold_iduser_id
USA1
USA2
CAB3
USC4

Table campaigns

columntypeconstraints
campaign_idINTPRIMARY KEY, NOT NULL
cap_7dINTNOT NULL
start_dateDATENOT NULL
end_dateDATENOT NULL

Sample data:

campaign_idcap_7dend_datestart_date
1032025-06-302025-05-15
2052025-06-152025-05-20

Table impressions

columntypeconstraints
impression_idINTPRIMARY KEY, NOT NULL
tsTIMESTAMPNOT NULL
user_idINTNOT NULL
campaign_idINTNOT NULL

Sample data:

campaign_idimpression_idtsuser_id
101002025-05-26 10:00:001
101012025-05-30 09:00:001
101022025-05-31 09:10:001
101032025-06-01 08:00:001
101042025-05-28 12:00:002
101052025-05-30 13:00:002
201062025-05-26 14:00:003
201072025-05-29 15:00:003
201082025-05-30 16:00:003
101092025-06-01 11:00:004

Table clicks

columntypeconstraints
click_idINTPRIMARY KEY, NOT NULL
impression_idINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

click_idimpression_idts
10001012025-05-30 09:05:00
10011022025-05-31 09:12:00
10021082025-05-30 16:01:00
10031032025-06-01 08:01:00

Table conversions

columntypeconstraints
conv_idINTPRIMARY KEY, NOT NULL
click_idINTNOT NULL
tsTIMESTAMPNOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

click_idconv_idrevenuets
1000200052025-05-30 10:00:00
1001200102025-06-01 10:00:00
10022002202025-06-01 11:30:00
1003200382025-06-01 13:00:00
1003200422025-06-02 09:00:00

Expected output

One row per (user_id, campaign_id) that has at least one valid impression in the last 7 days, showing the earliest time at/after t0 that an additional impression would not breach cap_7d.

campaign_idnext_eligible_timeuser_id
102025-06-06 09:00:001
102025-06-01 12:00:002
202025-06-01 12:00:003
102025-06-01 12:00:004

Hints

  1. If the current in-window count is below cap_7d, the next eligible time is t0.
  2. If the count is at or above the cap, rank the in-window impressions from oldest to newest and find the impression that must roll out.
  3. Use TO_CHAR(..., 'YYYY-MM-DD HH24:MI:SS') for the final timestamp display.

Rolling 7-day frequency caps (Part C: ROI of impressions beyond cap)

You work on ad serving with per-user rolling 7-day frequency caps at the campaign level.

Assume:

  • Fixed current date is 2025-06-01.
  • t0 = TIMESTAMP '2025-06-01 12:00:00' UTC.
  • A rolling 7-day window at time t includes [t - 7 days, t).
  • Only impressions within a campaign’s [start_date, end_date] (inclusive) are valid.
  • An impression is considered "beyond cap" if, at that impression’s timestamp, it is the (cap_7d+1)-th, (cap_7d+2)-th, etc. impression for that (user_id, campaign_id) within the prior 7 days.
  • Revenue is attributed to the impression through the impression -> click -> conversion chain.
  • If multiple conversions follow the same click, sum their revenue.
  • For this task, include all attributed revenue for those impressions regardless of conversion timestamp.

Task (C) For impressions with 2025-05-25 12:00:00 <= impression.ts < 2025-06-01 12:00:00, compute per campaign:

  • impressions_beyond_cap: number of impressions in that impression-ts window that are beyond cap
  • revenue_beyond_cap: total revenue attributed to those beyond-cap impressions
  • roi_beyond_cap = revenue_beyond_cap / impressions_beyond_cap (NULL if impressions_beyond_cap = 0)

Return: campaign_id, impressions_beyond_cap, revenue_beyond_cap, roi_beyond_cap.

Table users

columntypeconstraints
user_idINTPRIMARY KEY, NOT NULL
household_idVARCHAR(10)NOT NULL
countryVARCHAR(2)NOT NULL

Sample data:

countryhousehold_iduser_id
USA1
USA2
CAB3
USC4

Table campaigns

columntypeconstraints
campaign_idINTPRIMARY KEY, NOT NULL
cap_7dINTNOT NULL
start_dateDATENOT NULL
end_dateDATENOT NULL

Sample data:

campaign_idcap_7dend_datestart_date
1032025-06-302025-05-15
2052025-06-152025-05-20

Table impressions

columntypeconstraints
impression_idINTPRIMARY KEY, NOT NULL
tsTIMESTAMPNOT NULL
user_idINTNOT NULL
campaign_idINTNOT NULL

Sample data:

campaign_idimpression_idtsuser_id
101002025-05-26 10:00:001
101012025-05-30 09:00:001
101022025-05-31 09:10:001
101032025-06-01 08:00:001
101042025-05-28 12:00:002
101052025-05-30 13:00:002
201062025-05-26 14:00:003
201072025-05-29 15:00:003
201082025-05-30 16:00:003
101092025-06-01 11:00:004

Table clicks

columntypeconstraints
click_idINTPRIMARY KEY, NOT NULL
impression_idINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

click_idimpression_idts
10001012025-05-30 09:05:00
10011022025-05-31 09:12:00
10021082025-05-30 16:01:00
10031032025-06-01 08:01:00

Table conversions

columntypeconstraints
conv_idINTPRIMARY KEY, NOT NULL
click_idINTNOT NULL
tsTIMESTAMPNOT NULL
revenueDECIMAL(10,2)NOT NULL

Sample data:

click_idconv_idrevenuets
1000200052025-05-30 10:00:00
1001200102025-06-01 10:00:00
10022002202025-06-01 11:30:00
1003200382025-06-01 13:00:00
1003200422025-06-02 09:00:00

Expected output

One row per campaign that appears in the impression-ts analysis window. Shows how many impressions were beyond cap, their attributed revenue, and revenue per beyond-cap impression (NULL when count is 0).

campaign_idimpressions_beyond_caprevenue_beyond_caproi_beyond_cap
1011010
2000NULL

Hints

  1. To label an impression as beyond-cap, compare cap_7d to the count of impressions for the same user+campaign in [ts-7d, ts).
  2. Compute revenue per impression by aggregating conversions per click and then per impression_id.
  3. Use LEFT JOINs and COALESCE to handle impressions with no clicks/conversions.