About this project: There was a Murder in SQL City on Jan. 15th, 2018 that hasn't been solved yet!

The cold case was just reopened and Detective Bosch needed my help! I was asked to provide my sleuthing SQL skills to help solve this crime! A new piece of evidence was found that says if we don't solve the crime by Halloween, the killer will strike again! Halloween is tomorrow, so I had to work fast! This was the first time I was looking at the data and the pressure was on.

The goal was to find out whodunnit. Let me walk you through how I cracked the case!

Step 1:

I took a look at the Entity Relationship Diagram (ERD) to establish the layout and determine which tables I wanted to explore first.

Step 2:

First, I want to know what I'm dealing with and how many people are on this list. The "person" table looks like a great place to start! This will help me to understand how many known perpetrators I would have to sort through. I might recognize some criminals that have a history of committing crimes like this!

I ran this query two different ways, and I found that there were over 10K people in the database. That's a lot of names to go through! How would I narrow this down and find the person who committed the crime?

Step 3:

I wanted to know a little more about the people on this list. Maybe a certain detail will provide a clue. I need to see more about each person on the list. To do this, I selected all columns from the "person" table to see what information was on record. I initially limited my search to 20 records since I knew that there were over 10K and visually looking at a table with that many records wasn't going to be a good use of my time when the clock was ticking to solve this crime.

For each person we have their "license_id", "address_number", "address_street_name", and "ssn". This information isn't very helpful, yet. We need more to go on!

Step 4:

I really wanted to know more about the crime scenes on record and if there was any evidence left behind on the date of our crime that might help match anyone on our list.

I took a look at the "crime_scene_report" table to see what kinds of crimes we have in our database, and if any of them were similar to this crime.

Step 5:

Great, I now knew that there were 9 different types of crimes, and what those crimes were. The one in particular that stood out to me was murder. I still needed more information, so I looked at what other information was in the "crime_scene_report table", limiting it to 10 records just to get a high-level view of the table contents.

Step 6:

Very interesting! Not only did I see the type of crime committed, but there were also several crimes that occurred in SQL City! This could be the break that I needed!

First I limited to just the crimes that occurred in SQL City.

Step 7:

I was getting warmer, I needed to limit my search even more! I added more limitations to my search, restricting the "city" to only "SQL City" , the "type" to "murder", and ordered by "date".

We have some good information to go on now! We found information about a murder that occurred on the date in question in SQL City, and we now know that there are witnesses that we can question to help fill in more information.

Step 7:

I needed to find out what witnesses saw! With the limited name and address information, I searched for what I knew.

How many records were there for people who live on "Franklin Ave and "Northwestern Dr"?

This many records weren't helpful!! I needed to narrow my search since the clock was ticking.

Step 8:

I had to limit my search to include the witness's names and any other information that I knew!

I knew that one witness lived at the LAST house "Northwestern Dr". That would mean that the address number would be the highest on the street. I ONLY needed to know this ONE record, so I limited my search to ONE result.

I also knew that the other witness was named "Annabel" and lived on "Franklin Ave".

Step 9:

Great! I now knew that Annabel Miller and Morty Schapiro were the key witnesses, who gave fresh statements a week ago, and I wanted to find out if they said anything that might offer a clue to the murder. Where do I find their statement? I looked at the "interview" table.

Oh no! I couldn't search for Annabel Miller and Morty Schapiro because the transcript didn't have any names listed. I needed another way.

Step 10:

Not giving up, I was able to use the "id" number in "person_table" to the "person_id" in the "interview" table to match up my witnesses to their transcript statements! Annabel's ID was 16371 and Morty's ID was 14887.

Step 11:

I looked at the "get_fit_now_check_in" table for everyone who had a "check_in_date" of January 9th, 2018.

There were only 10 people who checked in at that time! I could work with this number, but I still needed more information. I wanted to know the names of the people who went to the gym that day.

Step 11:

I modified my search to include the "name" field in the "get_fit_now_member" table by joining the "membership_id" field to the "id" field in the "get_fit_now_member" table.

This was great! And the data even confirmed that Annabel Miller wasn't lying about going to the gym that day.

Step 12:

I needed to narrow down my search more! I had a partial license plate number of "H42W" which was a key piece of information! To find this, added another layer and joined the "person" table to obtain the "name" in the "person" table to the "name" in the "get_fit_now_member" table, and then another join to the "drivers_license" table.

Step 12: SPOILER!!!!!!!!!!!!!!

I finally identified the murderer as "Jeremy Bowers"!

Step 13:

But there was more! Another note was found just a moment ago to check the interview transcript from Jeremy and find out who the REAL KILLER was! 😮

To be continued! 😮😮😮😮😮