Skip to content

Instantly share code, notes, and snippets.

@codecademydev codecademydev/test.sqlite Secret

Created May 20, 2020
Embed
What would you like to do?
Codecademy export
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;
SELECT DISTINCT page_name
FROM page_visits;
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,
COUNT(utm_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 utm_campaign
ORDER BY 5 DESC;
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
FROM page_visits
GROUP BY user_id)
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign,
COUNT(utm_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 utm_campaign
ORDER BY 5 DESC;
SELECT COUNT(DISTINCT user_id)
FROM page_visits
WHERE page_name = '4 - purchase';
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)
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign,
COUNT(utm_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 utm_campaign
ORDER BY 5 DESC;
@ElenaKosourova

This comment has been minimized.

Copy link

ElenaKosourova commented May 20, 2020

Слайд1
Слайд2
Слайд3
Слайд4
Слайд5
Слайд6
Слайд7
Слайд8
Слайд9

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.