SQL Murder Mystery
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.
SELECT * FROM crime_scene_report WHERE type='murder' AND city='SQL City' AND date=20180115;
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;
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';
SELECT * FROM interview WHERE person_id=16371 OR person_id=14887;
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
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
48Z55is Jeremy Bowers, the guy from Morty’s transcript
48Z7Athere 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
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
- Hair color is
- Car is
- She attended
SQL Symphony Concert 3, 3 times in
- It’s a
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
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;