Solving the SQL Murder Mystery: A Step-by-Step Guide

Maybe I should consider a career as a detective! In this article, I am going to walk you through how I solved a mystery murder case from SQL Murder Mystery. Let us dive into the crime scene and uncover the truth step by step.

The Mystery
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. Let's start by retrieving the corresponding crime scene report from the police department’s database. A schema was provided;

The Initial Clues
I only remembered the type of crime, the city, and the date. So, I started with this query:

SELECT * FROM crime_scene_report 
WHERE type = 'murder' 
AND city = 'SQL City' 
AND date = '20180115';

Here’s what I found:
"Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness named Annabel lives somewhere on "Franklin Ave""

Great! So We have two witnesses. The first one lives at the last house on Northwestern Dr, and the second one, Annabel, lives on Franklin Ave.

Finding the First Witness
To identify the first witness, I run this query:

SELECT * FROM person 
WHERE address_street_name = 'Northwestern Dr' 
ORDER BY address_number DESC 
LIMIT 1;

From this query, I was able to identify Morty Schapiro with person_id 14887 and license_id 118009 as my first witness.

Finding the Second Witness
For the second witness, Annabel, I ran:

SELECT * FROM person 
WHERE name LIKE 'Annabel%' 
AND address_street_name = 'Franklin Ave';

From this query, I was able to get my second witness Annabel Miller with person_id 16371 and license_id 490173.

Gathering Witness Statements
Next, I needed to find out what each witness saw. I run a query to know what Morty Schapiro witnessed:

SELECT * FROM interview 
WHERE person_id = '14887';

Morty's statement: I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z". Only gold members have those bags. The man got into a car with a plate that included "H42W".

Now, let’s run a query to get Annabel Miller's statement:

SELECT * FROM interview 
WHERE person_id = '16371';

Annabel's statement: I saw the murder happen and I recognized the killer from my gym when I was working out last week on January the 9th.

Identifying the Killer
From the statements above, I know the following:

• The murderer is a "Get Fit Now Gym" gold member.

• The membership number starts with "48Z".

• The car plate includes "H42W".

• The murderer was at the gym on January 9th.

First, I checked for gym check-ins on January 9th with this query:

SELECT * FROM get_fit_now_check_in 
WHERE check_in_date = '20180109' 
AND membership_id LIKE '48Z%';

I got two results with membership_id 48Z7A and 48Z55. Both are gold members, so I need to narrow it down using the car plate information.

Narrowing Down the Suspects
First, I retrieved the license details for both suspects:

SELECT * FROM person 
WHERE id IN (28819, 67318);

I got two result from running the query above: Jeremy Bowers with license_id 423327 and Joe Germuska with license_id 173289.

Next, I checked their car plates:

SELECT * FROM drivers_license 
WHERE id IN (423327, 173289);

From my result, I was able to identify Jeremy Bowers as the Murderer.

The Real Mastermind
Upon further investigation, I found that Jeremy Bowers was hired by a woman with distinct characteristics. Let’s run a query to see his statement:

SELECT * FROM interview 
WHERE person_id = '67318';

Jeremy's statement: I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.

Using this information, I searched for the woman:

SELECT * FROM drivers_license 
WHERE hair_color = 'red' 
AND gender = 'female' 
AND car_make = 'Tesla' 
AND car_model = 'Model S';

I got 3 results from this, all with the same height as described by Jeremy. From Jeremy's statement the mastermind attended SQL Symphony Concert thrice.
I run a query to check for the person_id on the person table.

SELECT * FROM person
WHERE license_id in (202298,291182,918773);

I got the license_id and name of the 3 possible mastermind from the query above; Red korb with id 78881, Regina George with id 90700 and Miranda Priestly with id 99716.

Finally, I run a query to check their attendance at the SQL Symphony Concert, to see who attended thrice;

SELECT * FROM facebook_event_checkin 
WHERE person_id IN (78881, 90700, 99716);

From my query, I was able to get that The mastermind is Miranda Priestly with person_id 99716, who attended the concerts three times.

Conclusion
The murderer is Jeremy Bowers, but the mastermind behind the crime is Miranda Priestly. This was a thrilling case to solve, combining SQL queries with logical deduction. It was an exciting journey to catch the real villain behind the murder.

Would you like to try solving it yourself? Head over to SQL Murder Mystery and put your detective skills to the test!