Top CTR Campaigns by Day | Dataford Interview Questions

Your question is Top CTR Campaigns by Day. Start with the requirements and the two tables on the right.

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

Problem

PulsePoint needs a daily view of its strongest advertising campaigns. Assume the query runs on 2026-08-29, so the past week is 2026-08-23 through 2026-08-29. Write a PostgreSQL query that returns the top three campaigns by click-through rate for each day with valid campaign metrics.

Requirements

Join daily campaign metrics to campaigns to include the campaign name.
Filter metrics to the inclusive seven-day period ending on CURRENT_DATE, and exclude rows with zero impressions or NULL clicks.
Calculate CTR as clicks divided by impressions, expressed as a percentage rounded to two decimal places.
Rank campaigns independently for each day, returning at most three per day. Break CTR ties with the lower campaign_id.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	PulsePoint campaign name

daily_campaign_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric row identifier
campaign_id	INT	Campaign associated with the metrics
metric_date	DATE	Date on which metrics were recorded
clicks	INT	Number of ad clicks
impressions	INT	Number of ad impressions