Motor Vehicle Thefts Analysis

Situation:

Working as a data analyst for nz police department to help in awareness about motor vehicle theft.

Assignment:

Dig into stolen vehicle database to find details on when, which, and where vehicles are most likely to be stolen

Objectives:

When, Which and Where vehicles are most likely to be stolen

-- Database consist of 3 tables

1.locations

2.make_details

3.stolen_vehicles

Data Explorations:

We can find insights by solving the questions

1. Finding out the data range in the given dataset 2.Find the number of vehicles stolen each year

3. .Find the number of vehicles stolen each month

We can understand that month wise there is an increase in theft. But in 2022 April there is a decline in theft, so need to deep dive into april 2022

by looking at the pattern we can say on april month of 2022 we count only 6 days, Basically theft increasing only

4. Analysing the patterns with replaced number values for day of the week

SELECT

DAYOFWEEK(date_stolen),

CASE

WHEN DAYOFWEEK(date_stolen) = 1 THEN 'sunday'

WHEN DAYOFWEEK(date_stolen) = 2 THEN 'monday'

WHEN DAYOFWEEK(date_stolen) = 3 THEN 'tuesday'

WHEN DAYOFWEEK(date_stolen) = 4 THEN 'wednesday'

WHEN DAYOFWEEK(date_stolen) = 5 THEN 'thursday'

WHEN DAYOFWEEK(date_stolen) = 6 THEN 'friday'

WHEN DAYOFWEEK(date_stolen) = 7 THEN 'saturday'

END AS dayof_week,

COUNT(vehicle_id)

FROM

stolen_vehicles

GROUP BY DAYOFWEEK(date_stolen) , dayof_week

ORDER BY DAYOFWEEK(date_stolen) , dayof_week

;

While checking the data we can see that on sundays and saturdays theft values are less and high on mondays and tuesdays

Exploring the vehicle type, age, luxury vs standard and color fields in the stolen_vehicles table to identify which vehicles are most likely to be stolen

5.Find the vehicle types that are most often and least often stolen

SELECT

vehicle_type, COUNT(vehicle_id) AS number_of_theft FROM stolen_vehicles

GROUP BY vehicle_type ORDER BY COUNT(vehicle_id) ASC;

least stolen : special purpose vehicle

most stolen : Stationwagon

6.For each vehicle type, find the average age of the cars that are stolen SELECT vehicle_type,

AVG(YEAR(date_stolen) - model_year) FROM stolen_vehicles

GROUP BY vehicle_type;/* Create a table where the rows represent the top 10 vehicle types, the columns represent the top 7 vehicle colors

(plus 1 column for all other colors) and the values are the number of vehicles stolen */

SELECT color, COUNT(vehicle_id) AS number_of_theft

FROM stolen_vehicles

GROUP BY color ORDER BY number_of_theft DESC

LIMIT 7;

create temporary table color

select

vehicle_id,

vehicle_type,

case

when color = 'Silver' then 'Silver'

when color = 'White' then 'White'

when color = 'Black' then 'Black'

when color = 'Blue' then 'Blue'

when color = 'Red' then 'Red'

when color = 'Grey' then 'Grey'

when color = 'Green' then 'Green'

else 'Other' end as colour

from stolen_vehicles ;

SELECT

vehicle_type,

COUNT(vehicle_id) AS number_of_theft,

COUNT(CASE WHEN colour = 'Silver' THEN vehicle_id ELSE NULL END) AS SILVER,

COUNT(CASE WHEN colour = 'White' THEN vehicle_id ELSE NULL END) AS White,

COUNT(CASE WHEN colour = 'Black' THEN vehicle_id ELSE NULL END) AS Black,

COUNT(CASE WHEN colour = 'Blue' THEN vehicle_id ELSE NULL END) AS Blue,

COUNT(CASE WHEN colour = 'Red' THEN vehicle_id ELSE NULL END) AS Red,

COUNT(CASE WHEN colour = 'Grey' THEN vehicle_id ELSE NULL END) AS Grey,

COUNT(CASE WHEN colour = 'Green' THEN vehicle_id ELSE NULL END) AS Green,

COUNT(CASE WHEN colour = 'Other' THEN vehicle_id ELSE NULL END) AS Other

FROM color

GROUP BY vehicle_type

ORDER BY number_of_theft DESC

LIMIT 10;

Remaining steps added in additional images....

Insights:

Most densly populated city Auckland is the most targeted city by thieves

Workdays like monday and tuesday are most likely to happen a theft

Sundays and Saturdays are least likely to stole a vehicle

Most luxury cars are of the categories convertible and sports vehicle

Recommendations

1.Improve surveillance and security measures in high-risk regions; run awareness campaigns there.

2.Be cautious to use extra caution on Monday and Tuesday.

3.Promote the use of new technology and mandate that all cars have real-time GPS installed.

4.The automobiles that are being targeted are under ten years old, so please be mindful of this.

Same analysis also done via POWER BI