Compute ads revenue by geography in SQL

Q: Compute ads revenue by geography in SQL

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.

Q: How do I approach Data Manipulation (SQL/Python) interview questions?

Data Manipulation (SQL/Python) questions require understanding of core concepts and practice. PracHub provides solutions with explanations to help you master data manipulation (sql/python) interviews.

Question

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 in SQL

Overview

Tables

ad_impressions

ad_clicks

ad_billing

Task

Comments (0)

`ad_impressions`

`ad_clicks`

`ad_billing`