Codecademy export

test.sqlite

/* Codecademy Project: Mkt Atttribution - CollTShirts */

/*---------------------------------------------------------*/

/* Get familiar with CoolTShirts */

/* STEP 1:
   How many campaigns and sources does CoolTShirts use?
   Which source is used for each campaign?
*/

/* Query 1 - Number of distinct campaigns*/

SELECT COUNT(DISTINCT utm_campaign) AS 'nb_campaigns'
FROM page_visits;

/* Query 2 - Number of distinct sources */


SELECT COUNT(DISTINCT utm_source) AS 'nb_sources'
FROM page_visits;

/* Query 3 - Tables Relationship */

SELECT DISTINCT utm_campaign, utm_source
FROM page_visits;

/*STEP 2:
What pages are on the CoolTShirts website?
Find the distinct values of the page_name column.
*/

SELECT DISTINCT page_name AS 'pages_in_website'
FROM page_visits;

/* What is the user journey? */

/* STEP 3: 
How many first touches is each campaign responsible for?
*/

WITH first_touch AS (
  SELECT user_id, MIN(timestamp) AS 'first_touch_at'
  FROM page_visits
  GROUP BY user_id
)
SELECT pv.utm_campaign, pv.utm_source, COUNT(ft.first_touch_at) AS 'fsttouch/campaign'
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
ORDER BY 3 DESC;

/* STEP 4: 
How many last touches is each campaign responsible for?
*/

WITH last_touch AS (
  SELECT user_id, MAX(timestamp) AS 'last_touch_at'
  FROM page_visits
  GROUP BY user_id
)
SELECT pv.utm_campaign, pv.utm_source, COUNT(lt.last_touch_at) AS 'lasttouch/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
GROUP BY 1
ORDER BY 3 DESC;

/* STEP 5: 
How many visitors make a purchase?
*/

SELECT COUNT(DISTINCT user_id) AS 'users_purchase'
FROM page_visits
WHERE page_name = '4 - purchase';

/* STEP 6: 
How many last touches on the purchase page is each campaign responsible for?
*/

WITH last_touch AS (
  SELECT user_id, MAX(timestamp) AS 'last_touch_at'
  FROM page_visits
    GROUP BY user_id
)
SELECT pv.utm_campaign, pv.utm_source, COUNT(lt.last_touch_at) AS 'lasttouch/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
WHERE page_name = '4 - purchase'
GROUP BY 1
ORDER BY 3 DESC;