SQL Top 3 Campaigns by Conversion | Dataford Interview Questions

Digitas México needs a monthly view of campaign efficiency across paid media campaigns. Write a PostgreSQL query to identify the three campaigns with the highest conversion rate for each month.

Requirements

Join campaigns to campaign_metrics using campaign_id.
Aggregate impressions and conversions by campaign and calendar month.
Calculate conversion rate as total conversions divided by total impressions. Treat null conversions as zero and avoid division-by-zero errors.
Use a window function to return exactly the top three campaigns per month, breaking ties by lower campaign_id.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(150)	Campaign display name
status	VARCHAR(20)	Campaign lifecycle status

campaign_metrics

Column	Type	Description
metric_idPK	INT	Unique metrics record identifier
campaign_id	INT	Campaign associated with the metrics
metric_date	DATE	Date on which metrics were recorded
impressions	INT	Number of impressions
conversions	INT	Number of attributed conversions

Your question is SQL Top 3 Campaigns by Conversion. 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