Cost per Acquisition of Every Campaign — SQL Joins Problem
Spend arrives from the ad platforms per campaign per day; conversions come from the website's order tracking.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #282
Problem statement
Spend arrives from the ad platforms per campaign per day; conversions come from the website's order tracking. The performance team wants each campaign's cost per acquisition (CPA).
Return every campaign with campaign_id, campaign_name, total_spend (0 when it has no spend rows), conversions (0 when it has none) and cpa = total_spend ÷ conversions rounded to 2 decimals, NULL when there are no conversions. Order by campaign_id.
Tables
Table: Campaign
| Column | Type |
|---|---|
| campaign_id | int |
| campaign_name | varchar |
Primary key: campaign_id.
One row per campaign.
Table: DailySpend
| Column | Type |
|---|---|
| campaign_id | int |
| spend_date | date |
| spend | int |
Primary key: campaign_id, spend_date.
One row per campaign per day it spent, in rupees.
Table: Conversion
| Column | Type |
|---|---|
| conversion_id | int |
| campaign_id | int |
| converted_on | date |
| revenue | int |
Primary key: conversion_id.
One row per attributed order; campaign_id is always in Campaign.
Examples
Example 1
Campaign
| campaign_id | campaign_name |
|---|---|
| 1 | Diwali Mega Sale |
| 2 | Monsoon Footwear |
| 3 | Back to College |
| 4 | Wedding Season Gold |
DailySpend
| campaign_id | spend_date | spend |
|---|---|---|
| 1 | 2024-10-25 | 20000 |
| 1 | 2024-10-26 | 25000 |
| 2 | 2024-07-01 | 8000 |
| 3 | 2024-06-15 | 5000 |
| 3 | 2024-06-16 | 5000 |
Conversion
| conversion_id | campaign_id | converted_on | revenue |
|---|---|---|---|
| 1 | 1 | 2024-10-25 | 2999 |
| 2 | 1 | 2024-10-26 | 1499 |
| 3 | 1 | 2024-10-26 | 899 |
| 4 | 3 | 2024-06-16 | 499 |
| 5 | 4 | 2024-11-20 | 15999 |
Output
| campaign_id | campaign_name | total_spend | conversions | cpa |
|---|---|---|---|---|
| 1 | Diwali Mega Sale | 45000 | 3 | 15000 |
| 2 | Monsoon Footwear | 8000 | 0 | NULL |
| 3 | Back to College | 10000 | 1 | 10000 |
| 4 | Wedding Season Gold | 0 | 1 | 0 |
How to solve Cost per Acquisition of Every Campaign
The trap is the fan-out. Joining Campaign to DailySpend and Conversion together pairs every spend day with every conversion, so a campaign with three spend rows and two conversions shows six rows: SUM(spend) doubles and COUNT(*) triples. Aggregate each detail table to one row per campaign in its own derived table, and only then LEFT JOIN those totals to Campaign — a one-to-one join cannot multiply anything.
LEFT JOINs keep campaigns with no spend or no conversions; COALESCE(…, 0) reports those as 0. The CPA divides by the raw v.conversions, which is NULL when there are none, so the ratio is NULL with no division by zero.
The correlated-subquery alternative computes each total per campaign directly; the UNION ALL alternative stacks spend and conversions as one stream of (amount, count) rows so a single GROUP BY sums both safely. All three read each table once.
Reference solution (MySQL)
SELECT c.campaign_id, c.campaign_name,
COALESCE(s.total_spend, 0) AS total_spend,
COALESCE(v.conversions, 0) AS conversions,
ROUND(COALESCE(s.total_spend, 0) / v.conversions, 2) AS cpa
FROM Campaign c
LEFT JOIN (SELECT campaign_id, SUM(spend) AS total_spend FROM DailySpend GROUP BY campaign_id) s
ON s.campaign_id = c.campaign_id
LEFT JOIN (SELECT campaign_id, COUNT(*) AS conversions FROM Conversion GROUP BY campaign_id) v
ON v.campaign_id = c.campaign_id
ORDER BY c.campaign_idAnother way
SELECT c.campaign_id, c.campaign_name,
(SELECT COALESCE(SUM(spend), 0) FROM DailySpend d WHERE d.campaign_id = c.campaign_id) AS total_spend,
(SELECT COUNT(*) FROM Conversion v WHERE v.campaign_id = c.campaign_id) AS conversions,
ROUND((SELECT COALESCE(SUM(spend), 0) FROM DailySpend d WHERE d.campaign_id = c.campaign_id)
/ NULLIF((SELECT COUNT(*) FROM Conversion v WHERE v.campaign_id = c.campaign_id), 0), 2) AS cpa
FROM Campaign c ORDER BY c.campaign_idAnother way
WITH t AS (
SELECT campaign_id, spend AS amt, 0 AS conv FROM DailySpend
UNION ALL SELECT campaign_id, 0, 1 FROM Conversion
)
SELECT c.campaign_id, c.campaign_name, COALESCE(SUM(t.amt), 0) AS total_spend, COALESCE(SUM(t.conv), 0) AS conversions,
ROUND(COALESCE(SUM(t.amt), 0) / NULLIF(SUM(t.conv), 0), 2) AS cpa
FROM Campaign c LEFT JOIN t ON t.campaign_id = c.campaign_id
GROUP BY c.campaign_id, c.campaign_name ORDER BY c.campaign_id← Sign-ups by UTM Source Read From Landing URLs · Top Two Ads by Conversions in Each Campaign →