CYBER WEEK 50% SALE 📈 🚀
FORUM SQL Habit 116. Solution improvement

116. Solution improvement

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.

REPLIES

Hi @tangjm 👋

Brilliant point, indeed we don’t need a filter when we aggregate by a field. 👏

I’ve adjusted the proposed solution in the exercise, thank you very much for the suggestion! ❤

WRITE A REPLY

“no fuss, just things you actually need”

Start learning with SQL Habit today

Master Data Analysis with SQL through the story of how a startup succeeded through data.