Codecademy export

project.sqlite

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

 SELECT *
 FROM quiz
 LIMIT 5;

 SELECT *
 FROM home_try_on
 LIMIT 5;

 SELECT *
 FROM purchase
 LIMIT 5;

-- Creating a new table for our funnel 
CREATE TABLE funnel AS
SELECT quiz.user_id AS user_id,
  CASE 
    WHEN home_try_on.user_id IS NOT NULL THEN 1
    ELSE 0
    END AS "is_home_try_on",
  home_try_on.number_of_pairs AS number_of_pairs,
  CASE
    WHEN purchase.user_id IS NOT NULL THEN 1
    ELSE 0
    END AS "is_purchase"
FROM quiz
LEFT JOIN home_try_on
  ON home_try_on.user_id = quiz.user_id
LEFT JOIN purchase 
  ON purchase.user_id = home_try_on.user_id;

-- Allows us to calculate overall conversion rates by aggregating across all rows and to compare conversion rates between columns

SELECT  
  COUNT(user_id) AS "took_quiz",
  SUM(is_home_try_on) AS "home_try_on",
  SUM(is_purchase) AS "is_purchase",
  1.0 * SUM(is_home_try_on) / COUNT(user_id) AS "%  who took quiz and then tried on glasses",
  1.0 * SUM(is_purchase) / SUM(is_home_try_on) AS "%  who decided to purchase after trying"
FROM funnel;

-- comparing conversion rates among a/b groups:
SELECT  
  number_of_pairs,
  SUM(is_home_try_on) AS "home_try_on",
  SUM(is_purchase) AS "is_purchase",
  1.0 * SUM(is_purchase) / SUM(is_home_try_on) AS "%  who decided to purchase after trying"
FROM funnel
GROUP BY number_of_pairs
HAVING number_of_pairs IS NOT NULL;