Hello all, this is my first project feedback thread. I’ve not only done the SQL part extensively, but also created a funnel percentage calculator using Python. Look below the SQL code to find it.
PowerPoint presentation:
https://docs.google.com/presentation/d/1RUlCZDcwe4cU_ETzcseOIEM1bM4jcoHhppENRiVdvAw/edit?usp=sharing
The project itself:
-- Ex. 1
SELECT * FROM survey
LIMIT 10;
-- Ex. 2
SELECT question,
COUNT(DISTINCT(user_id)) AS 'no_of_users'
FROM survey
GROUP BY 1
ORDER BY 2 DESC;
-- Ex. 3 below, done in Python.
-- Ex. 4:
SELECT * FROM quiz
LIMIT 5;
SELECT * FROM home_try_on
LIMIT 5;
SELECT * FROM purchase
LIMIT 5;
-- Ex. 5:
SELECT DISTINCT quiz.user_id,
home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
home_try_on.number_of_pairs,
purchase.user_id IS NOT NULL AS 'is_purchase'
FROM quiz
LEFT JOIN home_try_on
ON quiz.user_id = home_try_on.user_id
LEFT JOIN purchase
ON quiz.user_id = purchase.user_id
LIMIT 10;
-- conversions funnel: quiz -> home_try_on and home_try_on -> purchase
WITH funnels AS(
SELECT DISTINCT quiz.user_id AS 'u_id',
home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
home_try_on.number_of_pairs,
purchase.user_id IS NOT NULL AS 'is_purchase'
FROM quiz
LEFT JOIN home_try_on
ON quiz.user_id = home_try_on.user_id
LEFT JOIN purchase
ON quiz.user_id = purchase.user_id
)
SELECT COUNT(u_id) AS 'Step1',
SUM(is_home_try_on) AS 'Step2',
SUM(is_purchase) AS 'Step3'
FROM funnels;
-- by number of pairs
WITH funnels AS(
SELECT DISTINCT(quiz.user_id) AS 'u_id',
home_try_on.user_id IS NOT NULL AS 'is_home_try_on',
home_try_on.number_of_pairs AS 'no_of_pairs',
purchase.user_id IS NOT NULL AS 'is_purchase'
FROM quiz
LEFT JOIN home_try_on
ON quiz.user_id = home_try_on.user_id
LEFT JOIN purchase
ON quiz.user_id = purchase.user_id
)
SELECT no_of_pairs,
SUM(is_purchase) AS 'purchase rate'
FROM funnels
GROUP BY 1
ORDER BY 2 DESC;
-- sales by gender pref:
WITH purchases AS(
SELECT style,
model_name,
user_id IS NOT NULL AS 'is_purchase',
price
FROM purchase
)
SELECT style,
SUM(is_purchase) AS 'no_of_purchases',
price * SUM(is_purchase) AS 'revenue'
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;
-- most popular model:
WITH purchases AS(
SELECT style,
model_name,
user_id IS NOT NULL AS 'is_purchase'
FROM purchase
)
SELECT model_name,
SUM(is_purchase) AS 'no_of_purchases'
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;
--most popular color purchased:
WITH purchases AS(
SELECT product_id,
style,
color,
price,
user_id IS NOT NULL AS 'is_purchase'
FROM purchase
)
SELECT color,
COUNT(is_purchase) AS 'no_of_purchases'
FROM purchases
GROUP BY 1
ORDER BY 2 DESC;
-- revenue by product:
WITH purchases AS(
SELECT product_id,
style,
color,
price,
user_id IS NOT NULL AS 'is_purchase'
FROM purchase
)
SELECT product_id,
style,
color,
price * SUM(is_purchase) AS 'revenue'
FROM purchases
GROUP BY 1
ORDER BY 4 DESC;
-- most common results of quiz:
--style:
SELECT style,
COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;
--fit:
SELECT fit,
COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;
--shape:
SELECT shape,
COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;
--color:
SELECT color,
COUNT(user_id) AS 'no_of_answers' FROM quiz
GROUP BY 1
ORDER BY 2 DESC;
--purchases by price:
SELECT price,
COUNT(user_id) AS 'no_of_purchases'
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;
--revenue by price:
SELECT price,
COUNT(user_id) * price AS 'revenue'
FROM purchase
GROUP BY 1
ORDER BY 2 DESC;
Now the Funnel percentage calculator:
#User inputs:
no_of_i = int(input('Number of steps: '))
input1 = int(input('1st step: '))
#Inputting subsequent inputs and making a list out of all user inputs:
inputs = []
inputs.append(input1)
while True:
subs_i = int(input('Subsequent step: '))
if subs_i <= input1 and (no_of_i -1) > len(inputs):
inputs.append(subs_i)
subs_i = input1
elif subs_i >= input1 or (no_of_i -1) < len(inputs) or subs_i < 0:
print('Invalid value')
else:
break
inputs.append(subs_i)
print("Your funnel values are: " + str(inputs))
#Calculating percentages using items in the list
def percentage(inputs):
x = 0
y = 1
for i in inputs:
prev_i = inputs[y-1]
x += 1
y = x
perc = i / prev_i * 100
print('Step ' + str(x) + ': ' + str(perc))
#Execution
percentage(inputs)
input('Press Enter to terminate the program')
Hope you find it useful! Appreciate any feedback.