Hi All,
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 (
SELECT user_id,
MAX(timestamp) as last_touch_at, page_name
FROM page_visits
GROUP BY user_id
),
last_touch_aggregate AS
(SELECT lt.user_id id,
lt.last_touch_at ltt,
pv.utm_source source,
pv.utm_campaign campaign,
pv.page_name 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 COUNT(DISTINCT user_id)
FROM last_touch
WHERE page_name = ‘4 - purchase’; */
SELECT COUNT(DISTINCT user_id)
FROM page_visits
WHERE page_name = ‘4 - purchase’;