SQL Murder Mystery Game

I have been a gamer since I was 5, so learning things using games is a really fun way to learn for me. I have decided to improve my SQL knowledge. While I search, I have come across with multiple games. I have chosen the Murder Mystery Game, and I have enjoyed playing it, so for beginners it can be a great tutorial to follow. I hope you enjoy the game and solving it with me. Cheers!

Murder Mystery

Solution of the SQL Murder Mystery Game

You can play it on your own from the creators of this game. Big shout out to The Northwestern University Knight Lab for creating a fun way of learning SQL.

We have a table like this.

Table For Mystery Game

We read this description first about the murder. Then get into code to find the first clue.

Our information is about crime scene report, we have date, type, and city. Let’s write the code for this table. I wrote this code after examining the data in the table.

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

Result:

Only one record exists. This is our next hint. We will examine the witnesses.

Let’s start with the first witness

Our witness lives at last house Northwestern Dr. Last house means the biggest “address number” in “Northwestern Dr.” street, so I wrote the code like this.

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

Result:

Let’s continue with second witness

Our witness named Annabel and lives in Franklin Ave. We have a name and a address street name.

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

Result:

We have found our witnesses, we will examine their interview. To find out more information about the murder.

I have written the code using their person_id. Their id corresponds to person_id in the interview table.

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

Result:

We are so close to solution, we should only combine the information that we had.

I have written this code using first witness’ two information

select * 
from get_fit_now_member
where membership_status = 'gold'
and id like '%48Z%'

I will get results from there and according to results I will use drivers’ license table since I have information about plate.

I have reached to “person” table from “get fit now member” table.

select * 
from get_fit_now_member
INNER JOIN person ON get_fit_now_member.person_id = person.id
where get_fit_now_member.membership_status = 'gold'
and get_fit_now_member.id like '%48Z%'

Result:

We have two suspects

Now the last step is reaching the “driver's license” table.

select * 
from get_fit_now_member
INNER JOIN person ON get_fit_now_member.person_id = person.id
INNER JOIN drivers_license ON person.license_id = drivers_license.id
where get_fit_now_member.membership_status = 'gold'
and get_fit_now_member.id like '%48Z%'
and drivers_license.plate_number like '%H42W%'

Result:

We have found our murderer, but for educational purposes, let’s continue.

When I wrote this query, I get only one result, but to be sure, we will add details coming from second witness.

I have extended the code like this and only returned the related information of the suspect. I had duplicate records, and I have removed them using DISTINCT.

select get_fit_now_member.person_id, get_fit_now_member.name, person.ssn, person.address_number, person.address_street_name
from get_fit_now_member
INNER JOIN person ON get_fit_now_member.person_id = person.id
INNER JOIN drivers_license ON person.license_id = drivers_license.id
INNER JOIN get_fit_now_check_in ON get_fit_now_check_in.check_in_date = '20180109'
where get_fit_now_member.membership_status = 'gold'
and get_fit_now_member.id like '%48Z%'
and drivers_license.plate_number like '%H42W%'

Result:

select distinct get_fit_now_member.person_id, get_fit_now_member.name, person.ssn, person.address_number, person.address_street_name
from get_fit_now_member
INNER JOIN person ON get_fit_now_member.person_id = person.id
INNER JOIN drivers_license ON person.license_id = drivers_license.id
INNER JOIN get_fit_now_check_in ON get_fit_now_check_in.check_in_date = '20180109'
where get_fit_now_member.membership_status = 'gold'
and get_fit_now_member.id like '%48Z%'
and drivers_license.plate_number like '%H42W%'

Result:

That’s all. Let’s control our answer from the website.

Adventure calls us. Let’s deep dive into the case more 😊

I will examine the interview process to find out more information

select *
from interview
where person_id = '67318'

Result:

It seems that we have an instigator.

I will look into income table for money range first.

select *
from income
order by annual_income desc

Result:

I could not find something useful here but I know the upper bound at least!

I have decided to use “drivers license” table

select *
from drivers_license
WHERE height BETWEEN 65 AND 67
and car_make = 'Tesla'
and car_model = 'Model S'
and gender = 'female'
and hair_color = 'red'

Result:

We have found our 3 suspects. We will use the remaining two information to find real instigator.

I have added SQL Symphony Concert information to the query

select distinct *
from drivers_license
INNER JOIN facebook_event_checkin on facebook_event_checkin.event_name = 'SQL Symphony Concert' and facebook_event_checkin.date like '201712%'
WHERE height BETWEEN 65 AND 67
and drivers_license.car_make = 'Tesla'
and drivers_license.car_model = 'Model S'
and drivers_license.gender = 'female'
and drivers_license.hair_color = 'red'

But, still we could not reach the end. It continues with lots of records.

I should try connecting them using person and income tables.

select distinct *
from drivers_license
INNER JOIN facebook_event_checkin on facebook_event_checkin.event_name = 'SQL Symphony Concert' and facebook_event_checkin.date like '201712%'
INNER JOIN person on facebook_event_checkin.person_id = [person.id](<http://person.id/>) and drivers_license.id = person.license_id
INNER JOIN income on income.ssn = person.ssn
WHERE height BETWEEN 65 AND 67
and drivers_license.car_make = 'Tesla'
and drivers_license.car_model = 'Model S'
and drivers_license.gender = 'female'
and drivers_license.hair_color = 'red'

When I add person and income tables our suspects down to 1 so we found our instigator 😊.

I have arranged the query a bit to reach related information.

select distinct person.id, person.name, person.ssn, income.annual_income, person.address_number, person.address_street_name
from drivers_license
INNER JOIN facebook_event_checkin on facebook_event_checkin.event_name = 'SQL Symphony Concert' and facebook_event_checkin.date like '201712%'
INNER JOIN person on facebook_event_checkin.person_id = [person.id](<http://person.id/>) and drivers_license.id = person.license_id
INNER JOIN income on income.ssn = person.ssn
WHERE height BETWEEN 65 AND 67
and drivers_license.car_make = 'Tesla'
and drivers_license.car_model = 'Model S'
and drivers_license.gender = 'female'
and drivers_license.hair_color = 'red'

Result:

Let’s check our result

Finally, we have reached the instigator. The rest is up to SQL City justice 😊

I hope you like my post. If you have any suggestions or comments, please feel free to contact me via email.