SQL Murder Mystery
Northwestern's Knight Labs has a fun little diversion for practicing your SQL commands.
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.
You can check it out for yourself here: SQL Murder Mystery.
Spoiler alert, here's how my experience went below:
Alright, I know I want to look at the crime_scene_reports, but looking at the scheme table doesn't tell me what the date format is so I'll check first:
select * from crime_scene_report limit 1
date type description city
20180115 robbery A Man Dressed as Spider-Man Is on a Robbery Spree NYC
Alright, looks like yyymmdd, so let's grab our crime scene report:
select description from crime_scene_report where type is 'murder'
and city is 'SQL City' and date is '20180115'
description
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".
select \* from person
where name like 'Annabel%'
and address_street_name like 'Franklin%'
id name license_id address_number address_street_name ssn
16371 Annabel Miller 490173 103 Franklin Ave 318771143
If it were real life, I'd probably try both asc and desc for the "last house."
select \* from person
where address_street_name is 'Northwestern Dr'
order by address_number desc
limit 1
id name license_id address_number address_street_name ssn
14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949
I tried Morty's interview first and never actually went back to Annabel's (turns out she didn't have one).
select transcript from interview where person_id is 14887
transcript
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".
Alright, another basic command here:
select name from get_fit_now_member
where id like '48Z%'
and membership_status is 'gold'
name
Joe Germuska
Jeremy Bowers
Well now, not narrowed down yet. Finally we get to use some joins!
select get_fit_now_member.name from get_fit_now_member
join person on get_fit_now_member.person_id=person.id
join drivers_license on person.license_id=drivers_license.id
where get_fit_now_member.id like '48Z%' and get_fit_now_member.membership_status is 'gold'
and drivers_license.plate_number like '%H42W%'
name
Jeremy Bowers
There's our perp!
The exercise goes on and asks you to find out the mastermind behind it, let's look to see if our perp had an interview:
select transcript from interview where person_id is 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.
Alright now, challenge! Let's try to do this in a single shot. We're going to have to join our person and drivers_license tables with the facebook_event_checkin table.
select name, facebook_event_checkin.event_name, facebook_event_checkin.date
from person
join drivers_license on person.license_id=drivers_license.id
join facebook_event_checkin on facebook_event_checkin.person_id=person.id
where facebook_event_checkin.event_name like '%symphony%'
and drivers_license.car_make is 'Tesla'
and drivers_license.car_model is 'Model S'
and drivers_license.hair_color is 'red'
and drivers_license.gender is 'female'
name event_name date
Miranda Priestly SQL Symphony Concert 20171206
Miranda Priestly SQL Symphony Concert 20171212
Miranda Priestly SQL Symphony Concert 20171229
There's our three visits in 2017!