Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created Oct 16, 2020
Embed
What would you like to do?
Codecademy export
/*
Here's the first-touch query, in case you need it
*/
/* 1. Number of campaigns*/
SELECT distinct(utm_campaign)
from page_visits;
/* number of distinct sourcees */
SELECT distinct(utm_source)
from page_visits;
/* how they are related */
select distinct(utm_campaign), (utm_source)
FROM page_visits;
/* 2. */
select distinct(page_name)
FROM page_visits
where utm_campaign like '%cool-tshirts%';
/* 3. */
WITH first_touch AS (
SELECT user_id,
MIN(timestamp) as first_touch_at
FROM page_visits
GROUP BY utm_campaign)
SELECT (pv.utm_campaign), ft.first_touch_at,
count(pv.utm_campaign) as count
FROM first_touch as 'ft'
JOIN page_visits as 'pv'
ON ft.user_id = pv.user_id
AND ft.first_touch_at = pv.timestamp
Group by 1;
/* 4. */
WITH last_touch AS (
SELECT user_id,
Max(timestamp) as last_touch_at
FROM page_visits
GROUP BY utm_campaign)
SELECT (pv.utm_campaign), lt.last_touch_at,
count(pv.utm_campaign) as count
FROM last_touch as 'lt'
JOIN page_visits as 'pv'
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
Group by 1
;
/* 5. */
SELECT count(page_name) as number_of_purchases
from page_visits
Where page_name like '%purchase%';
/* 6. */
WITH last_touch AS (
SELECT user_id,
Max(timestamp) as last_touch_at
FROM page_visits
where page_name = '4 - purchase'
GROUP BY utm_campaign)
SELECT (pv.utm_campaign), lt.last_touch_at,
count(pv.utm_campaign) as count
FROM last_touch as 'lt'
JOIN page_visits as 'pv'
ON lt.user_id = pv.user_id
AND lt.last_touch_at = pv.timestamp
Group by 1;
/* 7. */
With campaign as (
SELECT utm_campaign, count(user_id) as users
from page_visits),
campaign_2 as (
SELECT utm_campaign, count(user_id) as users
from page_visits
where utm_campaign like '%cool_tshirts%'
Group By 1)
SELECT *
FROM campaign
UNION
SELECT *
FROM campaign_2
Order by users desc
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment