MY version on code academy's code

market_attribution.sql

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;