SQL Top Campaigns by Revenue | Dataford Interview Questions

Collaborative Real Estate wants to identify its strongest marketing campaigns in Q1 2025. Write a PostgreSQL query that attributes won deal revenue to campaigns through leads and ranks campaigns within each marketing channel.

Requirements

Include every campaign, including campaigns with no won revenue.
Calculate Q1 2025 revenue and the previous-quarter revenue for each campaign.
Rank campaigns within each channel by Q1 revenue, using campaign ID as a deterministic tie-breaker.
Return only the top two campaigns per channel.
Include each campaign's percentage change from the previous quarter, share of channel revenue, and revenue gap to the next-ranked campaign.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(150)	Collaborative Real Estate campaign name
channel	VARCHAR(50)	Marketing acquisition channel
status	VARCHAR(20)	Current campaign status

leads

Column	Type	Description
lead_idPK	INT	Unique lead identifier
campaign_id	INT	Campaign that generated the lead
created_at	DATE	Date the lead was created

deals

Column	Type	Description
deal_idPK	INT	Unique deal identifier
lead_id	INT	Lead associated with the deal
closed_at	DATE	Date the deal closed
revenue	NUMERIC(12,2)	Revenue attributed to the deal
deal_status	VARCHAR(20)	Deal outcome

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