Whodunnit? A solution and walkthrough for SQL Murder Mystery

 

There are two ways to go through the game. Either you just jump and figure out yourself or go with the walkthrough. Since I want to challenge myself and see if the basic SQL knowledge I got is enough, I jumped with the game directly. No help of AI.

I'll walk you through how I solved the game and found out the killer--and more. Please note that I will not treat you as a total beginner without SQL knowledge here.

Getting Started

The only hint we got from the website is the text below:

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.

So, our clues are:

- This happened on January 15, 2018

- Happened on SQL City

Now, we'll start on looking into the police department's database. The game instructed us to get the tables available in the DB (short for DataBase) by running the command:

SELECT name
FROM sqlite_master
where type = 'table'

This returns the following tables:

crime_scene_report, drivers_license, facebook_event_checkin,
interview, get_fit_now_member, get_fit_now_check_in,
solution, income, person

We also need to know the column and data types of each column so the game instructed us to run:

SELECT sql
FROM sqlite_master
where name = 'crime_scene_report'

This returns the following:

CREATE TABLE crime_scene_report ( date integer, type text, description text, city text )

So we know that the crime_scene_report table has the following columns and corresponding data types:

date - integer

type - text

description -text

city - text

During the early parts of playing the game, I decided to run the command they provided to find out the columns and data types but eventually stopped on using it and go my own way. I didn't use the schema and walkthrough available.

So, let's get one sample row from the db to get an idea on what we are working on here.

SELECT * FROM crime_scene_report
WHERE 1=1
LIMIT 1

Translates to: We want to select everything from crime_scene_report table and only show 1 row.

The WHERE 1=1 is for convenience when writing the next set of filters later on. Here are blogs that explains this further:

• https://www.navicat.com/en/company/aboutus/blog/1812-the-purpose-of-where-1-1-in-sql-statements
• https://www.sql-easy.com/learn/why-use-where-11-in-sql-queries/

We'll get the following result from that query:

The formatting used for date is integer as shown earlier and YYYYMMDD (year-month-date). Our query now, considering the clues provided, will be:

SELECT * FROM crime_scene_report
WHERE 1=1
AND date = 20180115
AND city LIKE '%SQL%'
AND type LIKE '%murder%'

We can remove the % prefix and suffix for SQL and murder but this is me first time accessing the DB entries and anticipating that the table might not have clean data. Sort of catch-all.

Result:

Finding witnesses

Okay, we have 2 witnesses:

1. Someone who lives at the last house on Northwestern Dr.

2. Annabel, who lives somewhere on Franklin Ave

Let's check the person table and see what we can get from it.

SELECT * FROM person
LIMIT 1

We get the following columns and we're mainly interested with where they live and their name.

So for witness 1:

SELECT * FROM person
WHERE 1=1
AND address_street_name LIKE '%Northwestern Dr%'
ORDER BY address_number DESC
LIMIT 3

Translates to: We want everything from person table with address_street_name of Northwestern Dr then sort by address_number, in descending order.

We were told that our first witness lives on the last house of Northwestern Dr so we sorted the results based on the highest address_number. The limit is placed just so we can get the first 3 results and validate that we indeed got the correct query.

Result:

So our first witness is Morty Schapiro. Let's look for our second witness.

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

Result:

Only one output so this is likely our second witness already.

Getting statements from witnesses

Let's now go to the interview table and see what it looks like:

SELECT * FROM interview
LIMIT 1

Result:

So, the interview table only has 2 columns; person_id and transcript. We already know the id of our witnesses so we can manually query and get their transcript.

SELECT * FROM interview
WHERE person_id = 14887

Result:

Or, we can use joins and get a more beautiful/customized output.

SELECT
person.id, person.name, interview.transcript
FROM person
JOIN interview
ON person.id = interview.person_id
WHERE person.address_street_name LIKE '%Northwestern%'
ORDER BY person.address_number desc
LIMIT 1

Result:

For our second witness:

SELECT
person.id, person.name, interview.transcript
FROM person
JOIN interview
ON person.id = interview.person_id
WHERE 1=1
AND person.address_street_name LIKE '%Franklin Ave%'
AND name LIKE '%Annabel%'

Result:

We got the statements of our 2 witnesses so we can dig based on each of the witness' statement. Let's go with the first witness.

Looking at the first witness' interview

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

Clues for suspect:

- Has a Get Fit Now Gym bag, which is only given to gold members.

- Membership number on the bag starts with 48Z

- Getaway vehicle has plate including H42W

It's not uncommon for criminals to use a different getaway vehicle unrelated to them and use someone else's belongings to make the investigation more difficult but we'll use the information we have. It's also possible that the witness remembered incorrect information. We just need to be skeptical.

Let's run a query for the gym-related table:

SELECT * FROM get_fit_now_member
WHERE 1=1
AND id LIKE '48Z%'
AND membership_status = 'gold'

Result:

We have two possible suspects. Let's also check the vehicle part of witness interview. Only related table is drivers_license.

SELECT * FROM drivers_license
LIMIT 1

Result:

We already have an idea what the columns of person table looks like so let's try to join those two tables.

SELECT license_id, name, age, height, hair_color,
gender, plate_number, car_make, car_model
FROM person
JOIN drivers_license
ON person.license_id = drivers_license.id
WHERE 1=1
AND plate_number LIKE '%H42W%'
LIMIT 5

Result:

We set the limit to 5 but we only got 3 rows so only three people matches our query. Interesting that Jeremy Bowers appeared on both queries.

We should not rely on single data point, as we should not be arresting innocent people. Let's see what the second witness has to say.

Looking at the second witness' interview

I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

Get row sample for get_fit_now_check_in table

SELECT * FROM get_fit_now_check_in
LIMIT 1

Result:

We know that the murder happened last 2018 so let's count entries we got for the date when Annabel worked out and allegedly saw the killer back then.

SELECT COUNT(*) AS jan_9_check_ins from get_fit_now_check_in
WHERE 1=1
AND check_in_date = 20180109

Result:

Counting is not really necessary here so this can be skipped. This is just part of exploring the dataset.

We have only 10 check-ins for January 9, 2018 so we can browse through all of the records and display everything.

Let's join the get_fit_now_member table and  membership_id table to get the name of the persons who checked-in for our specified date. We can also review if Annabel is telling the truth as she mentioned that she saw the suspect on an earlier day in the same gym.

SELECT
membership_id, check_in_date,
check_in_time, name, membership_status
FROM get_fit_now_check_in
JOIN get_fit_now_member
ON get_fit_now_check_in.membership_id = get_fit_now_member.id
WHERE 1=1
AND get_fit_now_check_in.check_in_date = 20180109

We can show all columns on the query but I only picked the columns I think that are most important.

Result:

Annabel, our second witness, indeed went to the gym at that day. Before she checked-in, someone named Jeremy Bowers, a gold member, checked in minutes earlier.

We've established that Annabel is likely telling the truth about going to the gym on that date. This is the third time that Jeremy Bowers appeared on our queries so could he be our killer? Let's check if we have interview records for him.

Checking the suspect's interview

We can say with confidence that Jeremy Bowers is our killer but let's check our records for his statement, if there's any.

SELECT
person.id, person.name, interview.transcript
FROM person
JOIN interview
ON person.id = interview.person_id
WHERE 1=1
AND person.name = 'Jeremy Bowers'

Result:

Okay, he's definitely the killer based on this confession. I don't think the case ends here as our killer is telling us something about the mastermind which should also be investigated.

Looking for the mastermind

Here's the killer'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.

Clues:

- A woman with a lot of money.

- No name but around 65" or 67" height.

- Red hair

- Drives a Tesla Model S

- Attended the SQL Symphony Concert, 3 times, on December 2017

We will use the concert attendance clue then work our way to verifying the physical characteristics and the vehicle information.

SELECT * FROM facebook_event_checkin
LIMIT 1

Result:

Now that we got the column names and sample row, let's do a query filtering only the event named SQL Symphony Concert, from December 2017 (the format is still year-month-day). Then group by person_id and sort based on how frequent they attended the said event (descending order). We only want 5 entries.

SELECT person_id, event_id, event_name,
COUNT(*) AS count_person_id_attendance
FROM facebook_event_checkin
WHERE 1=1
AND event_name = 'SQL Symphony Concert'
AND date LIKE '201712%'
GROUP BY person_id
ORDER BY count_person_id_attendance DESC
LIMIT 5

Result:

We have two persons who attended the SQL Symphony concert thrice last December 2017. Let's make the query a bit more complex so we know the names of the people and only filter those who attended thrice.

SELECT person_id, name, event_name,
COUNT(*) AS count_person_id_attendance
FROM facebook_event_checkin
JOIN person
ON facebook_event_checkin.person_id = person.id
WHERE 1=1
AND event_name = 'SQL Symphony Concert'
AND date LIKE '201712%'
GROUP BY person_id
HAVING count_person_id_attendance = 3

Result:

We know from the killer that the mastermind is a woman and based on the name, we may be looking at Miranda Priestly as the mastermind. Let's test against the characteristics shared by the killer, versus the drivers_license table.

We already know the columns of drivers_license table from earlier query.

SELECT license_id, name, age,
height, hair_color, gender,
plate_number, car_make, car_model
FROM person
JOIN drivers_license
ON person.license_id = drivers_license.id
WHERE 1=1
AND hair_color = 'red'
AND car_make = 'Tesla'
AND gender = 'female'
LIMIT 10

Result:

We set the limit to 10 but we only have 3 persons who match the criteria.

Miranda Priestly appears to have a red hair color, owns/drives Tesla Model S, and height is around 66" or 67".

A more advance way is to combine the three tables by doing the following:

SELECT COUNT(*) AS count_attendance,
event_name, name, height,
hair_color, gender, car_make, car_model
FROM facebook_event_checkin
JOIN person
ON facebook_event_checkin.person_id = person.id
JOIN drivers_license
ON drivers_license.id = person.license_id
WHERE 1=1
AND event_name = 'SQL Symphony Concert'
AND date LIKE '201712%'
AND hair_color = 'red'
AND car_make = 'Tesla'
AND gender = 'female'
GROUP BY person_id
HAVING count_attendance = 3

Result:

Is she really our mastermind?

Let's check against the solution table.

Yup, there goes our mastermind. Break out the champagne!

Random explorations

Our killer mentioned that the woman is rich, is that true? Let's check top 10 richest based on annual income.

SELECT income.ssn, name, annual_income
FROM income
JOIN person
ON income.ssn = person.ssn
ORDER BY annual_income DESC
LIMIT 10

Result:

She's not there. Let's filter for her.

SELECT income.ssn, name, annual_income FROM income
JOIN person
ON income.ssn = person.ssn
AND name = 'Miranda Priestly'

Result:

Also, Jeremy Bowers (killer) income:

Okay, probably not in the top 10 richest but still rich, especially if compared to context of Jeremy Bowers' annual income.

Hopefully, you're reading this after you've tried your skills against the game and checking how people have done their queries. If not, thank you for checking this out anyways.