SQL Window Functions for Campaign Ranking | Dataford Interview Questions

How would you write a SQL query using window functions to rank campaigns by performance within each advertiser and calculate running totals over time?

Use total conversions as the performance measure, based on daily campaign metrics.

Output

One row per advertiser, campaign, and metric date.
Return advertiser_id, advertiser_name, campaign_id, metric_date, daily_conversions, total_conversions, campaign_rank, and running_conversions.
Rank campaigns within each advertiser by total conversions descending, with ties sharing a rank. Order by advertiser, rank, campaign, and date.

Schema

advertisers

Column	Type	Description
advertiser_idPK	INT	Unique advertiser identifier
advertiser_name	VARCHAR(100)	Advertiser display name

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
advertiser_id	INT	Advertiser that owns the campaign
campaign_name	VARCHAR(120)	Campaign display name

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric record identifier
campaign_id	INT	Campaign associated with the metric
metric_date	DATE	Date of the campaign metric
conversions	INT	Conversions attributed to the campaign on the date

Your question is SQL Window Functions for Campaign Ranking. Start with the requirements and the three 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