SQL Murder Mystery Challenge (SQL)

The Challenge

To practice my SQL and problem-solving skills, I decided to try my hand at solving the whodunnit SQL Murder Mystery, which can be found at https://mystery.knightlab.com/.

There’s been a murder in SQL City and the lead detective needs help! The challenge is to use SQL skills to search the Murder Mystery database and determine who committed the crime. The only clues you have to start with is that there was a murder that occurred on Jan. 15, 2018 in SQL City.

The Data

The Murder Mystery database was built using SQLite and consists of 9 tables. The database schema can be found below.

The Solution

To complete the challenge, I wrote various SQL commands and queries into the web-based SQL “box” that looked like this:

I’ve included all my queries in the section below, as well as comments that detail my thought process.

First, I needed to see the murder crime scene reports from SQL City.

The result showed that there was a murder on January 15, 2018 and that there were two witnesses. So I started with the first witness on Northwestern Dr.

A search for all people living on Northwestern Dr resulted in a huge list! BUT there was one clue in the crime scene report that said the witness lived at the last house on the street, so I revised the query and received the following result.

My first witness was Morty Schapiro!

The second witness was more straightforward to find as the crime scene report included a name. The challenge here was using the LIKE command since I was only given her first name.

The second witness was Annabel Miller!

Now that I identified the witnesses, I searched the interview table to find their witness transcripts using Morty (14887) and Annabel’s (16371) person_id’s which I found from the previous queries.

Based off Morty’s interview, the suspect was a male with a gold membership to “Get Fit Now” Gym and his membership ID started with “48Z” – based off this transcript I made the following search that resulted in two names.

Now I had the names of two possible suspects. From here, I focused on the second part of Morty’s interview that included a portion of the license plate - below are the results.

Following the license plate search, I returned to the person table and wrote a query that filtered the table to just the two possible suspects. The result gave the license_id of both Joe and Jeremy, which I compared with the previous license plate search.

Jeremy Bowers was now my prime suspect!

From here, I turned my attention towards Annabel’s interview. Annabel said that she recognized the killer from when she was at the gym on January 9. My thinking was that if Jeremy Bowers was at the gym on that day, around the same time as Annabel, he must be the killer! First, I ran a query to retrieve Annabel’s gym id.

Then, I searched the check_in table with both Annabel and Jeremy’s gym ids on January 9.

Turns out both Annabel and Jeremy were at the gym on January 9th and they both check out at the same time! All signs from both Morty and Annabel’s witness interview point to Jeremy Bowers as the murderer!

I checked my solution and was right!

I tried the additional challenge to see who the real villain was next. I checked Jeremy’s interview transcript and below is what he had to say.

Jeremy’s transcript gave a good description of the woman who hired him. Using his interview, I wrote a more complex query which included multiple JOINs to query multiple tables at once. After applying all the descriptions from the transcript, all signs pointed to Miranda Priestly as the true villain that hired Jeremy!

A quick check of my solution showed that I had solved the mystery!

Conclusion and Lessons Learned

This challenge was a lot of fun and a great way to practice SQL and problem-solving skills! I successfully solved the murder using a variety of SQL commands. I’m sure there are plenty of other ways one could have arrived at the solution. Some of the steps could have been done more efficiently by using JOINs in place of running additional queries, however because I was keeping notes/screenshots as I was working through the challenge, I could easily reference previous query results to keep track of id numbers, names, etc. Overall, this was great practice and I would recommend it to anyone practicing SQL.

Please feel free to reach out to me for any questions, comments, or suggestions on other ways the mystery could’ve been solved. Thank you!