Top Campaigns by Rolling CTR | Dataford Interview Questions

Amazon DSP reporting needs a ranked view of active advertising campaigns based on click-through rate. Assume the reporting date is 2024-03-30. Write a PostgreSQL query that evaluates the rolling 30-day period from 2024-03-01 through 2024-03-30, inclusive.

Requirements

Join campaign metadata with daily Amazon DSP delivery metrics.
Include only campaigns whose status is ACTIVE and whose delivery date falls within the rolling window.
Aggregate impressions and clicks by campaign, exclude campaigns with fewer than 100 total impressions, and calculate CTR as clicks divided by impressions, expressed as a percentage.
Rank campaigns by CTR descending, use impressions as a descending tie-breaker, and return the top three campaigns.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(150)	Amazon DSP campaign name
status	VARCHAR(20)	Campaign state

campaign_daily_metrics

Column	Type	Description
metric_idPK	INTEGER	Unique daily metric record
campaign_id	INTEGER	References campaigns.campaign_id
metric_date	DATE	Date of delivery
impressions	INTEGER	Delivered impressions
clicks	INTEGER	Attributed clicks

Your question is Top Campaigns by Rolling CTR. 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