Warby Parker SQL Analysis Assignment

What’s up, I created a power point presentation where I analyized the data from Warby Parker. I create the presentation in such a way that it could be used to create a business plan such as what type of glasses to focus on, in what colors, for each gender, etc. Let me know what you think and if you see anything I should change in my code I’d love some feedback!

Can’t upload my ppt, because I’m a new user… so… I guess that’ll be posted later?

Thanks, Scott

-- To look at whether 3 pair sold better than 5.

SELECT COUNT(DISTINCT quiz.user_id) AS 'Number of Customers', 
home_try_on.number_of_pairs AS 'Number of Glasses Sent',
SUM(purchase.user_id IS NOT NULL) AS 'Made a Purchase'
FROM quiz
	LEFT JOIN home_try_on 
  	ON quiz.user_id = home_try_on.user_id
  LEFT JOIN purchase
  	ON purchase.user_id = quiz.user_id
  GROUP BY 2
  HAVING number_of_pairs IS NOT NULL;

-- Looking at what models sell best by gender

SELECT 
	CASE 
  	WHEN style LIKE 'Men%' THEN "Men's"
    WHEN style LIKE 'Women%' THEN "Women's"
	ELSE 'Unknown'
END AS "Men's or Women's", 
	model_name AS 'Model', 
	COUNT(2) AS 'Number Sold'
FROM purchase
	GROUP BY 2
	ORDER BY 1, 3 DESC;

-- Looking at what colors sell best by gender

SELECT 
	CASE 
  	WHEN style LIKE 'Men%' THEN "Men's"
    WHEN style LIKE 'Women%' THEN "Women's"
	ELSE 'Unknown'
END AS "Men's or Women's",
	color AS 'Color',
  COUNT(color) AS 'Number Sold'
FROM purchase
	GROUP BY 2
  ORDER BY 1, 3 DESC;

-- Finding the most popular style and color combo for men and women
  
SELECT 	
  CASE 
  	WHEN style LIKE 'Men%' THEN "Men's"
    WHEN style LIKE 'Women%' THEN "Women's"
	ELSE 'Unknown'
END AS "Men's or Women's",
	model_name AS 'Model',
	color AS 'Color',
COUNT(color) AS 'Number Sold'
FROM purchase
	GROUP BY 2, 3
	ORDER BY 4 DESC
  LIMIT 2;
  
-- Finding the popularity of each style and color combo

  SELECT 	
  CASE 
  	WHEN style LIKE 'Men%' THEN "Men's"
    WHEN style LIKE 'Women%' THEN "Women's"
	ELSE 'Unknown'
END AS "Men's or Women's",
	model_name AS 'Model',
	color AS 'Color',
COUNT(color) AS 'Number Sold'
FROM purchase
	GROUP BY 2, 3
	ORDER BY 4 DESC;

-- I used this code to look at all the questions in the survey, their responses, and how many times each response was given. 

SELECT question,
response,
COUNT(*) AS 'Number of Users Who Gave Each Answer'
FROM survey 
GROUP BY 1, 2
ORDER BY 1, 3 DESC;