Murder Mystery Solved!
A walk-through of solving murder using data we have and extracting data required through SQL queries.
Before you read further, I would suggest you try it yourself. Click here.
The problem to solve:
A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City. Start by retrieving the corresponding crime scene report from the police department’s database.
The next obvious thing to do as a detective is to search with facts we have. the date, type of incident, and place we have. Let’s explore the crime report.
So, we have 2 witnesses and clues the 1st witness live on “Northwestern Dr” and the second witness Annabel lives on “Franklin Avenue”. Before we jump into solving, we have to get an idea of the database and schema designed.
To get to learn more facts we need to check the interview data if the witness has said any clues about the murder they witnessed.
We found that the first witness, Morty Schapiro lives in the last house on “Northwestern Dr”. As seen from the schema, with person id we can get interview transcript. So let’s find the second witness too.
Listing the facts from the witness.
- Killer is male.
- He goes to ‘Get Fit Now Gym’ and is a gold member. Premium Member of the gym. The membership number starts with “48Z”.
- The killer has a car with a license plate including “H42W”.
- Annabel recognized the killer from the gym while working out on January 9th. Check the people who attended the gym on the date and narrow it down to the duration Annabel worked out.
From mary’s statement, we found 2 suspects, let’s see which one attended during the same time as Annabel on January 9th. Annabel member id is 90081.
In detail, we see that 2 suspects and Annabel attended the gym similarly on January 9th, so it can be any one of the 2 suspects.
Another clue we have is the license plate number. That includes, “H42W”.
Checking with the license plate number, where the id in the above table is the license plate number.
Looks like we found the murderer. It’s Jeremy Bowers.
After catching the murderer, the next question is WHY? Let’s look into Jeremy’s interview, never leave a case until you are 100% sure, so let’s take up the challenge!
Second Query, using the facts from the above statement given by the murderer.
So, it was Miranda Priestly who hired Jermey to shoot. Mission Accomplished! Case Closed!
I wish we knew her story too, it would be more exciting. I tried to see if we have her interview transcript, but I guess the data we have is the one before catching the real suspect. It was fun and totally worth it.