Ranking Campaigns by Revenue | Dataford Interview Questions

Your question is Ranking Campaigns by Revenue. 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

Corsair's marketing team wants to compare campaign revenue across regions for campaigns active during the first half of 2025. Write a PostgreSQL query that ranks every campaign within its region, including campaigns with no qualifying revenue.

Requirements

Aggregate revenue from revenue_events for dates from 2025-01-01 through 2025-06-30.
Preserve campaigns with no matching revenue and report their revenue as 0.
Rank campaigns within each region by revenue descending using RANK(). Ties must receive the same rank.
Return results ordered by region, rank, and campaign ID.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
region	VARCHAR(50)	Target geographic region

revenue_events

Column	Type	Description
event_idPK	INT	Unique revenue event identifier
campaign_id	INT	Logical reference to a campaign; source data may contain unmatched IDs
event_date	DATE	Date used for revenue attribution
revenue	NUMERIC(12,2)	Revenue attributed to the event