SQL for Campaign Performance | Dataford Interview Questions

Write a SQL query to find the top-performing marketing campaigns based on customer acquisition cost and lifetime value. Return the three campaigns with at least two acquired customers, ranked by lifetime value divided by customer acquisition cost. Use lifetime value as the average non-null customer lifetime value.

Output

One row per qualifying campaign
Columns: campaign_id, campaign_name, total_spend, acquired_customers, customer_acquisition_cost, lifetime_value, ltv_to_cac_ratio
Order by ltv_to_cac_ratio descending, then customer_acquisition_cost ascending, then campaign_id ascending

Schema

campaigns

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

campaign_spend

Column	Type	Description
spend_idPK	INT	Unique spend record identifier
campaign_id	INT	Referenced campaign
spend_date	DATE	Date of the spend
spend_amount	NUMERIC(12,2)	Amount spent on the campaign

customers

Column	Type	Description
customer_idPK	INT	Unique customer identifier
campaign_id	INT	Campaign that acquired the customer
acquired_at	DATE	Customer acquisition date
lifetime_value	NUMERIC(12,2)	Customer lifetime value

Your question is SQL for Campaign Performance. 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