Global Homicide Trends: SQL and SQLite Analysis

Overview

This learning-focused portfolio project uses international intentional-homicide statistics from UNdata to demonstrate a reproducible SQL and SQLite analysis workflow. It examines recorded homicide counts and rates across countries, regions, subregions, and years.

The analysis is descriptive. It identifies patterns in the available data but does not establish why homicide levels changed or recommend specific public-safety interventions.

Questions explored

• How do recorded homicide counts and rates vary across countries and regions?
• Which geographic areas report the highest and lowest values in the available data?
• How do recorded values change over the period covered by the dataset?
• How can SQL filtering, aggregation, sorting, and joins support exploratory analysis?

Data

The source data was downloaded in XLS format and lightly cleaned and restructured with Python before being imported into SQLite.

The analysis uses two processed CSV files:

• number.csv — recorded homicide counts by country, region, subregion, and year.
• rates.csv — recorded homicide rates for corresponding geographic units and years.

The repository should include the exact UNdata source URL, download date, indicator definitions, and any transformations applied to the original files.

Workflow

1. Download the source files from UNdata.
2. Convert the relevant worksheets from XLS to CSV.
3. Clean column names and reshape the data where required.
4. Import the processed CSV files into SQLite using DB Browser for SQLite.
5. Use SQL queries to filter, aggregate, compare, and join the tables.
6. Review the results and document descriptive patterns and limitations.

Tools

• SQL
• SQLite
• DB Browser for SQLite
• Python and pandas for light data preparation
• Jupyter Notebook

Repository contents

• Data-preparation notebooks for XLS-to-CSV conversion and restructuring
• Processed count and rate datasets
• SQLite analysis notebooks and queries
• Supporting database file, where required

How to use the project

Dataset Download:

• Start by downloading homicide datasets here, here and here .
• Save datasets on your local machine.

Data Cleaning and Transformation:

• Open the Python script provided in the repository.
• Modify the script to load the dataset you downloaded.
• Run the script to perform basic data cleaning and transformation tasks.

Database Creation and Import:

• Open DB Browser for SQLite.
• Create a new database and give it an appropriate name.
• Use the "Import" option to add the cleaned data as CSV files to the database. Ensure you follow the steps mentioned in the article to import the data correctly.

Exploring Insights:

• Once you have the database set up, you can start exploring the data using SQL queries.
• Open the "Execute SQL" tab in DB Browser.
• Copy the SQL queries provided in the article or write your own to analyze the data. You can explore trends, compare homicide rates, and identify patterns.

Interpreting Results:

• As you execute the SQL queries, the results will be displayed in tabular format. Interpret the results to understand trends, compare regions, and identify countries with high or low homicide rates.

Drawing Conclusions:

• Based on the insights gained, draw conclusions about the trends and patterns in homicide rates across different countries and regions.

MEDIUM ARTICLE

In the medium article , you'll get an in-depth look at the methodologies I used, the objectives I set out to achieve, and the exciting discoveries I made along the way.

Feel free to reach out if you have any questions or thoughts to share!