Codecademy export

test.sqlite

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