I see that you are trying to give an example of a specific user's first touch on the website which is cool, however, the question was looking for how many first touches each campaign was responsible for. In this case I should have seen a table that shows me each campaign (interview-with-cool-tshirts-founder, getting-to-know-cool-tshirts, ten-crazy-cool-tshirt-fact, and cool-tshirts-search) and the count of first touches for each of these. The correct code is as follows:
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) AS first_touch_at
FROM page_visits
GROUP BY 1),
ft_attr AS (
SELECT ft.user_id,
ft.first_touch_at,
pv.utm_campaign
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
)
SELECT ft_attr.utm_campaign,
COUNT(*)
FROM ft_attr
GROUP BY 1
ORDER BY 2 DESC;
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#L9-L12
I see that you are trying to give an example of a specific user's first touch on the website which is cool, however, the question was looking for how many first touches each campaign was responsible for. In this case I should have seen a table that shows me each campaign (interview-with-cool-tshirts-founder, getting-to-know-cool-tshirts, ten-crazy-cool-tshirt-fact, and cool-tshirts-search) and the count of first touches for each of these. The correct code is as follows: