SQL Murder Mystery

The Case

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.

Here is the schema diagram to show us the available tables and their relationships within the database

Investigation

To unravel the mystery, I've initiated the process of retrieving the pertinent crime scene report from the database of the police department, utilizing the information provided

From the above query output, there are two witnesses

1. The first witness lives at the last house on "Northwestern Dr."
2. The second witness named Annabel lives on "Franklin Ave."

Witness 1 Details

Witness 1 is Morty Schapiro with id 14887

Witness 2 Details

Witness 2 is Annabel Miller with id 16371

Now,lets see whether the witnesses have been interviewed to potentially gather clues about the crime.

From the witnesses interviews we have found out that the murder is

• Holds a gold membership at "get_fit_now_gym".
• Their gym bag code starts with "48Z".
• The car plate includes the letters "H42W".
• Was last spotted by Annabel Miller at the gym on January 9th.

Having obtained membership IDs of potential suspects from the witness interviews, our next step is to retrieve their corresponding person IDs.

Now armed with the names of the suspects, we'll utilize their person_id numbers to ascertain whether their license plates contain the letters "H42W", a clue provided by Morty Schapiro.

We have found the criminal personal id, now we can find the name of the criminal

We found the criminal, its Jermey Bowers!!

I had to check the answer by inserting the killer name in the query below:

Based on the information provided by the killer, I need to narrow down the result to find who is behind this murder.

Jeremy said

• The suspect is identified as a woman.
• Her height falls between 65 inches and 67 inches.
• She has red hair.
• She drives a Tesla Model S.
• She attended the SQL Symphony Concert at least three times in December 2017.

RESULT

Miranda Priestly was behind that crime and She hired Jeremy.

• Ecstatic about the benefits of a recent task involving SQL queries.
• Opportunity to practice SQL queries while solving a crime case.
• Confidence in using various SQL statements and filtering criteria to extract necessary data.
• Preparedness to further explore SQL's applications in a data analyst career.
• Encouragement for others to share their thoughts or experiences in the comments.