SQL With LAG/LEAD | Dataford Interview Questions

Your question is SQL With LAG/LEAD. 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

Blend360 analysts monitor campaign performance across marketing channels. Write a PostgreSQL query that summarizes conversions by channel and day, then compares each result with the previous recorded day for that channel.

Requirements

Join campaign metadata to daily campaign metrics and include only active campaigns during January 2025.
Aggregate conversions by channel and metric_date, treating rows where all conversion values are NULL as zero.
Use LAG to return the previous recorded day's conversions for each channel.
Calculate both the absolute conversion change and percentage change. Return NULL for the first available day or when the previous value is zero.
Sort the results by channel and metric date.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Blend360 campaign name
channel	VARCHAR(50)	Marketing channel used by the campaign
is_active	BOOLEAN	Whether the campaign is currently active

daily_campaign_metrics

Column	Type	Description
metric_idPK	INT	Unique metric record identifier
campaign_id	INT	References campaigns.campaign_id
metric_date	DATE	Date on which the metric was recorded
conversions	INT	Number of conversions recorded for the campaign