CTR by Country Query | Dataford Interview Questions

Write a query to find the click-through rate (CTR) for Moloco campaigns grouped by country, filtering out campaigns with fewer than 1,000 impressions.

CTR is clicks divided by impressions, expressed as a percentage. Treat NULL clicks as zero.

Output

One row per country, including a NULL country value if applicable.
Columns: country, total_impressions, total_clicks, and ctr_percent.
Include only countries containing campaigns with at least 1,000 total impressions. Sort alphabetically by country, with NULL last.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique Moloco campaign identifier
campaign_name	VARCHAR(100)	Campaign name
country	VARCHAR(2)	ISO country code associated with the campaign

campaign_metrics

Column	Type	Description
metric_idPK	INT	Unique metric record identifier
campaign_id	INT	Campaign associated with the metric record
metric_date	DATE	Date covered by the metric record
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks

Your question is CTR by Country Query. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem