SQL Murder Mystery

Can you find out whodunnit? | Link

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.

Starting Data

Crime : Murder
Occurred : Jan 15 2018
Location : SQL City

Tables

crime_scene_report
drivers_license
person
facebook_event_checkin
interview
get_fit_now_member
get_fit_now_check_in
income
solution

First Query

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

Result: 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”.

Second Query

SELECT *
FROM person
WHERE name LIKE "Annabel%" OR 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
16371	Annabel Miller	490173	103	Franklin Ave	318771143

Third Query

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

Result: 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”.

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.

Fourth Query

SELECT *
FROM get_fit_now_member
JOIN get_fit_now_check_in
ON membership_id = id
WHERE membership_status = "gold" 
AND check_in_date = 20180109

SELECT *
FROM drivers_license
WHERE plate_number LIKE "%H42W%"

SELECT *
FROM person
WHERE license_id = 183779 OR license_id = 423327 OR license_id = 664760

id	person_id	name	membership_start_date	membership_status	membership_id	check_in_date	check_in_time	check_out_time
XTE42	55662	Sarita	Bartosh	20170524	gold	XTE42	20180109	486
6LSTG	83186	Burton	Grippe	20170214	gold	6LSTG	20180109	399
GE5Q8	92736	Carmen	Dimick	20170618	gold	GE5Q8	20180109	367
48Z7A	28819	Joe	Germuska	20160305	gold	48Z7A	20180109	1600
48Z55	67318	Jeremy	Bowers	20160101	gold	48Z55	20180109	1530
90081	16371	Annabel	Miller	20160208	gold	90081	20180109	1600

id	name	license_id	address_number	address_street_name	ssn
51739	Tushar	Chandra	664760	312	Phi
67318	Jeremy	Bowers	423327	530	Washington
78193	Maxine	Whitely	183779	110	Fisk

Culprit: Jeremy Bowers!

Fifth Query - The real culprit!

SELECT *
FROM interview
WHERE person_id=67318

Transcript: 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 *
FROM drivers_license
JOIN person
ON drivers_license.id = license_id
JOIN facebook_event_checkin
ON person.id = person_id
WHERE car_make = "Tesla" AND gender = "female"
AND height BETWEEN 65 AND 67

Result: Miranda Priestly!