SQL Murder Mystery

You can try it yourself here

Overview

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

1. Crime description

select description from crime_scene_report where type = 'murder' and city = 'SQL City' and date = 20180115

Answer

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

2. Witnesses

a. Person on Northwestern Dr

select * from person where address_street_name = 'Northwestern Dr' order by address_number desc limit 1

Answer

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

b. Annabel

select * from person where name like '%Annabel%' and address_street_name = 'Franklin Ave'

Answer

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

3. Testimonies

select interview.transcript, person.name from interview inner join person on person.id = interview.person_id where person_id in (14887, 16371)

Answer

transcript	name
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".	Morty Schapiro
I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.	Annabel Miller

4. Gym Memberships

select m.person_id, m.name, ci.check_in_date, ci.check_in_time, ci.check_out_time
from get_fit_now_member m
join get_fit_now_check_in ci
on m.id = ci.membership_id
where m.id like '48Z%' 
and m.membership_status = 'gold'
and ci.check_in_date = 20180109

Answer

person_id	name	check_in_date	check_in_time	check_out_time
28819	Joe Germuska	20180109	1600	1730
67318	Jeremy Bowers	20180109	1530	1700

5. Car Plate Number

select person.name from drivers_license
join person on drivers_license.id = person.license_id
where person.id in (28819, 67318)
and drivers_license.plate_number like '%H42W%'

Answer

Jeremy Bowers

The Real Villain

Congrats, you found the murderer! But wait, there's more... If you think you're up for a challenge, try querying the interview transcript of the murderer to find the real villain behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement with your new suspect to check your answer.

SQL 1

      select transcript from interview 
      join person
      on interview.person_id = person.id
      where person.name = 'Jeremy Bowers'
    

Answer

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.

SQL 2

      select person.name, count(*) as concert_attendance
      from person
      join drivers_license on drivers_license.id = person.license_id
      join facebook_event_checkin on facebook_event_checkin.person_id = person.id
      where drivers_license.height between 65 and 67
        and drivers_license.hair_color = 'red'
        and drivers_license.car_make = 'Tesla'
        and drivers_license.car_model = 'Model S'
        and facebook_event_checkin.event_name = 'SQL Symphony Concert'
        and facebook_event_checkin.date between '20171201' AND '20171231'
      group by person.name
      having count(*) = 3;
    

Answer

Miranda Priestly

Congrats, you found the brains behind the murder! Everyone in SQL City hails you as the greatest SQL detective of all time. Time to break out the champagne!