Compute SHOP spend share and model performance
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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_date` DATE
- `advertiser_id` BIGINT
- `destination` VARCHAR -- values: `'SHOP'` or `'WEBSITE'`
- `revenue_usd` NUMERIC -- attributed revenue in USD
### Table 2: `ad_conversions`
- `event_date` DATE
- `advertiser_id` BIGINT
- `destination` VARCHAR -- values: `'SHOP'` or `'WEBSITE'`
- `conversions` BIGINT -- 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_date` in the data**.
## Tasks
1) **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_date`
- `shop_revenue`
- `total_revenue`
- `shop_revenue_share` = `shop_revenue / total_revenue`
2) **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_date`
- `destination`
- `revenue_usd`
- `conversions`
- `revenue_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).
Overview: 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.
Read the full Meta Data Scientist interview experience this question came from
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_channel` to determine whether revenue is SHOP vs WEBSITE.
- Only include rows whose `event_date` is in the given date range.
Tables
ad_revenue(impression_id BIGINT, event_date DATE, advertiser_id INT, model_version VARCHAR(10), predicted_channel VARCHAR(10), actual_channel VARCHAR(10), revenue_usd DECIMAL(10,2))
ad_conversions(impression_id BIGINT, event_date DATE, conversions INT)
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.
Tables
ad_revenue(impression_id BIGINT, event_date DATE, advertiser_id INT, model_version VARCHAR(10), predicted_channel VARCHAR(10), actual_channel VARCHAR(10), revenue_usd DECIMAL(10,2))
ad_conversions(impression_id BIGINT, event_date DATE, conversions INT)
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.