I’m in lesson 110. CPA Cost per acquisition
In this lesson appears the following query:
WITH spends AS (
SELECT
utm_source,
SUM(amount) AS total_spend
FROM marketing_spends
GROUP BY 1
), total_users AS (
SELECT
utm_source,
COUNT(*) AS users_count
FROM users
WHERE
utm_source IS NOT NULL
GROUP BY 1
)
SELECT
s.utm_source,
total_spend / users_count AS CPA
FROM spends s
INNER JOIN total_users u
ON s.utm_source = u.utm_source
However, I tried to do the exercise by my own using the following query:
SELECT ms.utm_source, SUM(amount), COUNT(DISTINCT(u.id))
FROM marketing_spends ms
LEFT JOIN users u
ON u.utm_source = ms.utm_source
GROUP BY 1
But I get different results for the amount spent, I don’t understand why it is, the users count is correct.