Compute ad revenue metrics by geography in SQL

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.