SQL Month-Over-Month Campaign Comparison | Dataford Interview Questions

Your question is SQL Month-Over-Month Campaign Comparison. 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

Targetbase needs a monthly performance view for campaign optimization across its activation channels. Write a PostgreSQL query that compares each active campaign's metrics from January through April 2025.

Requirements

Generate one row per active campaign and calendar month, including months with no activity.
Aggregate impressions, clicks, spend, conversions, and revenue from daily metrics.
Join campaign and channel attributes, then use LAG to return prior-month revenue.
Calculate month-over-month revenue percentage change. Return NULL when prior revenue is zero or unavailable.
Return results in campaign and chronological month order.

Representative Data

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Campaign identifier
campaign_name	VARCHAR(150)	Campaign name
channel_id	INT	References channels.channel_id
objective	VARCHAR(80)	Campaign objective
start_date	DATE	Campaign start date
end_date	DATE	Campaign end date

channels

Column	Type	Description
channel_idPK	INT	Channel identifier
channel_name	VARCHAR(80)	Targetbase activation channel
platform	VARCHAR(60)	Delivery platform

campaign_daily_metrics

Column	Type	Description
metric_datePK	DATE	Metric date
campaign_idPK	INT	References campaigns.campaign_id
impressions	INT	Delivered impressions
clicks	INT	Recorded clicks
spend	NUMERIC(12,2)	Daily spend
conversions	INT	Attributed conversions
revenue	NUMERIC(12,2)	Attributed revenue