Compute ads revenue by geography in SQL

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.

Compute ads revenue by geography (last 30 days)

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).

Table ad_impressions

column	type	constraints
impression_id	VARCHAR(50)	PRIMARY KEY, NOT NULL
ts	TIMESTAMP	NOT NULL
user_id	VARCHAR(50)	NOT NULL
shop_id	VARCHAR(50)	NOT NULL
country	VARCHAR(2)	NOT NULL
region	VARCHAR(20)	NOT NULL
ad_slot	VARCHAR(30)	NOT NULL

Sample data:

ad_slot	country	impression_id	region	shop_id	ts	user_id
search_top	US	imp_001	CA	s1	2025-05-10 12:00:00	u1
search_top	US	imp_002	CA	s2	2025-05-11 09:15:00	u2
browse	US	imp_003	NY	s3	2025-05-20 18:30:00	u3
search_bottom	CA	imp_004	ON	s4	2025-05-25 07:45:00	u4
browse	US	imp_005	CA	s2	2025-05-28 21:05:00	u5
search_top	US	imp_006	CA	s1	2025-04-15 10:00:00	u6

Table ad_clicks

column	type	constraints
click_id	VARCHAR(50)	PRIMARY KEY, NOT NULL
impression_id	VARCHAR(50)	NOT NULL
ts	TIMESTAMP	NOT NULL

Sample data:

click_id	impression_id	ts
clk_100	imp_002	2025-05-11 09:16:00
clk_101	imp_004	2025-05-25 07:46:30
clk_102	imp_006	2025-04-15 10:01:00

Table ad_billing

column	type	constraints
impression_id	VARCHAR(50)	NOT NULL
bill_ts	TIMESTAMP	NOT NULL
billing_model	VARCHAR(3)	NOT NULL
revenue_usd	DECIMAL(10,2)	NOT NULL

Sample data:

bill_ts	billing_model	impression_id	revenue_usd
2025-05-10 12:00:05	CPM	imp_001	0.5
2025-05-11 09:16:10	CPC	imp_002	0.8
2025-05-20 18:30:05	CPM	imp_003	0.4
2025-05-25 07:46:40	CPC	imp_004	1.2
2025-05-28 21:05:10	CPC	imp_005	0
2025-04-15 10:00:10	CPM	imp_006	0.55

Expected output

One row per (country, region) for billed impressions in the window [2025-05-02, 2025-06-01), including revenue and performance metrics. Because the sample data does not contain any geography with at least 100000 impressions in the window, the filtered result set is empty.

Hints

1. Filter the population using ad_billing.bill_ts (not impression ts) for the date window.
2. Join billing -> impressions to get geography, and left join clicks to count clicks.
3. Use COUNT(DISTINCT ...) to avoid double counting and NULLIF to prevent divide-by-zero.