-
-
Save codecademydev/98bc16be2d5332e36ed9f0b96fc347b9 to your computer and use it in GitHub Desktop.
Codecademy export
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/*--Quiz Funnel | |
SELECT question, | |
COUNT(DISTINCT user_id) | |
FROM survey | |
GROUP BY question; | |
-- Home Try-On Funnel | |
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; | |
WITH q AS( | |
SELECT '1-quiz' AS stage, | |
COUNT(DISTINCT user_id) | |
FROM quiz | |
), | |
h AS( | |
SELECT '2-home-try-on' AS stage, | |
COUNT(DISTINCT user_id) | |
FROM home_try_on | |
), | |
p AS( | |
SELECT '3-purchase' AS stage, | |
COUNT(DISTINCT user_id) | |
FROM purchase | |
) | |
SELECT * | |
FROM q | |
UNION ALL SELECT * | |
FROM h | |
UNION ALL SELECT * | |
FROM p; | |
WITH base_table AS( | |
SELECT DISTINCT q.user_id, | |
h.user_id IS NOT NULL AS 'is_home_try_on', | |
h.number_of_pairs AS 'AB_variant', | |
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 | |
) | |
SELECT AB_variant, | |
SUM(CASE WHEN is_home_try_on = 1 | |
THEN 1 | |
ELSE 0 | |
END) 'home_trial', | |
SUM(CASE WHEN is_purchase = 1 | |
THEN 1 | |
ELSE 0 | |
END) 'purchase' | |
FROM base_table | |
GROUP BY AB_variant | |
HAVING home_trial > 0; | |
SELECT style, | |
COUNT(user_id) | |
FROM quiz | |
GROUP BY 1 | |
ORDER BY 2 DESC; | |
SELECT style, | |
COUNT(user_id) | |
FROM purchase | |
GROUP BY 1 | |
ORDER BY 2 DESC; | |
*/ | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment