Rank Campaigns by ROI Per Region | Dataford Interview Questions

Your question is Rank Campaigns by ROI Per Region. 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

Netflix marketing analytics needs to compare campaign efficiency across geographic regions. Write a PostgreSQL query that aggregates campaign performance for Q1 2025 and ranks campaigns by return on investment within each region.

Requirements

Join campaign metadata with daily performance metrics.
Calculate total spend, total attributed revenue, and ROI as (revenue - spend) / spend.
Rank campaigns within each region using RANK(), with the highest ROI receiving rank 1.
Exclude campaigns with no positive spend and sort the result by region, ROI rank, and campaign ID.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Netflix campaign name
region	VARCHAR(20)	Target market region

campaign_daily_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric row identifier
campaign_id	INT	Campaign associated with the metric row
metric_date	DATE	Date of measured campaign activity
spend	NUMERIC(12,2)	Daily marketing spend
attributed_revenue	NUMERIC(12,2)	Revenue attributed to the campaign