SQL Murder Mystery

Just a fun little exercise to practice my SQL querying. Nothing really complex. What was tiring was jumping around different tables and some guesswork on what some id columns refer to.

Good for some fun if you’re looking to practice your SQL skills.

SQL Murder Mystery

Here’s the link to the game: https://mystery.knightlab.com/

First things first, we know there has been a murder and it occured sometime on Jan 15 2018 in SQL City.

Here are the tables that we can use the find the killer.

name
crime_scene_report
drivers_license
person
facebook_event_checkin
interview
get_fit_now_member
get_fit_now_check_in
income
solution

Getting initial information from crime_scene_report

As suggested, let’s start by retreving the crime scene report. Ok so let’s explore what’s in the crime_scene_report table.

Running this query:

SELECT sql 
FROM sqlite_master
where name = 'crime_scene_report'

tells us there are 4 columns (date integer, type text, description text, city text )

Let’s see what turns up when we query that with all the information we have so far.

select *
from crime_scene_report
where city = 'SQL City'
and date = 20180115
and type = 'murder'

AHA! More information we can use!

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”.

Let’s see here. We have 2 witnesses and an indication of where they live.

Exploring the Person table

To hone in on to their details, I think we need the person table.

Let’s look into the first witness using what we know.

1. Largest house number
2. Lives in Northwestern Dr

select *
from person
where address_street_name = 'Northwestern Dr'
order by address_number desc

Our first witness is Morty Schapiro (id 14887)

Looking into the second witness..

select *
from person
where address_street_name = 'Franklin Ave' and name like '%annabel%'

Her name is Annabel Miller (id 16371)

Checking the interview table

Ok where can we get more information? The interview table might be interesting. Let’s see if they were interviewed.

It looks like the interview table only has person_id and transcript. Let’s see if the id we saved earlier was useful.

select *
from interview
where person_id = 16371 OR person_id = 14887

Bingo! More clues!

Let’s look at the first one.

1. Gold gym member and membership number starts with “48Z”.
2. Got into a car with plate containing “H42W”.
3. The killer was in the gym on Jan 9.

Let’s see what the get_fit_now_member table can tell us based on the clues above.

select *
from get_fit_now_member
where membership_status = 'gold' AND id like '%48Z%'
limit 100;

Okay so we have 2 guys here who fit the bill:

1. Joe Germuska (id 28819, member id 48Z7A)
2. Jeremy Bowers (id 67318, member id 48Z55)

Let’s see what the vehicles table can tell us. I noticed that it doesn’t have a name column so I joined it to the persons table using license_id

select *
from drivers_license
join person on person.license_id = drivers_license.id
where plate_number like '%H42W%'
limit 100;

Well what do you know, Jeremy Bowers’ name turned up. Looks like he’s the murderer?

Let’s go back at the interview table to see if he has given any testimony

select *
from interview
where person_id = 67318 
limit 100;

The plot thickens! Looks like there was someone behind the murder and Mr Bowers was just a hired gun.

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.

Let’s see who has attended that event 3 times in 2017.

select 
person_id, 
p.name,
count(person_id),
event_name, 
substr(CAST(date as varchar),0,5) as year, 
substr(CAST(date as varchar),5,2) as month 

from facebook_event_checkin e
join person p on p.id = e.person_id

where event_name like '%SQL Symphony%' and substr(CAST(date as varchar),0,5) = '2017' AND substr(CAST(date as varchar),5,2) = '12'

group by event_name, 
person_id, 
substr(CAST(date as varchar),0,5), 
substr(CAST(date as varchar),5,2)

having count(person_id) = 3
limit 100;

And since the mastermind is a female, it has to be Miranda Priestly.

Badaboom. Mystery solved.