Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MY version on code academy's code
FIRST TOUCH:
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) as first_touch_at
FROM page_visits
GROUP BY user_id),
num_campaign AS (
SELECT DISTINCT pv.utm_campaign, COUNT(ft.user_id) as 'num_campaign'
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
GROUP BY pv.utm_campaign),
num_source AS (
SELECT DISTINCT pv.utm_source, COUNT(ft.user_id) as 'num_source'
FROM first_touch ft
JOIN page_visits pv
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
GROUP BY pv.utm_source)
SELECT *
FROM num_source
CROSS JOIN num_campaign;
LAST TOUCH:
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
FROM page_visits
WHERE page_name = '4 - purchase'
GROUP BY user_id),
num_campaign AS (
SELECT DISTINCT pv.utm_campaign, COUNT(lt.user_id) as 'num_campaign'
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
GROUP BY pv.utm_campaign),
num_source AS (
SELECT DISTINCT pv.utm_source, COUNT(lt.user_id) as 'num_source'
FROM last_touch lt
JOIN page_visits pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
GROUP BY pv.utm_source)
SELECT *
FROM num_source
CROSS JOIN num_campaign;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment