Codecademy export

project.sqlite

 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 h.user_id = p.user_id
 LIMIT 10;

 WITH funnels as 
 (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 h.user_id = p.user_id)
 SELECT COUNT(*) AS 'num_quiz', SUM(is_home_try_on) AS 'num_home_try_on',  SUM(is_purchase) AS 'num_purchase',
1.0 * SUM(is_home_try_on) / COUNT(user_id) AS 'quiz_to_home_try_on',
1.0 * SUM(is_purchase) / SUM(is_home_try_on) AS 'home_try_on_to_purchase'
FROM funnels;

 WITH funnels as 
 (SELECT DISTINCT q.user_id, 
 h.user_id IS NOT NULL AS 'is_home_try_on',
 h.number_of_pairs AS 'num_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 h.user_id = p.user_id)
 SELECT COUNT(*) AS 'num_quiz', SUM(is_home_try_on) AS 'num_home_try_on',  SUM(is_purchase) AS 'num_purchase',
1.0 * SUM(is_home_try_on) / COUNT(user_id) AS 'quiz_to_home_try_on',
1.0 * SUM(is_purchase) / SUM(is_home_try_on) AS 'home_try_on_to_purchase'
FROM funnels
WHERE num_pairs LIKE '3%';

WITH funnels as 
 (SELECT DISTINCT q.user_id, 
 h.user_id IS NOT NULL AS 'is_home_try_on',
 h.number_of_pairs AS 'num_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 h.user_id = p.user_id)
 SELECT COUNT(*) AS 'num_quiz', SUM(is_home_try_on) AS 'num_home_try_on',  SUM(is_purchase) AS 'num_purchase',
1.0 * SUM(is_home_try_on) / COUNT(user_id) AS 'quiz_to_home_try_on',
1.0 * SUM(is_purchase) / SUM(is_home_try_on) AS 'home_try_on_to_purchase'
FROM funnels
WHERE num_pairs LIKE '5%';

SELECT DISTINCT color, COUNT(*) F
FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT color, COUNT(*)
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT style, COUNT(*)
FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT style, COUNT(*)
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT fit, COUNT(*)
FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT shape, COUNT(*)
FROM quiz
GROUP BY 1
ORDER BY 2 DESC;

SELECT DISTINCT model_name, 
price, COUNT(*)
FROM purchase
GROUP BY 1
ORDER BY 3 DESC;