Can you see the error!? Marketing Attribution CoolTShirts Q6 SQL

Get Help SQL
, ,
#1

Hi all,

My query returns a different result (count 358) to that suggested by Codecademy (count 361), but I don’t know why! Can you spot the reason?
Question 6 from: https://www.codecademy.com/paths/analyze-business-data-with-sql-cfb/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-marketing-attribution/projects/analyze-data-sql-attribution-queries

I simply cannot see a reason for the difference!

Many thanks.

CODECADEMY SUGGESTED QUERY:

WITH last_touch AS (
  SELECT page_name, user_id,
         MAX(timestamp) AS last_touch_at
  FROM page_visits
  WHERE page_name = '4 - purchase'
  GROUP BY user_id)

SELECT page_name, count(last_touch_at)
FROM last_touch
GROUP BY page_name;

RETURNS 361 counts

MY QUERIES:
1)

WITH last_touch AS (
  SELECT page_name, user_id,
         MAX(timestamp) AS last_touch_at
  FROM page_visits
  GROUP BY user_id)

SELECT page_name, count(last_touch_at)
FROM last_touch
GROUP BY page_name;

ALTERNATIVELY:
2)

WITH last_touch AS (
  SELECT page_name, user_id,
         MAX(timestamp) AS last_touch_at
  FROM page_visits
  GROUP BY user_id)

SELECT page_name, count(last_touch_at)
FROM last_touch
WHERE page_name = '4 - purchase';

BOTH RETURN 358 counts

#2

Welcome @ngreenngreen. There’s a very useful in-depth discussion of this problem elsewhere. In short, if you have users who make a purchase but then revisit the site without a purchase then the number of ‘purchases’ can get a little confused. You have to be careful with your queries in this case.

Worthwhile reading:

1 Like
#3

@tgrtim thanks! - I will study this. I didn’t locate the other thread using keywords.

1 Like
#4

You’re all good, I just recognise this particular query :slightly_smiling_face: and the answer linked above probably covers everything you need.

1 Like