🔍 SQL Murder Mystery Investigation

Welcome to my SQL Murder Mystery project! This interactive investigation demonstrates my SQL skills through solving a complex murder case using database queries.

🕵️‍♂️ The Case

A murder has occurred in SQL City on January 15, 2018, and it's up to us to solve it! Using a combination of Python, pandas, and SQLite3, we'll navigate through multiple database tables to find the killer and uncover a deeper conspiracy.

📊 Technologies Used

• Python
• SQLite3
• pandas
• Jupyter Notebook

💡 Key SQL Concepts Demonstrated

• Basic SQL queries (SELECT, FROM, WHERE)
• Table JOINs
• Aggregation functions
• GROUP BY and HAVING clauses
• Complex nested queries
• Data filtering and sorting
• Pattern matching with LIKE

🗃️ Database Structure

The investigation involves querying multiple tables:

• crime_scene_report
• person
• drivers_license
• get_fit_now_member
• get_fit_now_check_in
• facebook_event_checkin
• interview

🔎 Investigation Highlights

1. Initial Crime Scene Analysis: Started by querying the crime scene report to gather initial clues
2. Witness Identification: Used address information to locate key witnesses
3. Suspect Tracking: Cross-referenced gym memberships, check-in records, and vehicle information
4. Final Breakthrough: Uncovered the true mastermind through concert attendance records

🎯 Project Outcomes

• Successfully identified both the murderer and the mastermind behind the crime
• Demonstrated practical application of SQL
• Showcased data analysis and problem-solving skills
• Applied multiple SQL concepts in a coherent investigation

🚀 Getting Started

1. Clone this repository
2. Install required dependencies:

   pip install pandas sqlite3 jupyter

3. Open the Jupyter notebook:

   jupyter notebook sql_mystery.ipynb

4. Follow along with the investigation!

📚 What I Learned

• How to effectively combine Python and SQL for data analysis
• Techniques for querying and joining multiple database tables
• Methods for filtering and aggregating data to find specific patterns
• Best practices for documenting and presenting SQL analysis

🤝 Contributing

Feel free to fork this repository and try solving the mystery yourself! If you find any improvements or alternative solutions, I'd love to see them.

📝 License

This project is open source and available under the MIT License.