SQL Case Study – Analyzing Los Angeles crime data using SQL queries | PGP-DSBA (Great Lakes & Texas McCombs)
The mayor of Los Angeles established a new Criminal Investigation Division to analyze rising crime trends.
As part of the Analytics Division, I performed SQL-based analysis to answer critical business questions and uncover patterns in crime data.
This case study demonstrates SQL skills in querying, filtering, joining, and aggregating data to provide actionable insights for city authorities.
- Identify major crime categories and frequency
- Analyze year-wise and month-wise crime trends
- Find areas with the highest crime density
- Study victim demographics and age-group analysis
- Generate reports for policymakers to improve safety measures
- SQL: Filtering, Aggregation, Window Functions, Subqueries, CASE WHEN
- Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN
- Advanced SQL: Mathematical calculations, Derived columns
- Visualization: Generated outputs to answer analytical questions
- Theft and Assault were among the most frequent crimes in LA.
- Certain regions consistently reported higher crime density.
- Analysis revealed that young adults were the most common victims.
- Seasonal/monthly patterns indicated spikes during specific times.
-
sql/sql/crime_la_queries.sql → Final SQL queries (Q1–Q10)
-
sql/sql/crime_la_project_dumpfile.sql → Database dump file
-
docs/Crimes_LA_Project_Report.pdf → Final Business Report (with insights & findings)
- Improved SQL query writing with complex filters and joins
- Gained ability to transform raw data into actionable insights
- Enhanced report writing skills for communicating results
This project was completed as part of the Post Graduate Program in Data Science & Business Analytics (PGP-DSBA) with Great Lakes & Texas McCombs.