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.
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