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

Vehicle Accidents Analysis with SQL

Compétences & Outils

SQL
Tableau Software
Data Wrangling
Interactive Visualization

Description du projet

Apprendre à combiner plusieurs outils d’analyse et de visualisation pour répondre aux questions commerciales : 

  • Utiliser SQL pour manipuler et analyser les données
  • Utiliser Tableau pour visualiser les résultats et les rendre plus accessibles

Objectifs de l'analyse

Utiliser SQL et Tableau pour manipuler, analyser et visualiser les données
  1. Combien d'accidents se sont produits en zones urbaines par rapport aux zones rurales ?
				
					SELECT COUNT(accidentindex) AS accidents, area
FROM accident
GROUP BY area;
accidentsarea
58533Urban
21999Rural
  1. Quel jour de la semaine a le plus grand nombre d'accidents ?
				
					SELECT day, COUNT(accidentindex) AS number_accidents
FROM accident
GROUP BY day
ORDER BY number_accidents DESC
LIMIT 1;
daynumber_accidents
Friday12937
  1. Quel est l'âge moyen des véhicules impliqués dans des accidents de la route en fonction de leur type?
				
					SELECT vehicletype, ROUND(AVG(agevehicle),2) AS average
FROM vehicle
GROUP BY vehicletype
HAVING ROUND(AVG(agevehicle),2) IS NOT NULL
ORDER BY average DESC;
vehicletypeaverage
Motorcycle over 500cc10.46
Motorcycle over 125cc and up to 500cc10.38
Motorcycle - unknown cc8.58
Car8.27
Agricultural vehicle7.90
Other vehicle7.82
Minibus (8 - 16 passenger seats)7.75
Bus or coach (17 or more pass seats)7.14
Goods vehicle - unknown weight6.63
Goods over 3.5t. and under 7.5t6.56
Motorcycle 50cc and under6.50
Taxi/Private hire car6.35
Van / Goods 3.5 tonnes mgw or under6.27
Motorcycle 125cc and under6.09
Goods 7.5 tonnes mgw and over5.29
Data missing or out of range4.00
Mobility scooter2.50
  1. Peut on identifier une quelconque corrélation entre le nombre d'accidents et l'âge du véhicule ?
  1. Extraction des données nécessaires 
				
					SELECT vehicletype, COUNT(accidentindex) AS number_accidents, ROUND(AVG(agevehicle),2) AS vehicle_age
FROM vehicle
GROUP BY vehicletype
ORDER BY number_accidents DESC;
vehicletypenumber_accidentsvehicle_age
Car1829548.27
Pedal cycle19440NULL
Van / Goods 3.5 tonnes mgw or under138766.27
Motorcycle 125cc and under92346.09
Motorcycle over 500cc705410.46
Taxi/Private hire car54206.35
Bus or coach (17 or more pass seats)53817.14
Goods 7.5 tonnes mgw and over47625.29
Motorcycle 50cc and under22376.50
Motorcycle over 125cc and up to 500cc218710.38
Goods over 3.5t. and under 7.5t17086.56
Other vehicle12867.82
Goods vehicle - unknown weight6156.63
Agricultural vehicle5047.90
Minibus (8 - 16 passenger seats)4987.75
Motorcycle - unknown cc2758.58
Mobility scooter2222.50
Ridden horse107NULL
Data missing or out of range584.00
Tram18NULL
Electric motorcycle9NULL
  1. Visualisation des résultats

D’après le scatterplot, on ne peut conclure aucune corrélation entre le nombre d’accidents et l’âge moyen du véhicule

  1. Y a-t-il des conditions météorologiques spécifiques qui contribuent aux accidents graves ? 
				
					SELECT weatherconditions, severity, COUNT(accidentindex) AS accidents
FROM accident
WHERE severity='Fatal'
GROUP BY weatherconditions, severity
ORDER BY accidents DESC;
weatherconditionsseverityaccidents
Fine no high windsFatal668
Raining no high windsFatal84
Fine + high windsFatal18
UnknownFatal17
Raining + high windsFatal17
OtherFatal7
Fog or mistFatal5
Snowing no high windsFatal1

D’après la treemap, on ne peut considérer de conditions météorologiques spécifiques qui contribueraient aux accidents graves.

  1. Les accidents impliquent-ils souvent des impacts du côté gauche des véhicules ? 
				
					SELECT lefthand, COUNT(accidentindex) AS number_accidents
FROM vehicle
GROUP BY lefthand
lefthandnumber_accidents
Data missing or out of range1019
No255480
Yes1346
  1. Y a-t-il une quelconque relation entre les motifs de déplacements et la gravité des accidents ? 
				
					SELECT v.journeypurpose, a.severity, COUNT(v.accidentindex) AS accidents
FROM vehicle v
JOIN accident a
ON a.accidentindex=v.accidentindex
WHERE journeypurpose NOT IN ('Data missing or out of range', 'Other')
GROUP BY v.journeypurpose, a.severity
ORDER BY journeypurpose,
	CASE
		WHEN severity='Fatal' THEN 1
		WHEN severity='Serious' THEN 2
		ELSE 3
	END;
journeypurposeseverityaccidents
Commuting to/from workFatal112
Commuting to/from workSerious1833
Commuting to/from workSlight25021
Journey as part of workFatal310
Journey as part of workSerious2632
Journey as part of workSlight36843
Not knownFatal1014
Not knownSerious13108
Not knownSlight171924
Pupil riding to/from schoolFatal2
Pupil riding to/from schoolSerious38
Pupil riding to/from schoolSlight777
Taking pupil to/from schoolFatal5
Taking pupil to/from schoolSerious120
Taking pupil to/from schoolSlight2509
  1. Calculer l'âge moyen des véhicules impliqués dans des accidents, en tenant compte de la lumière du jour et du point d'impact.
				
					SELECT a.lightconditions, v.pointimpact, ROUND(AVG(v.agevehicle),2) AS avg_vehicle_age
FROM accident a
JOIN vehicle v
ON a.accidentindex=v.accidentindex
GROUP BY a.lightconditions, v.pointimpact
lightconditionspointimpactavg_vehicle_age
DarknessBack7.73
DarknessDid not impact6.80
DarknessFront8.22
DarknessNearside7.75
DarknessOffside7.83
DaylightBack7.57
DaylightData missing or out of range8.30
DaylightDid not impact7.51
DaylightFront8.31
DaylightNearside7.91
DaylightOffside7.92
SQL Projects