Murder mystery.md

Solving the mystery from https://mystery.knightlab.com/

Stop reading now if you don't want spoilers!

.

.

.

.

.

.

.

.

.

.

Crime scene report

SELECT * FROM crime_scene_report WHERE date = "20180115" AND city = "SQL City"

date	crime	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

Witnesses

Annabel

SELECT * FROM person WHERE name LIKE "Annabel %"

id	name	license_id	address_number	address_street_name	ssn
16371	Annabel Miller	490173	103	Franklin Ave	318771143

SELECT * FROM interview WHERE person_id = "16371"

person_id	transcript
16371	I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

Morty

SELECT * FROM person WHERE address_street_name = "Northwestern Dr" ORDER BY address_number DESC

id	name	license_id	address_number	address_street_name	ssn
14887	Morty Schapiro	118009	4919	Northwestern Dr	111564949

SELECT * FROM interview WHERE person_id = "14887"

person_id	transcript
14887	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".

Info from witness statements

• Gold member of Get Fit Now
• Get Fit Now membership number starts with 48Z
• License plate includes H42W
• Was at the gym on 9 January 2018
• Victim was shot

Suspects

48Z, Gold membership, was at gym

SELECT *
FROM get_fit_now_check_in, get_fit_now_member
WHERE get_fit_now_check_in.membership_id = get_fit_now_member.id
AND check_in_date = '20180109'
AND get_fit_now_member.id LIKE '48Z%'              
AND get_fit_now_member.membership_status = 'gold'

membership_id	check_in_date	check_in_time	check_out_time	id	person_id	name	membership_start_date	membership_status
48Z7A	20180109	1600	1730	48Z7A	28819	Joe Germuska	20160305	gold
48Z55	20180109	1530	1700	48Z55	67318	Jeremy Bowers	20160101	gold

License plate

SELECT *
FROM drivers_license, person
WHERE person.license_id = drivers_license.id
AND drivers_license.plate_number LIKE '%H42W%'

id	age	height	eye_color	hair_color	gender	plate_number	car_make car_model	id	name	license_id	address_number	address_street_name	ssn
664760	21	71	black	black	male	4H42WR	Nissan Altima	51739	Tushar Chandra	664760	312	Phi St	137882671
423327	30	70	brown	brown	male	0H42W2	Chevrolet Spark LS	67318	Jeremy Bowers	423327	530	Washington Pl, Apt 3A	871539279
183779	21	65	blue	blonde	female	H42W0X	Toyota Prius	78193	Maxine Whitely	183779	110	Fisk Rd	137882671

In summary

Suspect	Gold member	Gym number	License plate	Was at gym
Joe Germuska	X	X		X
Jeremy Bowers	X	X	X	X
Tushar Chandra			X
Maxine Whitely			X

The plot thickens!

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.

SELECT person.id, person.name, person.license_id, 
	drivers_license.id, 
	facebook_event_checkin.person_id, facebook_event_checkin.event_name
FROM person, drivers_license, facebook_event_checkin
WHERE drivers_license.id = person.license_id
AND facebook_event_checkin.person_id = person.id
AND drivers_license.hair_color = 'red'
AND drivers_license.car_make = 'Tesla'
AND drivers_license.car_model = 'Model S'
AND drivers_license.gender = 'female'
AND facebook_event_checkin.event_name = 'SQL Symphony Concert'

id	name	license_id	id	person_id	event_name
99716	Miranda Priestly	202298	202298	99716	SQL Symphony Concert
99716	Miranda Priestly	202298	202298	99716	SQL Symphony Concert
99716	Miranda Priestly	202298	202298	99716	SQL Symphony Concert