SQL Murder Mystery https://mystery.knightlab.com/

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!