Channel Performance by Time in SQL | Dataford Interview Questions

HelloFresh marketing teams need a monthly view of campaign performance across acquisition channels. Write a PostgreSQL query that combines campaign metadata with daily campaign metrics.

Requirements

Aggregate impressions, clicks, conversions, spend, and revenue by calendar month and channel for Q1 2025.
Replace a missing channel with Unknown.
Calculate click-through rate, conversion rate, and return on ad spend. Avoid division-by-zero errors and preserve NULL when a ratio cannot be calculated.
Order the results chronologically, then alphabetically by channel.

Schema

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	HelloFresh campaign name
channel	VARCHAR(50)	Marketing acquisition channel

campaign_metrics

Column	Type	Description
metric_idPK	INT	Unique daily metric identifier
campaign_id	INT	References campaigns.campaign_id
metric_date	DATE	Date represented by the metrics
impressions	INT	Number of delivered impressions
clicks	INT	Number of campaign clicks
conversions	INT	Number of attributed conversions
spend	DECIMAL(10,2)	Advertising spend
revenue	DECIMAL(10,2)	Attributed revenue

Your question is Channel Performance by Time in SQL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem