Skip to content

Instantly share code, notes, and snippets.

@codecademydev codecademydev/project.sqlite Secret

Created May 15, 2020
Embed
What would you like to do?
Codecademy export
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;
@ElenaKosourova

This comment has been minimized.

Copy link

ElenaKosourova commented May 15, 2020

Слайд1
Слайд2
Слайд3
Слайд4
Слайд5
Слайд6
Слайд7
Слайд8
Слайд9

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.