SQL Top 3 Campaigns by Month | Dataford Interview Questions

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

Arby’s Marketing Analytics team wants to identify the strongest campaigns each month. Write a PostgreSQL query that returns the top three campaigns by revenue for every calendar month.

Requirements

Include only revenue events with event_status = 'completed'.
Aggregate revenue by campaign and calendar month using event_date.
Rank campaigns within each month and return no more than the top three, breaking revenue ties with the lowest campaign_id.
Return the month, campaign ID, campaign name, total revenue, and monthly rank in chronological order.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Arby’s campaign name
channel	VARCHAR(30)	Primary marketing channel
status	VARCHAR(20)	Campaign lifecycle status

campaign_revenue

Column	Type	Description
revenue_event_idPK	INTEGER	Unique revenue event identifier
campaign_id	INTEGER	Campaign associated with the revenue event
event_date	DATE	Date the revenue was attributed
revenue	NUMERIC(12,2)	Revenue attributed to the event
event_status	VARCHAR(20)	Revenue event processing status