Skip to content

Instantly share code, notes, and snippets.

@codecademydev codecademydev/test.sqlite Secret

Created May 5, 2020
Embed
What would you like to do?
Codecademy export
/*
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.