WITH spends AS ( SELECT utm_campaign, SUM(amount) AS total_spend FROM marketing_spends GROUP BY 1 ), total_customers AS ( SELECT utm_campaign, COUNT(*) AS customers_count FROM users WHERE utm_campaign IS NOT NULL AND status = 'customer' GROUP BY 1 ) SELECT s.utm_campaign, total_spend / customers_count AS CAC FROM spends s LEFT JOIN total_customers u ON s.utm_campaign = u.utm_campaign ORDER BY 2 ASC NULLS LAST
I think the
utm_campaign IS NOT NULL filter used here is redundant because we’re doing a
LEFT JOIN from the spends CTE and the marketing_spends table has no NULLs under its utm_campaign column. It would, however, make a difference if we did the join the other way round.