Codecademy export

project.sqlite

--Quiz Funnel

SELECT * 
 FROM survey
 LIMIT 10;
 
--Number of Responses
 
 SELECT question,
 COUNT (DISTINCT user_id)
 FROM survey
 GROUP BY 1;
 --1. 500 100%
 --2. 475 95%
 --3. 380 80%
 --4. 361 95%
 --5. 270 75%
 
 --Home Try-On Funnel
 
 SELECT *
 FROM quiz
 LIMIT 5;
 --Columns: user_id, style, fit, shape, color
 SELECT *
 FROM home_try_on
 LIMIT 5;
 --Columns: user_id, number_of_pairs, address
 SELECT *
 FROM purchase
 LIMIT 5;
 --Columns: user_id, product_id, style, model_name, color, price
 
 --Create new funnel table

 SELECT DISTINCT q.user_id, 
	CASE
		WHEN h.user_id IS NOT NULL THEN 'True'
		WHEN h.user_id IS NULL THEN 'False'
	END AS 'is_home_try_on',
  CASE
    WHEN h.number_of_pairs IS NULL THEN 'None'
    ELSE h.number_of_pairs 
  END AS 'num_pairs',
	CASE
		WHEN p.user_id IS NOT NULL THEN 'True'
		WHEN p.user_id IS NULL THEN 'False'
  END AS 'is_purchase'
 FROM quiz AS 'q'
 LEFT JOIN home_try_on AS 'h'
   ON q.user_id = h.user_id
 LEFT JOIN purchase AS 'p'
   ON h.user_id = p.user_id
LIMIT 10;

--Analysis with tables

WITH purchase_funnel AS ( 
SELECT DISTINCT q.user_id, 
CASE
  WHEN h.user_id IS NOT NULL THEN 'True'
  WHEN h.user_id IS NULL THEN 'False'
END AS 'is_home_try_on',
CASE
  WHEN h.number_of_pairs IS NULL THEN 'None'
  ELSE h.number_of_pairs 
END AS 'num_pairs',
CASE
  WHEN p.user_id IS NOT NULL THEN 'True'
  WHEN p.user_id IS NULL THEN 'False'
END AS 'is_purchase'
FROM quiz AS 'q'
LEFT JOIN home_try_on AS 'h'
 ON q.user_id = h.user_id
LEFT JOIN purchase AS 'p'
 ON h.user_id = p.user_id)
SELECT DISTINCT COUNT(user_id) AS 'total_quizzed',
SUM(CASE
    WHEN is_home_try_on = 'True' THEN 1
    WHEN is_home_try_on = 'False' THEN 0
END) AS 'total_quiz_to_try_on',
SUM(CASE
    WHEN is_purchase = 'True' THEN 1
    WHEN is_purchase = 'False' THEN 0
END) AS 'total_try_on_to_purchase',
1.0 * SUM(CASE
          WHEN is_home_try_on = 'True' THEN 1
          WHEN is_home_try_on = 'False' THEN 0
      END) / COUNT(user_id) AS 'quiz_to_try_on_rate',
1.0 * SUM(CASE
          WHEN is_purchase = 'True' THEN 1
          WHEN is_purchase = 'False' THEN 0
      END) / SUM(CASE
          WHEN is_home_try_on = 'True' THEN 1
          WHEN is_home_try_on = 'False' THEN 0
      END) AS 'try_on_to_purchase_rate'
FROM purchase_funnel
LIMIT 10;

--Highest/Lowest Prices  

 SELECT MAX(price) AS 'highest_price', 
 MIN(price) AS 'lowest_price'
 FROM purchase;
--Average Price

SELECT ROUND(AVG(price), 2) AS 'average_price'
FROM purchase;

--Most Purchased Model

SELECT model_name, COUNT(model_name) AS 'num_purchased'
FROM purchase
GROUP BY model_name
ORDER BY 2 DESC;

--A/B Test Info

SELECT 
COUNT(DISTINCT CASE
        WHEN number_of_pairs = '3 pairs' THEN user_id
        ELSE NULL
    END) AS '3_tried_on_A',
COUNT(DISTINCT CASE
      WHEN number_of_pairs = '5 pairs' THEN user_id
      ELSE NULL
    END) AS '5_tried_on_B'	   
FROM home_try_on
LIMIT 10;

--rate of purchase A/B

WITH purchase_funnel AS (
 SELECT DISTINCT q.user_id, 
	CASE
		WHEN h.user_id IS NOT NULL THEN 'True'
		WHEN h.user_id IS NULL THEN 'False'
	END AS 'is_home_try_on',
  CASE
    WHEN h.number_of_pairs IS NULL THEN 'None'
    ELSE h.number_of_pairs 
  END AS 'num_pairs',
	CASE
		WHEN p.user_id IS NOT NULL THEN 'True'
		WHEN p.user_id IS NULL THEN 'False'
  END AS 'is_purchase'
 FROM quiz AS 'q'
 LEFT JOIN home_try_on AS 'h'
   ON q.user_id = h.user_id
 LEFT JOIN purchase AS 'p'
   ON h.user_id = p.user_id)
SELECT COUNT(DISTINCT CASE 
   WHEN num_pairs = '3 pairs'
   AND is_purchase = 'True' THEN user_id
   ELSE NULL
  END) AS 'A Purchased', 
COUNT(DISTINCT CASE 
   WHEN num_pairs = '5 pairs'
   AND is_purchase = 'True' THEN user_id
   ELSE NULL
  END) AS 'B Purchased'
FROM purchase_funnel
LIMIT 10;

--Top 5 Colors Purchased

SELECT COUNT(color) AS 'top_color_purchased', 
color 
FROM purchase
GROUP BY color
ORDER BY 1 DESC
LIMIT 5;

--Top 5 Colors on Quiz

SELECT COUNT(color) AS 'top_color_quiz', 
color 
FROM quiz
GROUP BY color
ORDER BY 1 DESC
LIMIT 5;