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.
Quick Answer: 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.