SQL Murder Mystery (Solution)

This is the walkthrough of my solution to SQL Murder Mystery.

Situation

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.

---
title: SQL Murder Mystery Database ERD
---
erDiagram
	interview {
		int person_id FK
		string transcript
	}
	get_fit_now_check_in {
		string membership_id PK
		int check_in_date
		int check_in_time
		int check_out_time
	}
	get_fit_now_member {
		string id PK
		int person_id FK
		string name
		int membership_start_date
		int membership_status
	}
	person {
		int id PK
		string name
		int license_id FK
		int address_number
		string address_street_name
		string ssn FK
	}
	drivers_license {
		int id PK
		int age
		int height
		string eye_color
		string hair_color
		string gender
		string plate_number
		string car_make
		string car_model
	}
	income {
		string ssn PK
		int annual_income
	}
	facebook_event_checkin {
		int person_id FK
		int event_id
		string event_name
		int date
	}
	crime_scene_report {
		int date
		string type
		string description
		string city
	}
	solution {
		int user
		string value
	}
	get_fit_now_member 1 to 1 person : is
	person 1 to 1 drivers_license : has
	person 1 to 1 income : has
	person 1 to zero or many interview : conducted
	person 1 to 1 facebook_event_checkin : has
	get_fit_now_member 1 to one or many get_fit_now_check_in : has

The witnesses

To repeat, the murder happened in Jan. 15, 2018 in SQL City. So we want to get all of the murder reports from crime_scene_report table where the value of city is 'SQL City'. We still don’t know the format of date in the table since the schema says it’s just an INT.

SELECT * FROM crime_scene_report
WHERE city = 'SQL City' AND type = 'murder'

date	type	description	city
20180215	murder	REDACTED REDACTED REDACTED	SQL City
20180215	murder	Someone killed the guard! He took an arrow to the knee!	SQL City
…	…	…	…

Knowing that YYYYMMDD is the date format, we can finally find all the reports during the said date:

SELECT * FROM crim_scene_report
WHERE 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

We can find the two witnesses from person table using the provided info. Assuming that the order of houses were based on their address_number, we can find the first witness by filtering the results with only those who live in Northwestern Dr and sort the results via address_number in descending order.

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

Finding the second witness is easy since the first name and address are already provided.

SELECT * FROM person
WHERE name LIKE 'Annabel%' AND address_street_name = 'Franklin Ave'

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

Knowing the id of two witnesses, we can check if they have history of interviews in interview table.

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

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

Based from these interview transcripts:

• We can find who checked-in during Jan. 9 from get_fit_now_check_in with membership id that starts with 48Z.
• We can JOIN get_fit_now_member and person via person ID and find who has license plate that includes H42W.

SELECT get_fit_now_member.id, get_fit_now_member.name, get_fit_now_member.person_id, get_fit_now_check_in.check_in_date
FROM get_fit_now_member
JOIN get_fit_now_check_in ON get_fit_now_member.id = get_fit_now_check_in.membership_id
WHERE id LIKE '48Z%' AND get_fit_now_check_in.check_in_date = 20180109

…and we have two possible suspects:

id	name	person_id	check_in_date
48Z7A	Joe Germuska	28819	20180109
48Z55	Jeremy Bowers	67318	20180109

SELECT person.id, person.name, drivers_license.plate_number
FROM person
JOIN drivers_license ON person.license_id = drivers_license.id
WHERE drivers_license.plate_number
LIKE '%H42W%'
AND person.id = 28819 OR person.id = 67318