Skip to content

Instantly share code, notes, and snippets.

@cpl

cpl/logbook.md

Last active December 18, 2019 15:51
Show Gist options
  • Save cpl/17943377f49d266ff4c8649fc5752adc to your computer and use it in GitHub Desktop.
Save cpl/17943377f49d266ff4c8649fc5752adc to your computer and use it in GitHub Desktop.
Download ZIP
SQL Murder Mystery https://mystery.knightlab.com/
Raw
logbook.md

SQL Murder Mystery

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;
date type description city
20180115 murder 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". SQL City

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;
id name license_id address_number address_street_name ssn
16371 Annabel Miller 490173 103 Franklin Ave 318771143
14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949

Suspects licenses

SELECT * FROM drivers_license WHERE id=490173 OR id=118009;
id age height eye_color hair_color gender plate_number car_make car_model
118009 64 84 blue white male 00NU00 Mercedes-Benz E-Class
490173 35 65 green brown female 23AM98 Toyota Yaris

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;
id person_id name membership_start_date membership_status name
90081 16371 Annabel Miller 20160208 gold Annabel Miller

Suspects GetFitNow CheckIn Times

SELECT * FROM get_fit_now_check_in WHERE membership_id = '90081';
membership_id check_in_date check_in_time check_out_time
90081 20180109 1600 1700

Suspects Interviews

SELECT * FROM interview WHERE person_id=16371 OR person_id=14887;
name transcript
Morty Schapiro 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".
Annabel Miller I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

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%';
id person_id name membership_start_date membership_status id name license_id address_number address_street_name ssn id age height eye_color hair_color gender plate_number car_make car_model
48Z55 67318 Jeremy Bowers 20160101 gold 67318 Jeremy Bowers 423327 530 Washington Pl, Apt 3A 871539279 423327 30 70 brown brown male 0H42W2 Chevrolet Spark LS 
SELECT * FROM get_fit_now_check_in
	WHERE membership_id = '48Z55';
membership_id check_in_date check_in_time check_out_time
48Z55 20180109 1530 1700 
SELECT * FROM interview
	WHERE person_id = 67318;
person_id transcript
67318 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.
  • The plot thickens
  • We'll track this mysterious woman later on
SELECT * FROM facebook_event_checkin
	WHERE person_id = 67318;

| person_id | event_id | event_name | date | | 67318 | 4719 | The Funky Grooves Tour| 20180115 | | 67318 | 1143 | SQL Symphony Concert | 20171206 |

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;
membership_id check_in_date check_in_time check_out_time
48Z7A 20180109 1600 1730
48Z55 20180109 1530 1700
90081 20180109 1600 1700
  • 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';
id person_id name membership_start_date membership_status id name license_id address_number address_street_name ssn
48Z7A 28819 Joe Germuska 20160305 gold 28819 Joe Germuska 173289 111 Fisk Rd 138909730 
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';
id name license_id address_number address_street_name ssn id age height eye_color hair_color gender plate_number car_make car_model
78881 Red Korb 918773 107 Camerata Dr 961388910 918773 48 65 black red female 917UU3 Tesla Model S
90700 Regina George 291182 332 Maple Ave 337169072 291182 65 66 blue red female 08CM64 Tesla Model S
99716 Miranda Priestly 202298 1883 Golden Ave 987756388 202298 68 66 green red female 500123 Tesla Model S 
SELECT * FROM facebook_event_checkin
	WHERE person_id IN (78881, 90700, 99716);
person_id event_id event_name date
99716 1143 SQL Symphony Concert 20171206
99716 1143 SQL Symphony Concert 20171212
99716 1143 SQL Symphony Concert 20171229

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;
ssn annual_income
987756388 310000 
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;
value
Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment