🎄  CHRISTMAS 40% SALE
GET THE OFFER
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