Cracking the SQL Murder Mystery: A Step-by-Step Solution

Hey SQL enthusiasts! I recently stumbled upon the intriguing SQL Murder Mystery online game, where SQL isn’t just about SELECT statements — it’s about unleashing your inner detective, armed with SQL, critical and creative thinking skills. No dark alleys here, just relational databases and thrilling clues. 🤓

Joon Park and Cathy He, the intelligent minds behind this game, crafted it during their time as Knight Lab fellows. In this article, join me on the exciting journey of solving the mystery as we dive into queries and “Aha!” moments.

If you’re itching to solve along, go to SQL Murder Mystery for the web version by Joe Germuska 💻🕶️.

The Brief
“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.”

Step 1: Delving into the Police Department’s Database Mastery

To kick off our investigation, I familiarized myself with the police department’s database. I executed the following query to retrieve the names of tables in the database:

To gain a deeper understanding of how the tables are connected, I took the time to study the schema. Check out the image below for a visual representation of the database schema:

This initial exploration laid the groundwork for navigating through the database in our quest to solve the mystery.

Step 2: Unraveling the Crime Scene Details

With crucial information about the crime type, date, and location in hand, I executed the following query to extract detailed insights from the crime_scene_report table:

The investigation reveals that two witnesses were present during the crime. However, the details of the witnesses are sparse at this point — Witness 1 resides at the last house on Northwestern Dr, while Witness 2, Annabel, lives on Franklin Avenue. Our next move? Dive deeper into the lives of these witnesses to propel our investigation forward.

Step 3: Unveiling Witness Insights

In our quest for valuable information about our two key witnesses, I devised two main conditions utilizing the power of subqueries.

In Condition 1, the aim was to fetch records related to Witness 2 or any closely associated records. This condition hinges on two criteria: the name column containing “Annabel” and the street name aligning with a subquery. The subquery extracts street names from the “person” table where the street name resembles “Frank%.”

Meanwhile, Condition 2 focuses on retrieving records related to Witness 1. This condition zeroes in on street names resembling “%North%” and the last house number (MAX ( )function) is used here to produce the last house number) corresponding to street addresses resembling “Northwest%.”

Let’s take a peek at the SQL magic and the result:

Our witnesses are unveiled! Witness 1 is Morty Shapiro and Witness 2 is Annabel Miller, with IDs 14887 and 16371, respectively.

Now, for those new to the world of SQL, I understand the potential head-spinning effect of subqueries. Fear not! I’ve got you covered with simpler optional queries below to ease your journey into witness details:

Now embark on this investigative adventure with confidence! 😊

Step 4: Decrypting Witness Testimonies

Let’s delve into the minds of our witnesses by retrieving and analyzing their interview transcripts. Using the IDs 14887 and 16371, we aim to extract insights from the “interview” table.

This query amalgamates information from the “person” and “interview” tables, connecting them based on the common column id in the “person” table and person_id in the “interview” table. The result provides us with a comprehensive understanding of our witnesses’ accounts.

Excitingly, our pool of information deepens! Annabel vividly recalls the crime transpiring on January 9, 2018, and points to a fellow gym-goer as the potential perpetrator.

On the flip side, Morty Shapiro’s testimony is equally compelling. He narrates an auditory experience of a gunshot, swiftly followed by a visual encounter with an individual carrying a distinctive “Get Fit Now Gym” bag. What’s more intriguing is the gym membership number on the bag, commencing with the enigmatic “48Z.” According to Morty, the mysterious figure swiftly fled the crime scene in a getaway vehicle with a license plate featuring “H42W.”

Fingers crossed as the pieces of the puzzle start falling into place! 🕵️‍♂️

Step 5: Navigating the Crime Trail — Suspect Identification

Armed with crucial details from the witnesses, including the crime date, killer’s gender, gym membership, and car plate number, we embark on a targeted query to track down the elusive suspect. The common thread in the witness statements is the gym, making it a focal point for our investigation.

Voila! We now have two prime suspects in our sights: Joe Germuska and Jeremy Bowers, both holding the prestigious status of gold members. The plot thickens as we zero in on these individuals, scrutinizing their every move.

Step 6: Gym Alibis Under Scrutiny

In our pursuit of truth, we turn our attention to the gym records to discern the whereabouts of our suspects on the fateful day of the crime. The query below aims to retrieve crucial details about check-ins at the fitness centre, encompassing membership_id, membership status, member name, check-in date, check-in time, and check-out time on the date the crime occurred.

The outcome, unfortunately, yields no data. This leaves us at a crossroads — either both suspects were absent from the gym on that specific date, or… there’s a twist waiting to be uncovered. Our investigative instincts tell us there’s more to explore. Let’s plunge deeper into the digital breadcrumbs and unveil the final piece of this enigmatic puzzle.

Step 7: Connecting the Dots — Suspect Showdown

In this pivotal step, we amalgamate comprehensive details about our suspects, Jeremy and Joe, leveraging their membership IDs, names, and associated license plate numbers to unravel the truth.

Gotcha! Jeremy Bowers, it seems the jig is up.

The result points to Jeremy Bowers, with a plate number that aligns seamlessly with the one disclosed in Morty’s testimony.

So, Jeremy, care to explain why your plate number matches the getaway vehicle mentioned in the crime scene? The game is afoot, and we’re closing in on the elusive.

Let’s confirm the solution 👇

Unmasking the Mastermind — Jeremy’s Revelation

Step 8: Unveiling the Puppet Master — Insights from Jeremy’s Testimony
Only Jeremy can lead us to the brain behind the murder! In our relentless pursuit of the truth, we now shift our focus to Jeremy Bowers, the prime suspect. A detailed review of Jeremy’s interview transcript unfolds a trove of information that could lead us to the orchestrator of this intricate crime.

Hold onto your seats! We’ve struck gold in Jeremy’s revelations:

• Jeremy discloses that he was hired by a woman of substantial financial means.
• Though unaware of her name, Jeremy provides a vivid physical description. The mysterious woman stands at around 5'5" (65") or 5'7" (67"), boasts red hair, and drives a Tesla Model S.
• Adding another layer to the mystery, the woman attended the SQL Symphony Concert three times in December 2017.

This revelation transforms our investigation, providing vital insights into the potential orchestrator of this intricate crime. The detailed physical description and specific details about her attendance at the SQL Symphony Concert now stand as key elements in unmasking and locating this enigmatic woman. The final pieces of the puzzle are falling into place, and the climax of our investigation draws near.

Step 9: A Confluence of Data — Miranda Priestly Emerges

In a masterful stroke of cross-referencing, we combine key information from Jeremy’s confession across multiple tables. The query orchestrates a symphony involving the “person,” “drivers_license,” “income,” and “facebook_event_checkin” tables, unveiling detailed insights about individuals meeting specific criteria laid out in Jeremy’s confession.

Behold the convergence:

We’ve zeroed in on a match, and the spotlight shines on Miranda Priestly.

The alignment of Miranda’s attendance at the SQL Symphony Concert and the detailed description provided by Jeremy Bowers creates a compelling narrative. Finally, the puzzle pieces interlock, and Miranda Priestly emerges as a key figure in our investigation.

Let’s confirm that Miranda Priestly is the right answer👇

Mission accomplished, we found the mastermind.