SQL Top Advertisers Per Month | Dataford Interview Questions

Sovrn wants to identify the advertisers contributing the most spend to its advertising marketplace each month. Write a PostgreSQL query that aggregates advertiser spend by calendar month and ranks advertisers within each month.

Requirements

Restrict the analysis to January and February 2024.
Calculate each advertiser's total spend per month, treating NULL spend amounts as zero.
Use a ranking window function partitioned by month and return advertisers with a rank of 3 or better. Include ties at third place.
Return the month as YYYY-MM, advertiser name, total spend, and rank, ordered chronologically by month and then by rank.

Schema

advertisers

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

advertiser_spend

Column	Type	Description
spend_idPK	INTEGER	Unique spend record identifier
advertiser_id	INTEGER	Advertiser associated with the spend record
spend_date	DATE	Date on which spend was recorded
spend_amount	NUMERIC(12,2)	Amount spent by the advertiser

Your question is SQL Top Advertisers Per Month. 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