SQL for Channel Metrics | Dataford Interview Questions

Write a SQL query to combine campaign performance data from multiple tables and calculate key marketing metrics by channel.

Use all campaign rows, including channels with no activity. Count approved conversions and handle missing values and zero denominators appropriately.

Output

One row per channel with channel, impressions, clicks, spend, conversions, attributed_revenue, ctr_percent, conversion_rate_percent, cost_per_click, and roas.
Include zero totals where no activity exists and NULL for undefined rate metrics.
Order by channel ascending.

campaigns

campaign_performance

Column	Type	Description
campaign_idPK	INT	Campaign receiving the performance activity
performance_datePK	DATE	Date of the performance record
impressions	INT	Number of ad impressions
clicks	INT	Number of ad clicks
spend	DECIMAL(12,2)	Campaign spend

campaign_conversions

Column	Type	Description
conversion_idPK	INT	Unique conversion identifier
campaign_id	INT	Campaign attributed to the conversion
conversion_status	VARCHAR(20)	Conversion review status
attributed_revenue	DECIMAL(12,2)	Revenue attributed to the conversion

Your question is SQL for Channel Metrics. Start with the requirements and the three 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.