Skip to content

Instantly share code, notes, and snippets.

@codecademydev

codecademydev/test.sqlite Secret

Created Feb 25, 2020
Embed
What would you like to do?
Codecademy export
--finding the number of distinct campaigns
SELECT COUNT(DISTINCT utm_campaign) AS c_campaign
FROM page_visits;
--finding the number of distinct sources
SELECT COUNT(DISTINCT utm_source) AS c_source
FROM page_visits;
--finding which source is used for each campaign
WITH relations AS
(SELECT DISTINCT utm_campaign,utm_source
FROM page_visits)
SELECT *
FROM relations;
--finding distinct page_name
SELECT DISTINCT page_name
FROM page_visits;
--finding the number of first touches that each campaign is responsible for
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) as first_touch_at
FROM page_visits
GROUP BY user_id),
ft_attr AS (
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 ft_attr.utm_source,
ft_attr.utm_campaign,
COUNT(*) AS number_of_first_touches
FROM ft_attr
GROUP BY 1, 2
ORDER BY 3 DESC;
--finding the number of last touches that each campaign is responsible for
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at
FROM page_visits
GROUP BY user_id),
lt_attr AS (
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign
FROM last_touch AS lt
JOIN page_visits AS pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
)
SELECT lt_attr.utm_source,
lt_attr.utm_campaign,
COUNT(*) AS number_of_last_touches
FROM lt_attr
GROUP BY 1, 2
ORDER BY 3 DESC;
--finding the number of visitors making purchases
SELECT COUNT(user_id)
FROM page_visits
WHERE page_name = '4 - purchase';
--finding the number of last touches on purchase page that each campaign is responsible for
WITH last_touch AS (
SELECT user_id,
MAX(timestamp) as last_touch_at,
page_name
FROM page_visits
WHERE page_name = '4 - purchase'
GROUP BY user_id),
lt_attr AS (
SELECT lt.user_id,
lt.last_touch_at,
pv.utm_source,
pv.utm_campaign,
pv.page_name
FROM last_touch AS lt
JOIN page_visits AS pv
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
)
SELECT lt_attr.utm_source,
lt_attr.utm_campaign,
COUNT(*) AS number_of_purchases
FROM lt_attr
GROUP BY 1, 2
ORDER BY 3 DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment