Top Channels by CAC | Dataford Interview Questions

Your question is Top Channels by CAC. Start with the requirements and the three 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

Asana Spa's marketing team wants to evaluate channel efficiency for the last completed reporting quarter, Q2 2025. Write a PostgreSQL query that identifies the three most cost-efficient acquisition channels based on cost per acquired user.

Requirements

Include campaign spend and users acquired between 2025-04-01 inclusive and 2025-07-01 exclusive.
Calculate user acquisition cost as total campaign spend divided by the count of distinct non-null acquired users per channel.
Return the three channels with the lowest positive acquisition cost, ordered from lowest to highest. Exclude channels with no qualifying users or no positive spend.

Schema

marketing_channels

Column	Type	Description
channel_idPK	INT	Unique marketing channel identifier
channel_name	VARCHAR(100)	Asana Spa marketing channel name

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
channel_id	INT	References marketing_channels.channel_id
campaign_name	VARCHAR(150)	Campaign name
campaign_date	DATE	Date associated with campaign spend
spend	DECIMAL(12,2)	Campaign spend amount

user_acquisitions

Column	Type	Description
acquisition_idPK	INT	Unique acquisition event identifier
campaign_id	INT	References campaigns.campaign_id
user_id	INT	Acquired Asana Spa user identifier
acquired_at	DATE	Date the user was acquired