Hello!
I was completing the 6th task on the SQL Marketing Attributions Question for CoolTShirts and noticed a slight difference in answers when I used these 2 queries below. I changed the location of the WHERE clause to inside the temporary table to outside the temporary table. The answer had a difference of 1/2 counts with these two different queries. Does anyone know why? Thank you in advance!
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
FROM page_visits
GROUP BY user_id)
SELECT pv.utm_campaign, COUNT(*)
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
**WHERE page_name = '4 - purchase'**
GROUP BY utm_campaign
ORDER BY COUNT(*) DESC;
AND
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
**WHERE page_name = '4 - purchase'**
FROM page_visits
GROUP BY user_id)
SELECT pv.utm_campaign, COUNT(*)
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
WHERE page_name = '4 - purchase'
GROUP BY utm_campaign
ORDER BY COUNT(*) DESC;