Compute ads revenue by geography in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You have ad delivery logs for a shop-ads system.
## Tables
### `ad_impressions`
- `impression_id` STRING (PK)
- `ts` TIMESTAMP (UTC)
- `user_id` STRING
- `shop_id` STRING
- `country` STRING
- `region` STRING
- `ad_slot` STRING
### `ad_clicks`
- `click_id` STRING (PK)
- `impression_id` STRING (FK → `ad_impressions.impression_id`)
- `ts` TIMESTAMP (UTC)
### `ad_billing`
- `impression_id` STRING (FK → `ad_impressions.impression_id`)
- `bill_ts` TIMESTAMP (UTC)
- `billing_model` STRING
- values: `'CPC'`, `'CPM'`
- `revenue_usd` NUMERIC
- For CPC, revenue is recorded on the clicked impression; for CPM, revenue is recorded per impression.
Assume timestamps are UTC and you should use `bill_ts` as the source of truth for revenue timing.
## Task
Write a SQL query to compute **ads revenue by geography** for the **last 30 days**:
- Group by `country` and `region`.
- Output columns:
- `country`, `region`
- `total_revenue_usd`
- `impressions`
- `clicks`
- `ctr` = clicks / impressions
- `revenue_per_1k_impressions` = 1000 * total_revenue_usd / impressions
- Return only geographies with at least **100,000 impressions** in the period.
- Order by `total_revenue_usd` descending.
Overview: This question evaluates SQL data manipulation and revenue attribution skills, testing the ability to aggregate and compute business metrics such as total revenue, impressions, clicks, CTR, and revenue per 1k impressions from ad impressions, clicks, and billing records.
You are given ad delivery logs for a shop-ads system.
Compute ads revenue by geography for the 30-day window from 2025-05-02 00:00:00 (inclusive) to 2025-06-01 00:00:00 (exclusive).
Requirements:
- Use `ad_billing.bill_ts` as the source of truth for whether revenue (and the associated impression/click) is in the window.
- Group by `country` and `region` (from `ad_impressions`).
- Output columns:
- `country`, `region`
- `total_revenue_usd`
- `impressions`
- `clicks`
- `ctr` = clicks / impressions
- `revenue_per_1k_impressions` = 1000 * total_revenue_usd / impressions
- Return only geographies with at least 100000 impressions in the window.
- Order by `total_revenue_usd` descending.
Notes:
- `ad_billing.revenue_usd` is already recorded at the impression level: for CPC it is only recorded on clicked impressions, and for CPM it is recorded per impression.
- Use safe division for rate calculations (avoid divide-by-zero).
Tables
ad_impressions(impression_id VARCHAR(50), ts TIMESTAMP, user_id VARCHAR(50), shop_id VARCHAR(50), country VARCHAR(2), region VARCHAR(20), ad_slot VARCHAR(30))
ad_clicks(click_id VARCHAR(50), impression_id VARCHAR(50), ts TIMESTAMP)
ad_billing(impression_id VARCHAR(50), bill_ts TIMESTAMP, billing_model VARCHAR(3), revenue_usd DECIMAL(10,2))
Hints
- Filter the population using ad_billing.bill_ts (not impression ts) for the date window.
- Join billing -> impressions to get geography, and left join clicks to count clicks.
Community answers
Answer by SS
WITH billing_30d AS (
-- Start with the table that has the date filter (bill_ts)
SELECT
impression_id,
revenue_usd
FROM ad_billing
WHERE bill_ts >= CURRENT_TIMESTAMP - INTERVAL '30 days'
),
metrics_joined AS (
SELECT
i.country,
i.region,
i.impression_id,
b.revenue_usd,
CASE WHEN c.click_id IS NOT NULL THEN 1 ELSE 0 END AS is_click
FROM ad_impressions i
LEFT JOIN billing_30d b ON i.impression_id = b.impression_id
LEFT JOIN ad_clicks c ON i.impression_id = c.impression_id
)
SELECT
country,
region,
SUM(COALESCE(revenue_usd, 0)) AS total_revenue_usd,
COUNT(impression_id) AS impressions,
SUM(is_click) AS clicks,
SUM(is_click) * 1.0 / NULLIF(COUNT(impression_id), 0) AS ctr,
1000 * SUM(COALESCE(revenue_usd, 0)) / NULLIF(COUNT(impression_id), 0) AS revenue_per_1k_impressions
FROM metrics_joined
GROUP BY 1, 2
HAVING COUNT(impression_id) >= 100000
ORDER BY 3 DESC;
Answer by edu.usa4ever
SELECT
a.country,
a.region,
SUM(b.revenue_usd) AS total_revenue_usd,
COUNT(a.impression_id) AS impressions,
COUNT(c.click_id) AS clicks,
COUNT(c.click_id) * 1.0 / COUNT(a.impression_id) AS ctr,
1000 * SUM(b.revenue_usd) / COUNT(a.impression_id) AS revenue_per_1k_impressions
FROM
ad_impressions a
LEFT JOIN ad_clicks c ON a.impression_id = c.impression_id
INNER JOIN ad_billing b ON a.impression_id = b.impression_id
WHERE
b.bill_ts >= CURRENT_TIMESTAMP - INTERVAL '30 days'
GROUP BY
a.country,
a.region
HAVING
COUNT(a.impression_id) >= 100000
ORDER BY
total_revenue_usd DESC;
Answer by joe_smith
select i.country
, i.region
, sum(b.revenue_usd) as total_revenue_usd
, count(i.impression_id) as impressions
, count(c.click_id) clicks
, cast(count(c.click_id) as float)/cast(count(i.impression_id) as float) as ctr
, sum(b.revenue_usd)*1000/count(i.impression_id) rev_per_1k_impressions
from ad_impressions i
left join ad_clicks c
on i.impression_id = c.impression_id
left join ad_billing b
on b.impression_id = i.impression_id
where cast(bill_ts as date) between current_date()-30 and current_date()
group by 1, 2
having count(i.impression_id)>=100000
order by total_revenue_usd desc
Answer by Xiaoming
select i.country , i.region , sum(b.revenue_usd) as total_revenue_usd , count(i.impression_id) as impressions , count(c.click_id) clicks , cast(count(c.click_id) as float)/cast(count(i.impression_id) as float) as ctr ,
Answer by zelda_
SELECT
country
,region
,sum(c.revenue_usd) as total_revenue_usd
,count(distinct a.impression_id) as impressions
,count(distinct b.click_id) as clicks
,count(distinct b.click_id) / nullif(count(distinct a.impression_id),0) as ctr
,1000*sum(c.revenue_usd) / nullif(count(distinct a.impression_id),0) as revenue_per_1k_impressions
FROM ad_impressions a
left join ad_clicks b
on a.impression_id = b.impression_id
left join ad_billing c
on a.impression_id = c.impression_id
where a.ts between '2025-05-02' and '2025-05-31'
group by 1,2
order by 3 desc