Mekki MOURADI

Data Analyst

Rabat, Maroc

Mekki MOURADI
mekkimouradi@gmail.com

À 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.

SQL Projects

SQL Date Time Functions

Liens

Certificat

Compétences & Outils

SQL
Data Cleaning
Statistical Analysis

Description du projet

Apprendre à manipuler les données via les Fonctions de Dates :
  • CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP
  • AGE
  • EXTRACT
  • TO_CHAR

Objectifs de l'analyse

  • Utiliser les Fonctions de Dates pour manipuler, extraire et formater des données de date et d’heure
  1. AGE
  1. Retrieve a list of the current age of all employees 
				
					SELECT  emp_no,
		AGE(CURRENT_DATE,birth_date) AS age
FROM employees
LIMIT 10;
emp_noage
1000170 years 4 mons 22 days
1000259 years 7 mons 22 days
1000364 years 1 mon 21 days
1000469 years 8 mons 23 days
1000569 years 3 days
1000670 years 9 mons 4 days
1000766 years 8 mons 1 day
1000865 years 11 mons 5 days
1000971 years 9 mons 5 days
1001060 years 7 mons 23 days
  1. Retrieve a list of all employees ages as at when they were employed 
				
					SELECT  emp_no,
		AGE(hire_date, birth_date) AS age_employed
FROM employees
LIMIT 10;
emp_noage_employed
1000132 years 9 mons 24 days
1000221 years 5 mons 19 days
1000326 years 8 mons 25 days
1000432 years 7 mons
1000534 years 7 mons 22 days
1000636 years 1 mon 12 days
1000731 years 8 mons 18 days
1000836 years 6 mons 24 days
1000932 years 9 mons 29 days
1001026 years 2 mons 23 days
  1. Retrieve a list of how long a manager worked at the company 
				
					SELECT  emp_no,
		AGE(to_date, from_date) AS years_employed
FROM dept_manager
ORDER BY AGE(to_date,from_date) DESC
LIMIT 10;
emp_noyears_employed
1101148009 years 15 days
1111338007 years 9 mons 25 days
1115348007 years 8 mons 23 days
1100398007 years 3 mons
1102288006 years 9 mons 11 days
1105678006 years 8 mons 6 days
1108548004 years 6 mons 3 days
1119398002 years 11 mons 29 days
1104208002 years 4 mons 2 days
1105117 years 3 mons 24 days

We can notice errors in the output, certainly due to some errors in the data. One way to tackle this issue from the start is to use this formula instead :

				
					SELECT emp_no,
		CASE
			WHEN AGE(to_date, from_date) < AGE(CURRENT_DATE, from_date) THEN  AGE(to_date, from_date)
			ELSE  AGE(CURRENT_DATE, from_date)
		END AS years_employed
FROM dept_manager
ORDER BY AGE(to_date,from_date) DESC
LIMIT 10;
emp_noyears_employed
11011434 years 1 mon 7 days
11113332 years 10 mons 17 days
11153432 years 9 mons 16 days
11003932 years 3 mons 23 days
11022831 years 10 mons 3 days
11056731 years 8 mons 29 days
11085429 years 6 mons 26 days
11193928 years 21 days
11042027 years 4 mons 25 days
1105117 years 3 mons 24 days
  1. Retrieve a list of how long it took to ship a product to a customer 
				
					SELECT  order_id,
		AGE(ship_date,order_date) AS ship_time
FROM sales
LIMIT 10;
order_idship_time
CA-2016-1521563 days
CA-2016-1521563 days
CA-2016-1386884 days
US-2015-1089667 days
US-2015-1089667 days
CA-2014-1158125 days
CA-2014-1158125 days
CA-2014-1158125 days
CA-2014-1158125 days
CA-2014-1158125 days
  1. Retrieve a list of the first name, last name, salary, and how long the employee earned a salary 
				
					SELECT  e.first_name, e.last_name, s.salary,
		CASE
			WHEN AGE(s.to_date, s.from_date) < AGE(CURRENT_DATE, s.from_date) THEN  AGE(s.to_date, s.from_date)
			ELSE  AGE(CURRENT_DATE, s.from_date)
		END AS period_salary
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
ORDER BY period_salary DESC
LIMIT 10;
first_namelast_namesalaryperiod_salary
BokyungAlpin6876922 years 5 mons 22 days
AimeeBrookner7051422 years 5 mons 22 days
ArfstPulkowski5183022 years 5 mons 22 days
SachinBach6399222 years 5 mons 22 days
NevinDulay6465722 years 5 mons 22 days
SatoruOtillio6362922 years 5 mons 22 days
AnnekePreusig5975522 years 5 mons 22 days
BezalelSimmel7252722 years 5 mons 22 days
MasakoBesselaar7463322 years 5 mons 22 days
FoxMagliocco7465022 years 5 mons 22 days
  1. EXTRACT
  1. Retrieve a list of the ship mode and how long (in seconds) it took to ship a product to a customer 
				
					SELECT  order_line,
		order_date,
		ship_date,
		EXTRACT(EPOCH FROM ship_date) - EXTRACT(EPOCH FROM order_date) AS seconds_taken
FROM sales
LIMIT 10;
order_lineorder_dateship_dateseconds_taken
12016-11-082016-11-11259200
22016-11-082016-11-11259200
32016-06-122016-06-16345600
42015-10-112015-10-18604800
52015-10-112015-10-18604800
62014-06-092014-06-14432000
72014-06-092014-06-14432000
82014-06-092014-06-14432000
92014-06-092014-06-14432000
102014-06-092014-06-14432000
  1. Retrieve a list of the ship mode and how long (in days) it took to ship a product to a customer 
				
					SELECT  order_line,
		order_date,
		ship_date,
		EXTRACT(DAY FROM ship_date) - EXTRACT(DAY FROM order_date) AS seconds_taken
FROM sales
LIMIT 10;
order_lineorder_dateship_dateseconds_taken
12016-11-082016-11-113
22016-11-082016-11-113
32016-06-122016-06-164
42015-10-112015-10-187
52015-10-112015-10-187
62014-06-092014-06-145
72014-06-092014-06-145
82014-06-092014-06-145
92014-06-092014-06-145
102014-06-092014-06-145
  1. Retrieve a list of the current age of all employees 
				
					SELECT  first_name,
		last_name,
		birth_date,
		EXTRACT(YEAR FROM CURRENT_DATE)-EXTRACT(YEAR FROM birth_date) || ' years old' AS current_age
FROM employees
LIMIT 10;
first_namelast_namebirth_datecurrent_age
GeorgiFacello1953-09-0271 years old
BezalelSimmel1964-06-0260 years old
PartoBamford1959-12-0365 years old
ChirstianKoblick1954-05-0170 years old
KyoichiMaliniak1955-01-2169 years old
AnnekePreusig1953-04-2071 years old
TzvetanZielinski1957-05-2367 years old
SaniyaKalloufi1958-02-1966 years old
SumantPeac1952-04-1972 years old
DuangkaewPiveteau1963-06-0161 years old
  1. Retrieve a list of all employees ages as at when they were employed 
				
					SELECT  first_name,
		last_name,
		EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date) || ' years old' AS age_employed
FROM employees
LIMIT 10;
first_namelast_nameage_employed
GeorgiFacello33 years old
BezalelSimmel21 years old
PartoBamford27 years old
ChirstianKoblick32 years old
KyoichiMaliniak34 years old
AnnekePreusig36 years old
TzvetanZielinski32 years old
SaniyaKalloufi36 years old
SumantPeac33 years old
DuangkaewPiveteau26 years old
  1. Retrieve a list of all employees who where 25 or less than 25 years as at when they were employed 
				
					SELECT  first_name,
		last_name,
		EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date) || ' years old' AS age_employed
FROM employees
WHERE EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date)<=25
ORDER BY age_employed DESC
LIMIT 10;
first_namelast_nameage_employed
VishuStrehl25 years old
HugoRosis25 years old
SibylNooteboom25 years old
SatosiAwdeh25 years old
CharleneBrattka25 years old
KshitijGils25 years old
ZissisPintelas25 years old
KendraHofting25 years old
ValdiodioNiizuma25 years old
XinglinPlessier25 years old
  1. How many employees were 25 or less than 25 years as at when they were employed 
				
					SELECT COUNT(EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date)) AS count_employees
FROM employees
WHERE EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date)<=25
count_employees
1579
  1. Retrieve the distribution of employees under 25 years 
				
					SELECT  EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date) || ' years' AS age_group,
		COUNT(EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date)) AS number_of_employees
FROM employees
WHERE EXTRACT(YEAR FROM hire_date)-EXTRACT(YEAR FROM birth_date)<=25
GROUP BY age_group
ORDER BY age_group
age_groupnumber_of_employees
20 years2
21 years101
22 years252
23 years313
24 years431
25 years480
  1. Retrieve a list of the product id, the month of sales and sales for chairs sub category in the year 2015 
				
					SELECT product_id, EXTRACT(MONTH FROM order_date), sales, order_date, sub_category
FROM sales
WHERE sub_category='Chairs' AND EXTRACT(YEAR FROM order_date) = 2015
LIMIT 10;
product_idextractsalesorder_datesub_category
FUR-CH-1000421812212.062015-12-27Chairs
FUR-CH-10003061489.992015-04-18Chairs
FUR-CH-100011464213.122015-04-30Chairs
FUR-CH-100005134831.942015-04-26Chairs
FUR-CH-1000469811396.82015-11-21Chairs
FUR-CH-100048602161.572015-02-28Chairs
FUR-CH-100044772389.72015-02-28Chairs
FUR-CH-1000059511190.722015-11-07Chairs
FUR-CH-1000264712283.922015-12-07Chairs
FUR-CH-10003956770.982015-07-03Chairs
  1. Retrieve a list of the month of sales and sum of sales for each month for Chairs sub category in the year 2015 
				
					SELECT  TO_CHAR(order_date::DATE, 'Month') AS month_2015,
		SUM(sales) AS total_sales
FROM sales
WHERE sub_category='Chairs' AND EXTRACT(YEAR FROM order_date) = 2015
GROUP BY month_2015
ORDER BY EXTRACT(MONTH FROM MIN(order_date));
month_2015total_sales
January 3246.79
February 2114.89
March 4085.23
April 4271.63
May 5681.94
June 3937.89
July 7313.56
August 1439.01
September9519.93
October 7103.54
November 12743.14
December 10276.99
SQL Projects