Coffee Sales Project with SQL

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

Data Analyst Junior avec une solide expérience en marketing numérique et des compétences approfondies dans l’utilisation de Microsoft Excel, SQL, Tableau Software et Python.

Coffee Sales Project with SQL

Compétences & Outils

Description du projet

Utiliser toute la panoplie de fonctions que nous offre SQL pour récupérer, modifier, extraire, et trier les données :

WHERE CLAUSE + AND & OR
IN, NOT IN, IS NULL, BETWEEN,
ORDER BY, DISTINCT
EXTRACT
UPPER, LOWER, LENGTH
CONCATENATION, BOOLEAN, EXPRESSIONS AND WILDCARDS
SUBSTRING, POSITION, COALESCE
MIN, MAX, AVG, SUM, COUNT
GROUP BY, HAVING
CASE, GROUP BY, TRANSPOSITION
SUBQUERIES

Objectifs de l'analyse

Répondre à différentes business questions pour accompagner l’entreprise dans sa prise de décision

WHERE CLAUSE + AND & OR

Select only the employees who work in Common Grounds and make more than 50k

				
					SELECT *
FROM employees
WHERE coffeeshop_id=1 AND salary > 50000
LIMIT 10;

employee_id	first_name	last_name	email	hire_date	gender	salary	coffeeshop_id
144108	Khalil	Corr	kcorr1@github.io	2014-04-23	M	52802	1
232690	Pincas	Mariotte	pmariotte1b@reuters.com	2018-12-08	M	63860	1
193281	Auberta	Greves	agreves1e@wikispaces.com	2012-07-06	F	54698	1
577294	Estevan	Wornum	NULL	2011-02-27	M	64490	1
986129	Ned	Castellanos	ncastellanos1x@hostgator.com	2016-05-28	M	65317	1
858668	Wright	Jermy	NULL	2021-12-30	M	51818	1
181482	Jacenta	Bettinson	jbettinson2t@sourceforge.net	2019-01-25	F	57914	1
633934	Kimberley	Vautin	kvautin2w@prweb.com	2014-08-31	F	56669	1
578451	Murvyn	Lanney	mlanney38@squarespace.com	2018-02-09	M	56011	1
503156	Julita	Gelly	jgelly3m@woothemes.com	2016-09-11	F	66837	1

Select only the employees who work in Early Rise or make more thant 50k

				
					SELECT *
FROM employees
WHERE coffeeshop_id=2 OR salary > 50000
LIMIT 10;

employee_id	first_name	last_name	email	hire_date	gender	salary	coffeeshop_id
144108	Khalil	Corr	kcorr1@github.io	2014-04-23	M	52802	1
782284	Vilhelmina	Rayman	vrayman2@jigsy.com	2015-08-17	F	57855	2
614903	Hamel	Grocock	hgrocock4@nasa.gov	2016-03-27	M	66566	3
938560	Samantha	Newall	snewalla@comsenz.com	2013-08-10	F	10223	2
746871	Joshua	Winscum	jwinscumb@sciencedirect.com	2022-12-29	M	28232	2
75097	Wally	Huebner	whuebnerc@dmoz.org	2020-08-30	F	57731	4
353657	Brigham	Boucher	bboucherh@army.mil	2016-03-09	M	38899	2
608868	Annabelle	Ottiwill	aottiwillj@wordpress.com	2016-07-19	F	54857	5
891720	Amye	Dilger	adilgern@amazon.com	2014-12-29	F	63671	3
826977	Lucas	Cuphus	NULL	2021-12-21	M	58566	5

IN, NOT IN, IS NULL, BETWEEN

Select all rows from the table where the supplier is Beans and Barley

				
					SELECT *
FROM suppliers
WHERE supplier_name = 'Beans and Barley';

coffeeshop_id	supplier_name	coffee_type
1	Beans and Barley	Arabica
2	Beans and Barley	Arabica
3	Beans and Barley	Arabica
5	Beans and Barley	Arabica

Select all rows from the table where the supplier is not Beans and Barley

				
					SELECT *
FROM suppliers
WHERE NOT supplier_name = 'Beans and Barley';
-- WHERE supplier_name <> 'Beans and Barley';

coffeeshop_id	supplier_name	coffee_type
1	Cool Beans	Robusta
2	Vanilla Bean	Liberica
2	Cool Beans	Robusta
3	Bean Me Up	Excelsa
3	Vanilla Bean	Liberica
3	Cool Beans	Robusta
4	Vanilla Bean	Liberica
4	Bean Me Up	Excelsa
5	Vanilla Bean	Liberica
5	Bean Me Up	Excelsa

Select all Robusta and Arabica coffee types

				
					SELECT *
FROM suppliers
WHERE coffee_type IN ('Robusta', 'Arabica');

coffeeshop_id	supplier_name	coffee_type
1	Beans and Barley	Arabica
1	Cool Beans	Robusta
2	Beans and Barley	Arabica
2	Cool Beans	Robusta
3	Cool Beans	Robusta
3	Beans and Barley	Arabica
5	Beans and Barley	Arabica

Select all coffee types that are not Robusta and Arabica

				
					SELECT *
FROM suppliers
WHERE coffee_type NOT IN ('Robusta', 'Arabica');

coffeeshop_id	supplier_name	coffee_type
2	Vanilla Bean	Liberica
3	Bean Me Up	Excelsa
3	Vanilla Bean	Liberica
4	Vanilla Bean	Liberica
4	Bean Me Up	Excelsa
5	Vanilla Bean	Liberica
5	Bean Me Up	Excelsa

Select all employees with missing emails

				
					SELECT *
FROM employees
WHERE email IS NULL
LIMIT 10;

employee_id	first_name	last_name	email	hire_date	gender	salary	coffeeshop_id
877425	Horten	Byre	NULL	2022-05-21	M	40458	5
826977	Lucas	Cuphus	NULL	2021-12-21	M	58566	5
824952	Marja	Lacey	NULL	2010-02-28	F	28628	5
793404	Aili	Stowe	NULL	2014-08-09	F	19337	1
702218	Eliza	Gonzalo	NULL	2022-12-11	F	61276	5
815230	Rockie	Simionato	NULL	2016-01-23	M	19016	5
577294	Estevan	Wornum	NULL	2011-02-27	M	64490	1
117778	Lorianna	Tomei	NULL	2021-06-18	F	52865	3
725760	Paxton	Nequest	NULL	2019-09-02	M	17484	1
141019	Willi	Croshaw	NULL	2019-02-06	M	39386	2

Select all employees who make between 35k and 50k

				
					SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 35000 AND 50000
LIMIT 10;

employee_id	first_name	last_name	salary
225709	Eleen	Tarpey	48048
243999	Jeremy	Whitlam	41159
599230	Webb	Hevey	48477
755091	Clem	Kitchingman	46818
925779	Pavel	Butchard	35003
353657	Brigham	Boucher	38899
877425	Horten	Byre	40458
593979	Rockie	Meriot	45651
790257	Rab	Dafter	49327
854959	Gustavus	Kettlestringe	47048

ORDER BY, DISTINCT

Order the data from the lowest salary to the highest

				
					SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary
LIMIT 10;

employee_id	first_name	last_name	salary
230049	Kevon	Perrigo	9878
714848	Ara	Ringe	10086
310537	Gilbertine	Whiscard	10176
615880	Lemar	Cotgrove	10220
938560	Samantha	Newall	10223
765510	Gloria	Doring	10239
783014	Cecelia	Huws	10241
771498	Malvina	Kloser	10256
684717	Evvie	Penlington	10436
123607	Dewain	Seadon	10447

Retrieve a list of the top 5 highest paid employees

				
					SELECT employee_id, first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

employee_id	first_name	last_name	salary
830873	Forrester	Roze	67724
356659	Dillon	Bourges	67599
275505	Huntley	Lovewell	67560
297631	Nolan	Coye	67548
249536	Austine	Benge	67539

Return all unique countries

				
					SELECT DISTINCT country
FROM locations;

country
United States
United Kingdom

EXTRACT

Return the year, month and day of the hire_date for each employee

				
					SELECT  EXTRACT (YEAR FROM hire_date) AS year,
		EXTRACT (MONTH FROM hire_date) AS month,
		EXTRACT (DAY FROM hire_date) AS day
FROM employees
LIMIT 10;

year	month	day
2015	8	29
2014	4	23
2015	8	17
2016	9	14
2016	3	27
2021	12	22
2014	1	21
2010	4	27
2014	7	3
2015	8	17

UPPER, LOWER, LENGTH

Uppercase last name, lowercase first name, return email and length of email

				
					SELECT  UPPER(last_name) AS upper_last_name,
		LOWER(first_name) AS lower_first_name,
		email,
		LENGTH(email) AS email_length
FROM employees
LIMIT 10;

upper_last_name	lower_first_name	email	email_length
MOSCONI	carson	cmosconi0@census.gov	20
CORR	khalil	kcorr1@github.io	16
RAYMAN	vilhelmina	vrayman2@jigsy.com	18
TARPEY	eleen	etarpey3@devhub.com	19
GROCOCK	hamel	hgrocock4@nasa.gov	18
BALLS	frazier	fballs5@nydailynews.com	23
WHITLAM	jeremy	jwhitlam6@nydailynews.com	25
HEVEY	webb	whevey7@wikia.com	17
SEXCEY	katharine	ksexcey8@harvard.edu	20
LILLOW	barton	blillow9@cam.ac.uk	18

CONCATENATION, BOOLEAN, EXPRESSIONS AND WILDCARDS

Concatenate the first name and last name

				
					SELECT first_name || ' ' ||last_name AS full_name
FROM employees
LIMIT 5;

full_name
Carson Mosconi
Khalil Corr
Vilhelmina Rayman
Eleen Tarpey
Hamel Grocock

If the person is Female and makes more than 50k then true else false

				
					SELECT  first_name || ' ' ||last_name AS full_name,
		salary,
		gender,
		(salary>50000 AND gender='F') AS less_than_50k
FROM employees
LIMIT 10;

full_name	salary	gender	less_than_50k
Carson Mosconi	32973	M	False
Khalil Corr	52802	M	False
Vilhelmina Rayman	57855	F	True
Eleen Tarpey	48048	F	False
Hamel Grocock	66566	M	False
Frazier Balls	15235	M	False
Jeremy Whitlam	41159	M	False
Webb Hevey	48477	M	False
Katharine Sexcey	23772	F	False
Barton Lillow	15083	M	False

If the email contains ".com" return true else false

				
					SELECT  email,
		(email like '%.com%') AS ".com"
FROM employees
LIMIT 10;

email	.com
cmosconi0@census.gov	False
kcorr1@github.io	False
vrayman2@jigsy.com	True
etarpey3@devhub.com	True
hgrocock4@nasa.gov	False
fballs5@nydailynews.com	True
jwhitlam6@nydailynews.com	True
whevey7@wikia.com	True
ksexcey8@harvard.edu	False
blillow9@cam.ac.uk	False

SUBSTRING, POSITION, COALESCE

Find the email clients of the employees

				
					SELECT  email,
		SUBSTRING(email FROM POSITION('@' IN email))
FROM employees
LIMIT 10;

email	substring
cmosconi0@census.gov	@census.gov
kcorr1@github.io	@github.io
vrayman2@jigsy.com	@jigsy.com
etarpey3@devhub.com	@devhub.com
hgrocock4@nasa.gov	@nasa.gov
fballs5@nydailynews.com	@nydailynews.com
jwhitlam6@nydailynews.com	@nydailynews.com
whevey7@wikia.com	@wikia.com
ksexcey8@harvard.edu	@harvard.edu
blillow9@cam.ac.uk	@cam.ac.uk

Fill missing emails with 'NO EMAIL PROVIDED'

				
					SELECT  employee_id,
		email,
		COALESCE(email,'NO EMAIL PROVIDED')
FROM employees
ORDER BY email DESC
LIMIT 10;

employee_id	email	coalesce
117778	NULL	NO EMAIL PROVIDED
725760	NULL	NO EMAIL PROVIDED
702218	NULL	NO EMAIL PROVIDED
793404	NULL	NO EMAIL PROVIDED
815230	NULL	NO EMAIL PROVIDED
577294	NULL	NO EMAIL PROVIDED
826977	NULL	NO EMAIL PROVIDED
877425	NULL	NO EMAIL PROVIDED
824952	NULL	NO EMAIL PROVIDED
141019	NULL	NO EMAIL PROVIDED

MIN, MAX, AVG, SUM, COUNT

Return the min salary, max salary, average salary, sum of all salaries, and the count of emails of employees

				
					SELECT  MIN(salary),
		MAX(salary),
		ROUND(AVG(salary),2),
		SUM(salary),
		COUNT(email)
FROM employees;

min	max	round	sum	count
9878	67724	38181.20	38181199	791

GROUP BY, HAVING

Return the total number of employees for each coffeeshop, the total salaries for each coffeeshop, average, min and max salaries. Return only the coffeeshops with more than 200 employees

				
					SELECT  coffeeshop_id,
		COUNT(employee_id),
		SUM(salary),
		MAX(salary),
		MIN(salary),
		ROUND(AVG(salary),2)
FROM employees
GROUP BY coffeeshop_id
HAVING COUNT(employee_id)>=200;

coffeeshop_id	count	sum	max	min	round
3	214	8585485	67560	10592	40119.09
5	203	7343255	67724	10220	36173.67
4	210	7875493	67599	9878	37502.35

CASE WITH GROUP BY AND CASE FOR TRANSPOSING DATA

Return a list of pay categories for salaries lower than 25k (Low Pay), salaries between 25k and 45k (Medium Pay), and salaries higher than 45k (High Pay)

				
					SELECT  employee_id,
		first_name,
		last_name,
		salary,
		CASE
			WHEN salary < 25000 THEN 'Low Pay'
			WHEN salary BETWEEN 25000 AND 45000 THEN 'Medium Pay'
			ELSE 'High Pay'
		END	AS pay_range
FROM employees
LIMIT 10;

employee_id	first_name	last_name	salary	pay_range
501559	Carson	Mosconi	32973	Medium Pay
144108	Khalil	Corr	52802	High Pay
782284	Vilhelmina	Rayman	57855	High Pay
225709	Eleen	Tarpey	48048	High Pay
614903	Hamel	Grocock	66566	High Pay
590293	Frazier	Balls	15235	Low Pay
243999	Jeremy	Whitlam	41159	Medium Pay
599230	Webb	Hevey	48477	High Pay
758331	Katharine	Sexcey	23772	Low Pay
561012	Barton	Lillow	15083	Low Pay

Return the count of employees in each pay category

				
					WITH cte AS (
SELECT  employee_id,
		first_name,
		last_name,
		salary,
		CASE
			WHEN salary < 25000 THEN 'Low Pay'
			WHEN salary BETWEEN 25000 AND 45000 THEN 'Medium Pay'
			ELSE 'High Pay'
		END	AS pay_range
FROM employees
)
SELECT pay_range, COUNT(*) AS count_employees
FROM cte
GROUP BY pay_range;

pay_range	count_employees
High Pay	380
Low Pay	273
Medium Pay	347

Transpose the output above

				
					SELECT
		SUM(CASE WHEN salary < 25000 THEN 1 ELSE 0 END) AS "Low Pay",
		SUM(CASE WHEN salary BETWEEN 25000 AND 45000 THEN 1 ELSE 0 END) AS "Medium Pay",
		SUM(CASE WHEN salary > 45000 THEN 1 ELSE 0 END) AS "High Pay"
FROM employees;

Low Pay	Medium Pay	High Pay
273	347	380

SUBQUERIES

Return first and last name, salary, and the difference between the salary and the average salary of all employees

				
					SELECT  first_name,
		last_name,
		salary,
		salary - (SELECT ROUND(AVG(salary),0) FROM employees) AS salary_difference
FROM employees
LIMIT 10;

first_name	last_name	salary	salary_difference
Carson	Mosconi	32973	-5208
Khalil	Corr	52802	14621
Vilhelmina	Rayman	57855	19674
Eleen	Tarpey	48048	9867
Hamel	Grocock	66566	28385
Frazier	Balls	15235	-22946
Jeremy	Whitlam	41159	2978
Webb	Hevey	48477	10296
Katharine	Sexcey	23772	-14409
Barton	Lillow	15083	-23098

Return all employees who work in US coffee shops and make more than 35k

				
					SELECT *
FROM employees
WHERE salary > 35000 AND coffeeshop_id IN
	(
	SELECT coffeeshop_id
	FROM shops
	WHERE city_id IN
		(SELECT city_id 
		 FROM locations
		 WHERE country = 'United States')
	)
LIMIT 10;

employee_id	first_name	last_name	email	hire_date	gender	salary	coffeeshop_id
144108	Khalil	Corr	kcorr1@github.io	2014-04-23	M	52802	1
782284	Vilhelmina	Rayman	vrayman2@jigsy.com	2015-08-17	F	57855	2
243999	Jeremy	Whitlam	jwhitlam6@nydailynews.com	2014-01-21	M	41159	4
599230	Webb	Hevey	whevey7@wikia.com	2010-04-27	M	48477	4
75097	Wally	Huebner	whuebnerc@dmoz.org	2020-08-30	F	57731	4
755091	Clem	Kitchingman	ckitchingmane@pinterest.com	2014-07-23	M	46818	4
925779	Pavel	Butchard	pbutchardf@opera.com	2016-09-21	M	35003	5
353657	Brigham	Boucher	bboucherh@army.mil	2016-03-09	M	38899	2
877425	Horten	Byre	NULL	2022-05-21	M	40458	5
608868	Annabelle	Ottiwill	aottiwillj@wordpress.com	2016-07-19	F	54857	5

Return the 30 day moving total pay

				
					SELECT  hire_date,
		salary,
		(SELECT SUM(salary)
		FROM employees e2
		WHERE e2.hire_date BETWEEN e1.hire_date - 30 AND e1.hire_date) AS pay_pattern
FROM employees e1
ORDER BY hire_date
LIMIT 20;

hire_date	salary	pay_pattern
2010-01-01	17691	17691
2010-01-11	32408	50099
2010-01-13	65993	116092
2010-01-23	21508	150505
2010-01-23	12905	150505
2010-02-06	39072	171886
2010-02-07	35271	207157
2010-02-09	13829	220986
2010-02-10	20835	241821
2010-02-12	19639	229052
2010-02-28	28628	157274
2010-03-02	55740	213014
2010-03-03	37106	250120
2010-03-04	39757	289877
2010-03-12	18104	219809
2010-03-27	37313	263663
2010-03-27	47015	263663
2010-03-29	55722	319385
2010-04-07	19095	177249
2010-04-13	23985	183130

Mekki MOURADI

Data Analyst

Rabat, Maroc

À Propos

Coffee Sales Project with SQL

Compétences & Outils

Description du projet

Objectifs de l'analyse

Home

Excel Projects

Tableau Projects

SQL Projects

Python Projects