Compute ad revenue metrics by geography in SQL
Company: Meta
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You work on a marketplace app that shows shop ads. You are given the following tables.
**Assumptions**
- All timestamps are stored in UTC.
- “Revenue” is the money earned by the platform from an ad click (CPC) and is recorded at click time.
- CTR = clicks / impressions.
- RPM = 1000 * revenue / impressions.
**Tables**
`ad_impressions`
- `impression_id` BIGINT
- `user_id` BIGINT
- `shop_id` BIGINT
- `impression_ts` TIMESTAMP (UTC)
`ad_clicks`
- `click_id` BIGINT
- `impression_id` BIGINT -- foreign key to `ad_impressions.impression_id`
- `click_ts` TIMESTAMP (UTC)
- `revenue_usd` NUMERIC(10,2) -- platform revenue from this click
`user_geo_daily`
- `user_id` BIGINT
- `geo_date` DATE -- in UTC
- `country_code` STRING
**Task**
Write a SQL query to produce **daily ad performance by country** for the last 7 complete UTC days (excluding today). Output one row per (`event_date`, `country_code`) with:
- `event_date` (DATE, UTC)
- `country_code`
- `impressions`
- `clicks`
- `revenue_usd`
- `ctr` (as a decimal)
- `rpm` (as a decimal)
Details:
- Attribute an impression to `event_date = DATE(impression_ts)`.
- Attribute an impression to a country using `user_geo_daily` where `geo_date = event_date`.
- Join clicks to impressions via `impression_id`.
- If an impression has no clicks, it should still count toward impressions with 0 clicks and 0 revenue.
- Avoid divide-by-zero errors for CTR/RPM.
Quick Answer: In the Data Manipulation (SQL/Python) domain, this intermediate-level question evaluates SQL aggregation, join logic between impressions and clicks, date-based UTC attribution, user-geo joins, and null/divide-by-zero-safe computation of derived metrics (CTR, RPM) at the country-day granularity.