Cost per Acquisition of Every Campaign

Medium
282.

Cost per Acquisition of Every Campaign

MediumJoinsAggregation

MySQL

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

ColumnType
campaign_idint
campaign_namevarchar

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key (together): campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Table: Conversion

ColumnType
conversion_idint
campaign_idint
converted_ondate
revenueint

Primary key: conversion_id.

One row per attributed order; campaign_id is always in Campaign.

Example

The rows must come back in the order shown.

Campaign

campaign_idcampaign_name
1Diwali Mega Sale
2Monsoon Footwear
3Back to College
4Wedding Season Gold

DailySpend

campaign_idspend_datespend
12024-10-2520000
12024-10-2625000
22024-07-018000
32024-06-155000
32024-06-165000

Conversion

conversion_idcampaign_idconverted_onrevenue
112024-10-252999
212024-10-261499
312024-10-26899
432024-06-16499
542024-11-2015999

Output

campaign_idcampaign_nametotal_spendconversionscpa
1Diwali Mega Sale45000315000
2Monsoon Footwear80000NULL
3Back to College10000110000
4Wedding Season Gold010
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
campaign_idint
campaign_namevarchar

Primary key: campaign_id.

One row per campaign.

Table: DailySpend

ColumnType
campaign_idint
spend_datedate
spendint

Primary key: campaign_id, spend_date.

One row per campaign per day it spent, in rupees.

Table: Conversion

ColumnType
conversion_idint
campaign_idint
converted_ondate
revenueint

Primary key: conversion_id.

One row per attributed order; campaign_id is always in Campaign.

Examples

Example 1

Campaign

campaign_idcampaign_name
1Diwali Mega Sale
2Monsoon Footwear
3Back to College
4Wedding Season Gold

DailySpend

campaign_idspend_datespend
12024-10-2520000
12024-10-2625000
22024-07-018000
32024-06-155000
32024-06-165000

Conversion

conversion_idcampaign_idconverted_onrevenue
112024-10-252999
212024-10-261499
312024-10-26899
432024-06-16499
542024-11-2015999

Output

campaign_idcampaign_nametotal_spendconversionscpa
1Diwali Mega Sale45000315000
2Monsoon Footwear80000NULL
3Back to College10000110000
4Wedding Season Gold010

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_id

Another 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_id

Another 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 →