SQL Murder Mystery Walk Through

SQL can be one of those coding languages that is fun to explore and manipulate data by querying different databases and tables. In this tutorial I will walk you through the SQL Murder Mystery game that can be found here.

First things first we want to understand the data we are working with and how the schema works.

Schema Overview

Looking here we see quite a few tables. One where we will start is the crime_scene_report Table. This will give us information about specific crimes that have happened in different cities.

get_fit_now_member: Seems to be a gym or membership type business with different metrics about a specific member. From this table we get a few different connections to other tables

get_fit_now_check_in: Seems to be event data pertaining to the check in and check out times for the Get Fit center.

person: We have a detailed person table where we can see the person's name, license number, address, and ssn. Connecting by ID and SSN to drivers_license and income will let us dive a little deeper into the person data.

facebook_event_checkin: will give us some data pertaining to Facebook events and show us which person has gone to which events.

Getting Started

Our objective is to find out the murderer from Jan.15th 2018 in SQL City.

First, we would want to utilize the crime report table to see what murders had occurred on that date in SQL city. That query would look a little something like this.

We see that there was 2 witnesses, one who lives at the end of Northwestern Dr and a second named Anabel who lives on Franklin Ave.

Lets investigate the first by looking on Northwestern Ave for the descending house numbers to find the home that is at the end. This data can be found in the person dataset

We find our first witness is Morty Schapiro. Second we will find an Annabel who lives on Franklin Ave

Not knowing Annabel's first or last name, we can utilize the LIKE function with % to search both sides of the word Annabel and get any results that has the word Annabel in it

We find our second witness Annabel Miller.

Knowing our two witnesses now, we will use our detective instincts to answer some key questions. If they both witnessed the murder, they must have been at the same location the night of the murder, knowing the event, we can find everyone who was there and narrow down our search to just those people.

Lets find that event now. with both person_id's being in the person table and events table we will like both of our witness's person_id's in a IN function to search the events on that date.

We find out both of them were at The Funky Grooves Tour and that is the location of the murder. What else we would want to know is their interview with the cops. Being witnesses this may help us get more information on that night.

With this information we get a few good directions. We see that the suspect is a male, workouts out at Get Fit Now, membership starts with 48z, car plate includes H42W ands was there at the gym on the 9th.

So lets start with the membership and see if we can narrow down names. Utilizing the previous method with the % sign we can attach that to he membership to narrow down to Gold members. Along with searching just for the 9th.

Now having the only two gold members who checked in on the 9th we can search both of those ID's into the member datatable

We have now narrowed down our searches to only two names. Working our way across the schema, we will plug these person_id's into the persons table to see more information on them.

Our main goal here is to find their license plate numbers because from our witnesses we know it starts with H42W. so now with their license-id's we will plug those into the drivers_license table.

Our results gives us the top one being Jeremy Bowers who drives a Chevy with the license that matches what we're looking for. So now we have found our guy! Lets go see what his interview has to say and his guilty verdict so we can plug him into the solution!

WHAT A TWIST. Let's get deeper down the rabbit hole and find out who bribed him into murder! So we don't know her name but we do know that shes been to the SQL Symphony concert 3 times in December 2017 and that she has red Hair and Drives a Tesla Model S.

So lets start with the Symphony concert and what information we can get from that. In this calculation we can pull a count of events grouping by the person_id where the event name is SQL Symphony Concert and the date is anything in December of 2017.

From here we get two different ID's so like before, lets plug those into the persons data to find our suspects.

We get a Bryan and Miranda. Knowing it is a female suspect were looking for, lets plug miranda's license_id into the drivers table and see if she drives a Tesla and has red hair!

BINGO! Only other information we want to confirm before we accuse her of murder is that we were told she makes a lot of money. So lets plug her SSN into the income table and see what that looks like.

Now for the final part is plugging in her information into the solutions query!

Congratulations detective! Thank you for coming along this walkthrough and feel free to play with the different tables a little more for some fun easter eggs scattered throughout! I hope you enjoyed and if you'd like some more SQL training check out some other Data School Blogs!

Author:
Brandon Traditi
Powered by The Information Lab
1st Floor, 25 Watling Street, London, EC4M 9BR
Subscribe
to our Newsletter
Get the lastest news about The Data School and application tips
Subscribe now
© 2025 The Information Lab