SQL Murder Mystery

Posted on Dec 18, 2019

This post will not be much of a read, just my “logbook” from solving the SQL Murder Mystery today.

Edit: Had to remove SQL results tables due to poor formatting of the theme. To see original “log” check out the gist.

SPOILER WARNING. This game was fun! I recommend it to any SQL amateur or pro. Give it a go at https://mystery.knightlab.com/.

There’s been a Murder in SQL City! The SQL Murder Mystery is designed to be both a self-directed lesson to learn SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.

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.

Crime report

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

Suspects

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

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

Suspects licenses

SELECT * FROM drivers_license WHERE id=490173 OR id=118009;

Suspects GetFitNow Membership

SELECT get_fit_now_member.id, person_id, get_fit_now_member.name, membership_start_date, membership_status, person.name
	FROM get_fit_now_member
	JOIN person ON person.id = person_id
	WHERE person.id=16371 OR person.id=14887;

Suspects GetFitNow CheckIn Times

SELECT * FROM get_fit_now_check_in WHERE membership_id = '90081';

Suspects Interviews

SELECT * FROM interview WHERE person_id=16371 OR person_id=14887;

Investigating transcripts

Morty Schapiro’s transcript

SELECT * FROM get_fit_now_member
	JOIN person ON person.id = person_id
	JOIN drivers_license ON drivers_license.id = person.license_id
	WHERE
		get_fit_now_member.id LIKE '48Z%' AND membership_status='gold' AND
		drivers_license.gender = 'male' AND drivers_license.plate_number LIKE '%H42W%';

SELECT * FROM get_fit_now_check_in
	WHERE membership_id = '48Z55';

SELECT * FROM interview
	WHERE person_id = 67318;

The plot thickens
We’ll track this mysterious woman later on

SELECT * FROM facebook_event_checkin
	WHERE person_id = 67318;

Annabel Miller’s transcript

In her transcript she mentiones seeing the killer at the Gym. From her entry we know she was indeed there on 9th Jan, between 16:00 and 17:00. This means the killer must have checked in before she left and the killer must have checked out after she left.

SELECT * FROM get_fit_now_check_in
	WHERE check_in_date=20180109
		AND check_in_time <  1700
		AND check_out_time > 1600;

We have to remember that 90081 is Annabel
Notice 48Z55 is Jeremy Bowers, the guy from Morty’s transcript
For 48Z7A there is just the one check-in

SELECT * FROM get_fit_now_member
	JOIN person ON person_id = person.id
	WHERE get_fit_now_member.id = '48Z7A';

SELECT * FROM facebook_event_checkin
	WHERE person_id = 28819;

This person attended no events

Jeremy Bowers’s

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.

From this statement we can extract a few things
- It’s a female
- 5'5" (65") or 5'7" (67")
- Hair color is red
- Car is Tesla Model S
- She attended SQL Symphony Concert 3, 3 times in December 2017

SELECT * FROM person
	JOIN  drivers_license ON person.license_id = drivers_license.id
	WHERE
		drivers_license.gender = 'female'  AND
		drivers_license.hair_color = 'red' AND
		(height >= 65 AND height <= 67)    AND
		car_make = 'Tesla' AND car_model='Model S';

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

Investigating Miranda Priestly

99716, Miranda Priestly is the only person matching the criteria
Will investigate the other points just to get an idea of this person

SELECT * FROM income WHERE ssn=987756388;

SELECT * FROM get_fit_now_member WHERE person_id = 99716;

Not a GetFitNow member

SELECT * FROM interview WHERE person_id = 99716;

Did not give an interview

Ending

Honestly not the ending I expected. I really hoped for this to go on for longer. I’ve put in Miranda Priestly and won.

INSERT INTO solution VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;