Codecademy export

test.sqlite

/*
Here's the first-touch query, in case you need it
*/

WITH first_touch AS (
    SELECT user_id,
        MIN(timestamp) as first_touch_at
    FROM page_visits
    GROUP BY user_id)

SELECT ft.user_id,
    ft.first_touch_at,
    pv.utm_source,
		pv.utm_campaign
FROM first_touch ft
JOIN page_visits pv
    ON ft.user_id = pv.user_id
    AND ft.first_touch_at = pv.timestamp ;

SELECT COUNT(DISTINCT utm_campaign) FROM page_visits;
SELECT COUNT(DISTINCT utm_source) FROM page_visits;

SELECT DISTINCT utm_campaign, utm_source FROM page_visits
GROUP BY 1;

SELECT DISTINCT page_name FROM page_visits;

SELECT 
(pv.utm_campaign) AS campaign, 
COUNT(ft.first_touch_at) AS first_touches 
FROM page_visits pv JOIN first_touch ft
ON pv.user_id = ft.user_id 
AND pv.timestamp = ft.first_touch_at 
GROUP BY 1;

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

SELECT pv.utm_campaign, COUNT(lt.last_touch_at) 
FROM page_visits pv JOIN last_touch lt 
ON pv.user_id = lt.user_id AND pv.timestamp = lt.last_touch_at
GROUP BY 1;

SELECT COUNT(DISTINCT user_id) AS num_purchase FROM page_visits
WHERE page_name = '4 - purchase';

SELECT pv.utm_campaign, COUNT(lt.last_touch_at) 
FROM page_visits pv JOIN last_touch lt 
WHERE page_name = '4 - purchase'
ON pv.user_id = lt.user_id AND pv.timestamp = lt.last_touch_at
GROUP BY 1;