Write SQL for ads metrics and variability
Company: Roblox
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Write ANSI SQL to compute daily and campaign-level metrics, including averages and standard deviations of daily CTR and CPC, using the schema and sample data below. Assume deduplication: count at most one click per impression (keep earliest click by ts). Treat CPC as NULL on days with zero clicks and exclude NULLs from CPC aggregates. Compute for date range 2025-08-30 to 2025-09-01, and also return which days are CTR outliers per campaign (CTR > mean_daily_ctr + 2*stddev_daily_ctr within the window).
Schema:
- ad_impressions(imp_id BIGINT PRIMARY KEY, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_clicks(click_id BIGINT PRIMARY KEY, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Sample data:
Table: ad_impressions
+--------+---------+-------+-------------+---------------------+
| imp_id | user_id | ad_id | campaign_id | ts |
+--------+---------+-------+-------------+---------------------+
| 1 | 101 | 11 | 1001 | 2025-08-30 10:00:00 |
| 2 | 102 | 12 | 1001 | 2025-08-30 10:05:00 |
| 3 | 101 | 11 | 1002 | 2025-08-31 09:00:00 |
| 4 | 103 | 13 | 1001 | 2025-08-31 10:00:00 |
| 5 | 104 | 14 | 1002 | 2025-09-01 12:00:00 |
+--------+---------+-------+-------------+---------------------+
Table: ad_clicks
+----------+--------+---------+-------+-------------+---------------------+
| click_id | imp_id | user_id | ad_id | campaign_id | ts |
+----------+--------+---------+-------+-------------+---------------------+
| 10 | 1 | 101 | 11 | 1001 | 2025-08-30 10:00:03 |
| 11 | 2 | 102 | 12 | 1001 | 2025-08-30 10:05:04 |
| 12 | 2 | 102 | 12 | 1001 | 2025-08-30 10:05:05 |
| 13 | 4 | 103 | 13 | 1001 | 2025-08-31 10:00:05 |
+----------+--------+---------+-------+-------------+---------------------+
Table: ad_spend
+------------+-------------+-------------+
| dt | campaign_id | spend_cents |
+------------+-------------+-------------+
| 2025-08-30 | 1001 | 300 |
| 2025-08-31 | 1001 | 200 |
| 2025-08-31 | 1002 | 100 |
| 2025-09-01 | 1002 | 500 |
+------------+-------------+-------------+
Tasks:
A) Produce a daily table per campaign with: dt, impressions, unique_clicks_dedup, CTR (unique_clicks_dedup/impressions), CPC (spend_cents/unique_clicks_dedup; NULL if 0), and a flag if the day is an outlier based on the 2-sigma rule computed over 2025-08-30..2025-09-01.
B) Produce an aggregate per campaign with mean_daily_ctr and stddev_daily_ctr (use STDDEV_SAMP over daily CTR values where impressions>0), mean_daily_cpc and stddev_daily_cpc (exclude NULL CPC). Also return a comma-separated list of dt that are outliers.
C) How would your query change if the window were the last 7 days relative to "today" where today=2025-09-01?
Overview: This question evaluates a candidate's ability to perform SQL-based data manipulation and analytics, including deduplication of events, computation of daily and campaign-level metrics (CTR, CPC), and calculation of statistical summaries (mean and standard deviation) and outlier detection over a specified date range.
Daily campaign metrics with CTR/CPC and outlier flag
Using the tables and sample data below, write ANSI SQL to compute a daily metrics table per campaign for the date range FROM 2025-08-30 TO 2025-09-01 (inclusive).
Requirements:
- Deduplicate clicks to count at most one click per impression, keeping the earliest click by ts.
- Compute metrics per (dt, campaign_id):
- impressions
- unique_clicks_dedup (deduplicated clicks)
- ctr = unique_clicks_dedup / impressions (as a numeric value)
- cpc = spend_cents / unique_clicks_dedup; if unique_clicks_dedup = 0, cpc must be NULL
- For daily ctr and cpc, use the impression date (CAST(ad_impressions.ts AS DATE)) as dt.
- Use ad_spend.spend_cents for the same (dt, campaign_id); if there is no spend row, treat spend_cents as 0.
- Over the window 2025-08-30..2025-09-01, compute the mean_daily_ctr and stddev_daily_ctr per campaign using the daily ctr values, and mark a day as a CTR outlier if:
daily_ctr > mean_daily_ctr + 2 * stddev_daily_ctr
- Return one row per (dt, campaign_id) in the date range, with a boolean flag is_ctr_outlier.
Schema:
- ad_impressions(imp_id BIGINT PRIMARY KEY, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_clicks(click_id BIGINT PRIMARY KEY, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Tables
ad_impressions(imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_clicks(click_id BIGINT, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Hints
- First build a daily (dt, campaign_id) fact table with impressions, deduplicated clicks, and spend before computing CTR and CPC.
- Use window functions (AVG and STDDEV_SAMP partitioned by campaign_id) over the daily CTR values to derive the outlier threshold.
Campaign-level averages and variability of daily CTR and CPC
You are analyzing ad performance for the date window **2025-08-30 through 2025-09-01 (inclusive)**.
You have three tables:
- `ad_impressions(imp_id, user_id, ad_id, campaign_id, ts)` — one row per ad impression. `ts` is a `TIMESTAMP`.
- `ad_clicks(click_id, imp_id, user_id, ad_id, campaign_id, ts)` — one row per recorded click. Multiple click rows can point at the same `imp_id` (duplicate clicks on one impression).
- `ad_spend(dt, campaign_id, spend_cents)` — daily ad spend in cents per campaign.
**Step 1 — Build daily per-campaign metrics** (restricted to the window above, bucketing by `CAST(ts AS DATE)`):
- `impressions` = number of impression rows for that `(dt, campaign_id)`.
- `unique_clicks_dedup` = number of **distinct impressions that received at least one click** for that `(dt, campaign_id)` (i.e. deduplicate multiple clicks on the same `imp_id` down to one), where the impression's date is taken from `ad_impressions.ts`.
- `daily_ctr` = `unique_clicks_dedup / impressions` when `impressions > 0`, else `NULL`.
- `daily_cpc` = `spend_cents / unique_clicks_dedup` when `unique_clicks_dedup > 0`, else `NULL` (a day with zero clicks has no CPC).
**Step 2 — Aggregate to one row per campaign** over its days that have `impressions > 0`:
- `mean_daily_ctr` = `AVG(daily_ctr)`.
- `stddev_daily_ctr` = `STDDEV_SAMP(daily_ctr)` (sample standard deviation; `NULL` for a single day).
- `mean_daily_cpc` = `AVG(daily_cpc)` over days where `daily_cpc IS NOT NULL`.
- `stddev_daily_cpc` = `STDDEV_SAMP(daily_cpc)` over the non-NULL daily CPC values.
**Step 3 — Outlier dates.** A day is a **CTR outlier** for a campaign when `daily_ctr > mean_daily_ctr + 2 * stddev_daily_ctr` (only meaningful when the campaign has a defined `stddev_daily_ctr`). For each campaign, build `outlier_dates`: a comma-separated string of the outlier `dt` values formatted as `YYYY-MM-DD`, ordered ascending. If there are none, return an empty string `''`.
**Output:** one row per campaign that has at least one day with `impressions > 0`, with columns `campaign_id, mean_daily_ctr, stddev_daily_ctr, mean_daily_cpc, stddev_daily_cpc, outlier_dates`. The numeric columns are rounded to 10 decimal places. **Order by `campaign_id` ascending.**
Tables
ad_impressions(imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_clicks(click_id BIGINT, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Hints
- Deduplicate clicks first: GROUP `ad_clicks` by `imp_id` so each clicked impression counts once, then attribute it to the impression's date/campaign before counting per day.
- Use a FULL OUTER JOIN of daily impressions, dedup clicks, and spend with COALESCE so a day with impressions but no clicks still appears (CTR computable, CPC NULL).
Adapting the metrics query to a 7-day explicit date window
Suppose you now need the same daily metrics and outlier logic as in Question 1, but instead of the fixed window 2025-08-30..2025-09-01 you must compute over the window FROM 2025-05-26 TO 2025-06-01 (inclusive), representing the last 7 days relative to a fixed reference date 2025-06-01.
Write ANSI SQL that is equivalent to your solution for Question 1, except that it uses this new explicit date range (2025-05-26..2025-06-01) everywhere the previous query used 2025-08-30..2025-09-01.
Schema:
- ad_impressions(imp_id BIGINT PRIMARY KEY, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_clicks(click_id BIGINT PRIMARY KEY, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
- ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Tables
ad_impressions(imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_clicks(click_id BIGINT, imp_id BIGINT, user_id BIGINT, ad_id BIGINT, campaign_id BIGINT, ts TIMESTAMP)
ad_spend(dt DATE, campaign_id BIGINT, spend_cents INT)
Hints
- The only change from Question 1 is the date window; replace all occurrences of the original start and end dates with the new ones.
- Make sure you update the WHERE clauses in every CTE that filters by date (impressions, clicks via impression date, and spend).