Let’s try to solve a murder mystery with SQL
Hey guys ! My classes officially ended last week. I have started my job search since a month ago and let’s just say the journey wasn’t quite a smooth ride.
Anyhoo, it’s not something within my control for now. But what I can do is brushing up my skills ! I have learn SQL during my course through SAS but it’s been a while since I used it. While searching for some refreshers I came across this ! A SQL Murder Mystery created by knight lab. It’s a pretty fun way to learn SQL I would say. So let’s get started and solve this together!
First of all, we were given a clue that “the crime was a murder that occurred sometime on Jan.15, 2018 and that it took place in SQL City” . And the ERD for the database was given per following.
Let’s have a look about this case under crime_scene_report.
SELECT * FROM crime_scene_report
WHERE type = ‘murder’
AND city = ‘SQL City’;
Ta-da ! We have our clue for 2 witnesses based on the 3rd observation. Let’s try to find out more details about our 2 witnesses.
SELECT * FROM person
WHERE address_street_name LIKE LOWER(“%Northwestern%”)
ORDER BY address_number DESC
LIMIT 1;
SELECT * FROM person
WHERE address_street_name LIKE LOWER (“%franklin%”) AND
name LIKE LOWER(“%Annabel%”)
LIMIT 1;
Alright ! Let’s hope both Morty and Annabel can give us some clues through their interview.
SELECT name, transcript
FROM Interview
Join person
ON Interview.person_id = person.id
WHERE person.id = 16371 OR person.id = 14887
Hmm. There’s a couple information we can gather from here. The culprit is a man, with a gold membership from “Get Fit Now Gym”. His membership ID starts with “48Z” and car plate number includes “H42W”. He was last seen at the gym on Jan 9th.
Where do we start? Let’s start with the plate number reference.
SELECT * FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”)
hmm. So we have 3 suspects based on car plate information. With the license id here, we should be able to gather the information from person table.
SELECT * FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”))
Aha ! Our suspects. The person id can be helpful to find out which person has a gold membership at the gym.
SELECT * FROM get_fit_now_member
WHERE person_id IN (SELECT id FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”)))
And… we are down with 1 suspect. Let’s see if Jeremy match the rest of the clues.
SELECT * FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND
membership_id IN (SELECT id FROM get_fit_now_member
WHERE person_id IN (SELECT id FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”))))
9th January check in date. Jeremy seems to be our guy.
Let’s back trace a little to ensure that the membership id indeed belongs to Jeremy.
SELECT * FROM get_fit_now_member
WHERE id IN
(SELECT membership_id FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND
membership_id IN (SELECT id FROM get_fit_now_member
WHERE person_id IN (SELECT id FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”)))))
Alright ! Seems like Jeremy really is our guy. But wait ! Jeremy just confessed there is someone behind this whole murder mystery. And he’s not the real culprit?
What???
Let’s see what Jeremy said in his interview.
SELECT * from interview
WHERE person_id =
(SELECT person_id FROM get_fit_now_member
WHERE id IN
(SELECT membership_id FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND
membership_id IN (SELECT id FROM get_fit_now_member
WHERE person_id IN (SELECT id FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE plate_number LIKE LOWER(“%H42W%”))))))
Oh no ! Apparently there’s a rich woman behind this scheme! She’s around 65" or 67", with red hair, drives a Tesla Model S and attended SQL Symphony Concert thrice back in Dec 2017.
Let’s hunt her down guys !
According to the driver license table, we should be able to cut down the number of suspects.
SELECT * FROM drivers_license
WHERE hair_color = ‘red’ AND
car_make = “Tesla” AND
car_model = “Model S” AND
gender = “female”
There, there. 3 suspects we have here. Next, let’s find out who has attended the SQL symphony in Dec 2017 thrice!
SELECT person_id, COUNT(person_id)
FROM facebook_event_checkin
WHERE event_name LIKE “%Symphony%” AND
date LIKE “201712%”
GROUP BY person_id
HAVING COUNT(person_id) = 3
ORDER BY COUNT(person_id) DESC
LIMIT 5
Alright ! We are down to 2 suspects. Let’s link the driver information and this up !
SELECT * FROM person
WHERE license_id IN (SELECT id FROM drivers_license
WHERE hair_color = ‘red’ AND
car_make = “Tesla” AND
car_model = “Model S” AND
gender = “female”) AND
id IN (SELECT person_id
FROM facebook_event_checkin
WHERE event_name LIKE “%Symphony%” AND
date LIKE “201712%”
GROUP BY person_id
HAVING COUNT(person_id) = 3)
There she is ! Miranda is the mastermind behind the murder mystery :)
Did you have fun? I certainly did! Do you know if there’s anything similar for R or Python ? Would love to have some fun while refreshing those as well !
