SQL Murder Mystery Solution and Walkthrough

I recently heard of SQL Murder Mystery (https://mystery.knightlab.com/), a website that uses gamification to learn/practise SQL skills. There’s been a murder, and by searching the SQL database you can find out whodunit. Seemed like a fun challenge.

Here’s the prompt:

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.

As the challenge recommended, I used SQLiteStudio.

First step, search the crime_scene_report table to find the report, based on the date, type, and location:

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

We have two addresses. The person table has addresses:

SELECT *
FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1

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

Now we have two people. Let’s check what they have to say in the interview table:

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

Gym details! There are two gym-related tables. First, get_fit_now_member:

SELECT *
FROM get_fit_now_member
WHERE membership_status = 'gold' AND id LIKE '48Z%'

Two potential suspects. Who checked in on the 9th?

SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND membership_id LIKE '48Z%'

Both of them, so that doesn’t help. What about the plate info mentioned in one of the interviews?

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

The interview implies it’s a male, so still two options. This gives us their licence numbers though, which we can look up in person:

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

One of those names is one we’ve seen before in our initial list of suspects:

INSERT INTO solution VALUES (1, 'Jeremy Bowers');
SELECT value FROM solution;

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.

Let’s see what Jeremy had to say:

SELECT *
FROM interview
WHERE person_id = '67318'

Let’s look her up in the drviers_license table:

SELECT *
FROM drivers_license
WHERE gender = 'female' AND height BETWEEN '65' AND '67' AND hair_color is 'red' AND car_make = 'Tesla' AND car_model = 'Model S'

That gives us three possibilities. What about the event? Got to love social media:

SELECT person_id, COUNT(person_id)
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert' AND date LIKE '201712%'
GROUP BY person_id
HAVING COUNT(person_id) = 3

Two possibilities. Who are these people?

SELECT *
FROM person
WHERE id = '24556' OR id = '99716'

One of those licence IDs was one in the table above!

INSERT INTO solution VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;

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!

When posing the second challenge, it did ask to do it in no more than 2 queries. Initially, I didn’t. Why? Complex is not necessarily faster or better - I would prefer to do 10 small queries, learning through each, rather than taking the time to create and debug a more complicated one. That said, we can get the answer in one easily enough:

SELECT p.name
FROM drivers_license d
    JOIN person p ON d.id = p.license_id
    JOIN facebook_event_checkin f ON p.id = f.person_id
WHERE d.gender = 'female' AND d.height BETWEEN '65' AND '67' AND d.hair_color is 'red' AND d.car_make = 'Tesla' AND d.car_model = 'Model S' AND f.event_name = 'SQL Symphony Concert' AND f.date LIKE '201712%'
GROUP BY person_id
HAVING COUNT(person_id) = 3

So there we have it! Bad Miranda.

Image source: Wikipedia