kolawole-faizah/Nigeria-crime-rate-analysis
Domain:
peace and securityRecord type:
dataset
Creator:
kol
Host:
Analysis of crime rate in Nigeria
# Nigeria-crime-rate-analysis 🇳🇬
Analysis of crime rate in Nigeria 🇳🇬
## Project Overview
This project is aimed at finding data-driven solutions to solving crime rate in Nigeria. This project also aim to provide insight into the crime rate over the course of three years.
Ultimately, providing recommendation on how to tackle these incidents moving forward.
## Data source
The primary datasource contains variable that gives information like state, city, crime type, date reported, severity and others across the country (Nigeria)
## Analytical tool
The tools used here are Microsoft PowerBI and MySQL.
1. Microsoft PowerBI (reporting) is used to create an interactive dashboard
2. MySQL (data analysis) is used to carry out the analysis.
## Data analysis
Query for the analysis..
```
CREATE DATABASE nigeria_crime_rate;
SELECT * FROM nigeria_crime_rate_dataset;
```
1. What is the average response time by crime type, and how does it compare across states?
```
SELECT Crime_Type,State,ROUND(AVG(Response_Time))
FROM nigeria_crime_rate_dataset
GROUP BY 1,2
ORDER BY 1 DESC,2 DESC,3 DESC;
```
2. Which cities have the highest rates of specific crimes (e.g., Theft), and how do they rank within each state?
```
WITH A AS (SELECT State,City,Crime_Type,COUNT(Crime_Type) AS Crimerate,
RANK()OVER(PARTITION BY Crime_Type ORDER BY COUNT(Crime_Type) DESC) AS RANKS
FROM nigeria_crime_rate_dataset
GROUP BY 1,2,3
ORDER BY 3,4 DESC)
SELECT City,Crime_Type, Crimerate, RANKS FROM A;
```
3. What is the trend of crime severity over time, especially in major cities?
```
SELECT City,YEAR(Date_Reported),ROUND(AVG(Severity),2)
FROM nigeria_crime_rate_dataset
where city in ('Ibadan','Port Harcourt','Lekki','Nsuka','Wuse','Zaria','Wari','Asaba','Kaduna North')
GROUP BY 1,2
ORDER BY 2,3 DESC;
```
4. Identify the top 5 states with the highest percentage of unsolved cases?
```
WITH A AS (SELECT State,Count(Outcome) AS UNRESOLVED,(SELECT COUNT(Outcome) from nigeria_crime_rate_dataset WHERE Outcome IN ( …