Coding questions · Meta
Compute SHOP spend share and model performance
This question evaluates proficiency in SQL/Python data manipulation and analytics, focusing on aggregations, joins, time-window computations, handling missing or zero values, and deriving key metrics such as revenue share and revenue-per-conversion.
You work on ads measurement. Advertisers can drive users to either Facebook Shop ('SHOP') or their own website ('WEBSITE'). After an ad is shown, you attribute downstream revenue and conversions to the destination.
Assume the following tables (all timestamps are in UTC and event_date is a calendar date):
Table 1: ad_revenue
event_dateDATEadvertiser_idBIGINTdestinationVARCHAR -- values:'SHOP'or'WEBSITE'revenue_usdNUMERIC -- attributed revenue in USD
Table 2: ad_conversions
event_dateDATEadvertiser_idBIGINTdestinationVARCHAR -- values:'SHOP'or'WEBSITE'conversionsBIGINT -- attributed conversion count
Notes/assumptions
- There is at most one row per (
event_date,advertiser_id,destination) per table. - “Past 30 days” means the most recent 30 calendar days including the max
event_datein the data.
Tasks
- SHOP share over past 30 days
Write SQL to compute, for each event_date in the past 30 days, the share of revenue going to destination='SHOP':
Required output columns:
event_dateshop_revenuetotal_revenueshop_revenue_share=shop_revenue / total_revenue
- How does the FB model perform?
Using only these two tables, write SQL to produce a stakeholder-ready daily time series for the past 30 days that helps evaluate performance by destination.
At minimum, include:
event_datedestinationrevenue_usdconversionsrevenue_per_conversion=revenue_usd / NULLIF(conversions, 0)
Also include at least one “share”-style diagnostic (e.g., revenue share or conversion share across destinations) that could indicate whether performance is shifting toward SHOP vs WEBSITE.
State any additional assumptions you make (e.g., how to handle missing rows / zero totals).
Compute SHOP revenue share over the last 30 days
Facebook ads can lead customers to either an advertiser's in-app checkout ('SHOP') or the advertiser's external site ('WEBSITE').
Using the data in ad_revenue, compute how the share of revenue attributed to 'SHOP' performed over the last 30 days.
Assume "last 30 days" means FROM 2025-05-03 TO 2025-06-01 (inclusive).
Return one row per advertiser plus one additional roll-up row for all advertisers combined.
Output columns:
- advertiser_id (use NULL for the all-advertisers roll-up)
- total_revenue_usd
- shop_revenue_usd
- shop_revenue_share (shop_revenue_usd / total_revenue_usd, rounded to 4 decimals)
Notes:
- Use
actual_channelto determine whether revenue is SHOP vs WEBSITE. - Only include rows whose
event_dateis in the given date range.
Table ad_revenue
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY |
| event_date | DATE | NOT NULL |
| advertiser_id | INT | NOT NULL |
| model_version | VARCHAR(10) | NOT NULL |
| predicted_channel | VARCHAR(10) | NOT NULL |
| actual_channel | VARCHAR(10) | NOT NULL |
| revenue_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| actual_channel | advertiser_id | event_date | impression_id | model_version | predicted_channel | revenue_usd |
|---|---|---|---|---|---|---|
| SHOP | 101 | 2025-05-01 | 1 | v1 | SHOP | 100 |
| WEBSITE | 101 | 2025-05-03 | 2 | v1 | SHOP | 80 |
| WEBSITE | 101 | 2025-05-10 | 3 | v1 | WEBSITE | 120 |
| SHOP | 101 | 2025-05-20 | 4 | v2 | SHOP | 200 |
| SHOP | 101 | 2025-05-28 | 5 | v2 | WEBSITE | 60 |
| SHOP | 102 | 2025-05-05 | 6 | v1 | SHOP | 150 |
| WEBSITE | 102 | 2025-05-18 | 7 | v1 | SHOP | 50 |
| WEBSITE | 102 | 2025-05-30 | 8 | v2 | WEBSITE | 130 |
| SHOP | 102 | 2025-06-01 | 9 | v2 | SHOP | 90 |
| SHOP | 103 | 2025-05-07 | 10 | v1 | WEBSITE | 70 |
| SHOP | 103 | 2025-05-25 | 11 | v2 | SHOP | 110 |
| WEBSITE | 103 | 2025-05-31 | 12 | v2 | WEBSITE | 160 |
| SHOP | 103 | 2025-05-15 | 13 | v1 | SHOP | 40 |
Table ad_conversions
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY |
| event_date | DATE | NOT NULL |
| conversions | INT | NOT NULL |
Sample data:
| conversions | event_date | impression_id |
|---|---|---|
| 2 | 2025-05-01 | 1 |
| 1 | 2025-05-03 | 2 |
| 2 | 2025-05-10 | 3 |
| 3 | 2025-05-20 | 4 |
| 1 | 2025-05-28 | 5 |
| 2 | 2025-05-05 | 6 |
| 1 | 2025-05-18 | 7 |
| 2 | 2025-05-30 | 8 |
| 1 | 2025-06-01 | 9 |
| 1 | 2025-05-07 | 10 |
| 2 | 2025-05-25 | 11 |
| 4 | 2025-05-31 | 12 |
| 1 | 2025-05-15 | 13 |
Expected output
One row per advertiser plus a roll-up row (advertiser_id = NULL) showing total revenue, SHOP revenue, and SHOP share for 2025-05-03 through 2025-06-01 inclusive.
| advertiser_id | shop_revenue_share | shop_revenue_usd | total_revenue_usd |
|---|---|---|---|
| 101 | 0.5652 | 260 | 460 |
| 102 | 0.5714 | 240 | 420 |
| 103 | 0.5789 | 220 | 380 |
| NULL | 0.5714 | 720 | 1260 |
Hints
- Use conditional aggregation: SUM(CASE WHEN actual_channel='SHOP' THEN revenue_usd END).
- Add an overall row with UNION ALL (or GROUPING SETS if supported).
Evaluate model performance over the last 30 days
Facebook uses a model to predict whether a user should be sent to 'SHOP' or 'WEBSITE'.
Using ad_revenue and ad_conversions, evaluate model performance for the date range FROM 2025-05-03 TO 2025-06-01 (inclusive).
Define model performance as:
- prediction_accuracy: fraction of impressions where predicted_channel = actual_channel (rounded to 4 decimals)
- total_revenue_usd: sum of revenue_usd
- total_conversions: sum of conversions
- revenue_per_conversion_usd: total_revenue_usd / total_conversions (rounded to 2 decimals)
Return one row per model_version.
Table ad_revenue
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY |
| event_date | DATE | NOT NULL |
| advertiser_id | INT | NOT NULL |
| model_version | VARCHAR(10) | NOT NULL |
| predicted_channel | VARCHAR(10) | NOT NULL |
| actual_channel | VARCHAR(10) | NOT NULL |
| revenue_usd | DECIMAL(10,2) | NOT NULL |
Sample data:
| actual_channel | advertiser_id | event_date | impression_id | model_version | predicted_channel | revenue_usd |
|---|---|---|---|---|---|---|
| SHOP | 101 | 2025-05-01 | 1 | v1 | SHOP | 100 |
| WEBSITE | 101 | 2025-05-03 | 2 | v1 | SHOP | 80 |
| WEBSITE | 101 | 2025-05-10 | 3 | v1 | WEBSITE | 120 |
| SHOP | 101 | 2025-05-20 | 4 | v2 | SHOP | 200 |
| SHOP | 101 | 2025-05-28 | 5 | v2 | WEBSITE | 60 |
| SHOP | 102 | 2025-05-05 | 6 | v1 | SHOP | 150 |
| WEBSITE | 102 | 2025-05-18 | 7 | v1 | SHOP | 50 |
| WEBSITE | 102 | 2025-05-30 | 8 | v2 | WEBSITE | 130 |
| SHOP | 102 | 2025-06-01 | 9 | v2 | SHOP | 90 |
| SHOP | 103 | 2025-05-07 | 10 | v1 | WEBSITE | 70 |
| SHOP | 103 | 2025-05-25 | 11 | v2 | SHOP | 110 |
| WEBSITE | 103 | 2025-05-31 | 12 | v2 | WEBSITE | 160 |
| SHOP | 103 | 2025-05-15 | 13 | v1 | SHOP | 40 |
Table ad_conversions
| column | type | constraints |
|---|---|---|
| impression_id | BIGINT | PRIMARY KEY |
| event_date | DATE | NOT NULL |
| conversions | INT | NOT NULL |
Sample data:
| conversions | event_date | impression_id |
|---|---|---|
| 2 | 2025-05-01 | 1 |
| 1 | 2025-05-03 | 2 |
| 2 | 2025-05-10 | 3 |
| 3 | 2025-05-20 | 4 |
| 1 | 2025-05-28 | 5 |
| 2 | 2025-05-05 | 6 |
| 1 | 2025-05-18 | 7 |
| 2 | 2025-05-30 | 8 |
| 1 | 2025-06-01 | 9 |
| 1 | 2025-05-07 | 10 |
| 2 | 2025-05-25 | 11 |
| 4 | 2025-05-31 | 12 |
| 1 | 2025-05-15 | 13 |
Expected output
One row per model_version with accuracy (predicted_channel vs actual_channel), total revenue, total conversions, and revenue per conversion for 2025-05-03 through 2025-06-01 inclusive.
| model_version | prediction_accuracy | revenue_per_conversion_usd | total_conversions | total_revenue_usd |
|---|---|---|---|---|
| v1 | 0.5 | 63.75 | 8 | 510 |
| v2 | 0.8333 | 57.69 | 13 | 750 |
Hints
- Accuracy can be computed as AVG of a 0/1 indicator for correct predictions.
- Join revenue to conversions on impression_id, and filter by the explicit date range.