Alternative solution for Marketing Attribution module

I find this version much easier to understand, it gives you BOTH first and last touch attribution, and it’s shorter(?).
https://www.codecademy.com/paths/data-science/tracks/analyze-data-sql-analyze-real-data/modules/analyze-data-sql-marketing-attribution/lessons/first-last-attribution/exercises/attr-query-iii

with first_touch as(
  select user_id,
  min(timestamp) as first_touch_at,
  utm_source
  from page_visits
  group by user_id
),
last_touch as(
  select user_id,
  max(timestamp) as last_touch_at,
  utm_source
  from page_visits
  group by user_id
)
select * 
from first_touch as ft
join last_touch as lt
  on ft.user_id=lt.user_id;
2 Likes

I agree with you, I am having a really bad time trying to find a reason to use JOIN on the first and last touch querys while having all the data on the same table…

Even if we want to have them in separate tables, it seems way more easy to understand if it is done like this:

SELECT user_id,
  MIN(timestamp) AS 'first_touch_at',
  utm_source,
  utm_campaign
FROM page_visits
GROUP BY user_id;

Is there any other reason to use JOIN as the exercise proposed solution?
Any feedback is very welcome. Thanks.

1 Like