Last Touch for all Campaigns

[kaymarie]

https://github.com/solidH2O/sql-scratch-capstone-turn-in/blob/1c903b3e13648443a187d3380a22f0682bc64246/Codeacademy%20SQL%20From%20Scratch%20CoolTShirts%20Capstone/CoolTShirts%20SQL%20Capstone%20sql%20file%20prep%20version.sql#L72-L84

Again I see how you were giving an example of a specific user's last touch on the website, however, the question directly asked for how many last touches each campaign is responsible for. I should have seen a table showing output of each campaign and the COUNT for last touches that it is responsible for. The correct code is as follows:

WITH last_touch AS (
  SELECT user_id,
         MAX(timestamp) AS last_touch_at
  FROM page_visits
  GROUP BY 1),
lt_attr AS (
  SELECT lt.user_id,
         lt.last_touch_at,
         pv.utm_source,
         pv.utm_campaign,
         pv.page_name
  FROM last_touch lt
  JOIN page_visits pv
    ON lt.user_id = pv.user_id
    AND lt.last_touch_at = pv.timestamp
)
SELECT lt_attr.utm_source,
       lt_attr.utm_campaign,
       COUNT(*)
FROM lt_attr
GROUP BY 1, 2
ORDER BY 3 DESC;