📢 BLACK FRIDAY 50% SALE 📢
FORUM SQL Habit Leeson 109 calculating CPA

Leeson 109 calculating CPA

In the following code from Lesson 109.. how come we dont need to group by utm_campaign at the end when we are calculating CPA? is it because we joined the users to the row by utm_campaign?

WITH spend_per_campaign AS (
  SELECT
    utm_campaign,
    SUM(amount) AS total_spend
  FROM marketing_spends
  GROUP BY 1  
), users_per_campaign AS (
  SELECT
    utm_campaign,
    COUNT(*) AS users_count
  FROM users
  WHERE 
    utm_campaign IS NOT NULL
  GROUP BY 1
)

SELECT
  s.utm_campaign,
  users_count,
  total_spend / users_count AS CPA
FROM spend_per_campaign s
INNER JOIN users_per_campaign u
  ON s.utm_campaign= u.utm_campaign

REPLIES

We don’t need to group it again (technically we can) because both our subqueries contain unique values of utm_campaign-s (since we grouped by utm_campaign in each of them).

After the INNER JOIN, there will be no duplications in the result set, so we can save ourselves an extra GROUP BY 💡

WRITE A REPLY