SQL Campaign Performance by Channel | Dataford Interview Questions

Your question is SQL Campaign Performance by Channel. 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

Targetbase needs a channel-level performance view for campaigns launched during January 2025. Write a PostgreSQL query that summarizes campaign delivery and business outcomes by marketing channel.

Requirements

Include active campaigns launched from 2025-01-01 through 2025-01-31.
Group results by channel and include the number of distinct campaigns, impressions, clicks, conversions, spend, and revenue.
Calculate click-through rate as clicks divided by impressions, expressed as a percentage, and calculate ROAS as revenue divided by spend. Return zero for either metric when its denominator is zero.
Include active in-range campaigns even when they have no event rows, and sort by total revenue descending, then channel ascending.

Schema

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
status	VARCHAR(20)	Campaign lifecycle status
launched_at	DATE	Campaign launch date

campaign_events

Column	Type	Description
event_idPK	INTEGER	Unique event record identifier
campaign_id	INTEGER	References campaigns.campaign_id
event_type	VARCHAR(30)	Event classification
impressions	INTEGER	Delivered impressions
clicks	INTEGER	Recorded clicks
conversions	INTEGER	Attributed conversions
spend	NUMERIC(12,2)	Media spend
revenue	NUMERIC(12,2)	Attributed revenue