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.

Hello,

I have noticed that there are a lot of places in which people have used subquery within select/from/where or some other clauses. Can you please share some scenrios & guidelines around when to use a sub-query in which clause.

Kind regards, Tushar

Is there a way of doing view-through attribution (when the person saw but not clicked on an ad)?