SQL for Top Channels by CAC | Dataford Interview Questions

Turing's Product Growth team wants to compare acquisition efficiency across marketing channels during January 2025. Write a PostgreSQL query that identifies the top three eligible channels using customer acquisition cost, or CAC, and conversion rate.

Requirements

Aggregate January 2025 marketing spend and lead activity separately by channel before joining them.
Calculate conversion rate as converted leads divided by total leads, expressed as a percentage.
Calculate CAC as total spend divided by converted leads. Exclude channels with fewer than two leads, no spend, or no conversions.
Rank eligible channels by lowest CAC, then highest conversion rate, and return the top three ranked channels.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Marketing channel identifier
channel_name	VARCHAR(100)	Marketing channel name

marketing_spend

Column	Type	Description
spend_idPK	INT	Spend record identifier
channel_id	INT	Referenced marketing channel
spend_date	DATE	Date on which spend was recorded
amount	NUMERIC(12,2)	Marketing spend amount

turing_leads

Column	Type	Description
lead_idPK	INT	Lead identifier
channel_id	INT	Channel that acquired the lead
signup_date	DATE	Date the lead signed up
converted_at	DATE	Date the lead converted, or null if not converted

Your question is SQL for Top Channels by CAC. 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