SQL for Weekly Lead KPIs | Dataford Interview Questions

Stanley Martin Homes marketing analysts need a weekly view of lead performance across acquisition channels used by the Stanley Martin Homes website and CRM. Write a PostgreSQL query for the period from 2025-01-06 through 2025-02-02.

Requirements

Return one row for every marketing channel and calendar week, including channel-week combinations with no leads.
Calculate lead volume, conversion rate as a percentage of leads where converted = TRUE, and average cost per lead as weekly spend divided by lead volume.
Treat missing lead counts as zero and avoid division-by-zero errors. Treat nullable spend as zero when aggregating.
Order results by channel name and week start.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Primary key for a marketing channel
channel_name	VARCHAR(80)	Marketing acquisition channel name

leads

Column	Type	Description
lead_idPK	INT	Primary key for a lead
channel_id	INT	Marketing channel attributed to the lead
created_at	DATE	Date the lead entered the Stanley Martin Homes CRM
converted	BOOLEAN	Whether the lead converted

marketing_spend

Column	Type	Description
spend_idPK	INT	Primary key for a spend record
channel_id	INT	Marketing channel receiving the spend
spend_date	DATE	Date the spend was recorded
amount	DECIMAL(10,2)	Spend amount for the channel and date

Your question is SQL for Weekly Lead KPIs. 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.

Problem