As I was working through question 6 of the CoolTShirts Marketing Attrition Project, I noticed that the number of last touches on the purchase page changed depending on where I put the WHERE clause and Im not sure why.
When I place it in the last_touch query, there are 161 associated with the purchase page. When I put it in the aggregate table (the join between the last touches query and original page_visits table), there are only 158.
Does anyone know why this is happening? My code is below and I have included a link to the project.
Thanks so much in advance!
last_touch AS (
MAX(timestamp) as last_touch_at, page_name
GROUP BY user_id
(SELECT lt.user_id id,
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
/* SELECT COUNT(DISTINCT user_id)
WHERE page_name = ‘4 - purchase’; */
SELECT COUNT(DISTINCT user_id)
WHERE page_name = ‘4 - purchase’;