Codecademy export

test.sqlite

/*
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
;