SQL Skill Share Projects

I finished the SQL Skill Share course, and the last requirement was to share the final three projects onto the Codecademy Forums.

Below is a hyperlink to a publicly shared google drive folder for the PowerPoint Presentations we were required to create for these projects.

PowerPoint Presentations

SQL Skill Share Usage Funnels Project

SELECT *
FROM survey
LIMIT 10;

SELECT question,
    COUNT(DISTINCT user_id)
FROM survey
GROUP BY question; 

SELECT *
FROM quiz
LIMIT 5;

SELECT *
FROM home_try_on
LIMIT 5;

SELECT *
FROM purchase
LIMIT 5;  

SELECT DISTINCT q.user_id,
   h.user_id IS NOT NULL AS 'is_home_try_on',
   h.number_of_pairs,
   p.user_id IS NOT NULL AS 'is_purchase'
FROM quiz q
LEFT JOIN home_try_on h
   ON q.user_id = h.user_id
LEFT JOIN purchase p
   ON p.user_id = q.user_id  
LIMIT 10;

SQL Skill Share Project Churn Rates

SELECT *
FROM subscriptions
LIMIT 100;

SELECT 
MIN(subscription_start),
MAX(subscription_start) 
FROM subscriptions; 

WITH months AS
  (SELECT
     '2017-01-01' AS first_day,
     '2017-01-31' AS last_day
   UNION
   SELECT
     '2017-02-01' AS first_day,
     '2017-02-28' AS last_day
   UNION
   SELECT
     '2017-03-01' AS first_day,
     '2017-03-31' AS last_day  
   ),
cross_join AS
  (SELECT * FROM subscriptions
   CROSS JOIN months
   ),
status AS 
  (SELECT 
   id,
   first_day AS month,
   CASE 
     WHEN (subscription_start < first_day) AND (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 87) THEN 1
     ELSE 0
   END AS is_active_87,
   CASE
     WHEN (subscription_start < first_day) AND (subscription_end > first_day OR subscription_end IS NULL) AND (segment = 30) THEN 1
     ELSE 0
   END AS is_active_30,
   CASE 
     WHEN (subscription_end BETWEEN first_day AND last_day) AND (segment = 87) THEN 1
     ELSE 0
   END AS is_canceled_87,
   CASE
     WHEN (subscription_end BETWEEN first_day AND last_day) AND (segment = 30) THEN 1
     ELSE 0
   END AS is_canceled_30    
FROM cross_join   
   ), 
status_aggregate AS
  (SELECT
   month,
   SUM(is_active_87) AS sum_active_87,
   SUM(is_active_30) AS sum_active_30,
   SUM(is_canceled_87) AS sum_canceled_87,
   SUM(is_canceled_30) AS sum_canceled_30
   FROM status
   GROUP BY month
   ) SELECT month,
   1.0 * sum_canceled_87/sum_active_87 AS churn_rate_87,
   1.0 * sum_canceled_30/sum_active_30 AS churn_rate_30
   FROM status_aggregate;

SQL Skill Share Project Marketing Attribution

SELECT COUNT(DISTINCT utm_campaign)
FROM page_visits;

SELECT COUNT(DISTINCT utm_source)
FROM page_visits;

SELECT DISTINCT utm_campaign, utm_source
FROM page_visits; 

SELECT DISTINCT page_name
FROM page_visits;

WITH first_touch AS (
    SELECT user_id,
        MIN(timestamp) as first_touch_at
    FROM page_visits
    GROUP BY user_id)
SELECT ft.user_id,
    ft.first_touch_at,
    pv.utm_source,
    pv.utm_campaign,
    COUNT(utm_campaign)
FROM first_touch ft
JOIN page_visits pv
    ON ft.user_id = pv.user_id
    AND ft.first_touch_at = pv.timestamp
GROUP BY utm_campaign
ORDER BY 5 DESC;

WITH last_touch AS (
    SELECT user_id,
        MAX(timestamp) as last_touch_at
    FROM page_visits
    WHERE page_name = '4 - purchase'
    GROUP BY user_id)
SELECT lt.user_id,
    lt.last_touch_at,
    pv.utm_source,
    pv.utm_campaign,
    COUNT(utm_campaign)
FROM last_touch lt
JOIN page_visits pv
    ON lt.user_id = pv.user_id
    AND lt.last_touch_at = pv.timestamp
GROUP BY utm_campaign
ORDER BY 5 DESC;

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