SQL: Subqueries and Window Functions | Dataford Interview Questions

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

Fractal's Crux Intelligence team needs a client-level view of campaign performance. Write a PostgreSQL query that identifies the highest-spending active campaigns for each client during Q1 2025.

Requirements

Aggregate Q1 2025 event spend for active campaigns, treating NULL spend as zero.
Rank campaigns separately for each client using ROW_NUMBER(), ordering by total spend descending and campaign ID ascending for deterministic ties.
Return only the top two campaigns per client, including the client name, campaign name, total spend, and rank.
Exclude campaigns with no qualifying Q1 events.

Schema

clients

Column	Type	Description
client_idPK	INT	Unique client identifier
client_name	VARCHAR(100)	Client display name

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
client_id	INT	Owning client identifier
campaign_name	VARCHAR(150)	Campaign display name
status	VARCHAR(20)	Campaign lifecycle status

campaign_events

Column	Type	Description
event_idPK	INT	Unique event identifier
campaign_id	INT	Campaign associated with the event
event_date	DATE	Event occurrence date
spend	NUMERIC(12,2)	Spend attributed to the event