cswork / libraryOpen practice workspace

Coding questions · Roblox

Write SQL for ads metrics and variability

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.

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?

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)

Table ad_impressions

columntypeconstraints
imp_idBIGINTPRIMARY KEY
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idimp_idtsuser_id
11100112025-08-30 10:00:00101
12100122025-08-30 10:05:00102
11100232025-08-31 09:00:00101
13100142025-08-31 10:00:00103
14100252025-09-01 12:00:00104

Table ad_clicks

columntypeconstraints
click_idBIGINTPRIMARY KEY
imp_idBIGINTNOT NULL
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idclick_idimp_idtsuser_id
1110011012025-08-30 10:00:03101
1210011122025-08-30 10:05:04102
1210011222025-08-30 10:05:05102
1310011342025-08-31 10:00:05103

Table ad_spend

columntypeconstraints
dtDATENOT NULL
campaign_idBIGINTNOT NULL
spend_centsINTNOT NULL

Sample data:

campaign_iddtspend_cents
10012025-08-30300
10012025-08-31200
10022025-08-31100
10022025-09-01500

Expected output

One row per (dt, campaign_id) between 2025-08-30 and 2025-09-01 with impressions, deduplicated clicks, daily ctr, daily cpc, and a boolean is_ctr_outlier flag based on the 2-sigma rule within that window.

campaign_idcpcctrdtimpressionsis_ctr_outlierunique_clicks_dedup
100115012025-08-302false2
100120012025-08-311false1
1002NULL02025-08-311false0
1002NULL02025-09-011false0

Hints

  1. First build a daily (dt, campaign_id) fact table with impressions, deduplicated clicks, and spend before computing CTR and CPC.
  2. 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.

Table ad_impressions

columntypeconstraints
imp_idBIGINTPRIMARY KEY
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idimp_idtsuser_id
11100112025-08-30 10:00:00101
12100122025-08-30 10:05:00102
11100232025-08-31 09:00:00101
13100142025-08-31 10:00:00103
14100252025-09-01 12:00:00104

Table ad_clicks

columntypeconstraints
click_idBIGINTPRIMARY KEY
imp_idBIGINTNOT NULL
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idclick_idimp_idtsuser_id
1110011012025-08-30 10:00:03101
1210011122025-08-30 10:05:04102
1210011222025-08-30 10:05:05102
1310011342025-08-31 10:00:05103

Table ad_spend

columntypeconstraints
dtDATENOT NULL
campaign_idBIGINTNOT NULL
spend_centsINTNOT NULL

Sample data:

campaign_iddtspend_cents
10012025-08-30300
10012025-08-31200
10022025-08-31100
10022025-09-01500

Expected output

One row per campaign with mean and sample standard deviation of daily CTR and CPC over 2025-08-30..2025-09-01, plus a comma-separated list of CTR outlier dates; with this data no day is an outlier, and campaign 1002 has zero clicks so its CPC stats are NULL.

campaign_idmean_daily_cpcmean_daily_ctroutlier_datesstddev_daily_cpcstddev_daily_ctr
1001175.00000000001.000000000035.35533905930E-10
1002NULL0E-10NULL0E-10

Hints

  1. 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.
  2. 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).
  3. In PostgreSQL build the comma-separated outlier list with STRING_AGG(TO_CHAR(dt,'YYYY-MM-DD'), ',' ORDER BY dt) — there is no LISTAGG / WITHIN GROUP.

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)

Table ad_impressions

columntypeconstraints
imp_idBIGINTPRIMARY KEY
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idimp_idtsuser_id
11100112025-08-30 10:00:00101
12100122025-08-30 10:05:00102
11100232025-08-31 09:00:00101
13100142025-08-31 10:00:00103
14100252025-09-01 12:00:00104

Table ad_clicks

columntypeconstraints
click_idBIGINTPRIMARY KEY
imp_idBIGINTNOT NULL
user_idBIGINTNOT NULL
ad_idBIGINTNOT NULL
campaign_idBIGINTNOT NULL
tsTIMESTAMPNOT NULL

Sample data:

ad_idcampaign_idclick_idimp_idtsuser_id
1110011012025-08-30 10:00:03101
1210011122025-08-30 10:05:04102
1210011222025-08-30 10:05:05102
1310011342025-08-31 10:00:05103

Table ad_spend

columntypeconstraints
dtDATENOT NULL
campaign_idBIGINTNOT NULL
spend_centsINTNOT NULL

Sample data:

campaign_iddtspend_cents
10012025-08-30300
10012025-08-31200
10022025-08-31100
10022025-09-01500

Expected output

The query structure is identical to Question 1, but filters all tables to the explicit date range 2025-05-26..2025-06-01. With the provided sample data (which only contains rows in late August and early September), this window contains no data, so the result set is empty.

Hints

  1. The only change from Question 1 is the date window; replace all occurrences of the original start and end dates with the new ones.
  2. Make sure you update the WHERE clauses in every CTE that filters by date (impressions, clicks via impression date, and spend).