Author: Bradley Shedden
Downloads:
Working Document
​​
-
Link to challenge: https://mystery.knightlab.com/
-
Creators: Joon Park and Cathy He while at Knight Lab
-
GitHub Repository: https://github.com/NUKnightLab/sql-mysteries
-
SQL Version: SQL Lite
Project Explanation:
The purpose of this exercise is to practice and showcase my SQL abilities in a fun and engaging way that will be both interesting and informative to readers on my SQL skills and general analytical thought process.
​
Mystery Description:
There's been a Murder in SQL City! The SQL Murder Mystery is designed to be both a self-directed lesson to learn SQL concepts and commands and a fun game for experienced SQL users to solve an intriguing crime.
​
Introduction
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.
​
Schema Diagram
​
​
​
​
​
​
​
​
​
​
​
​
​
​
Beginning of Investigation
From the introduction and my understanding of the schema structure, the key details that I can see being useful to finding the lost crime scene report are:
-
Crime type = Murder
-
City = SQL City
-
Date = Jan.15, 2018
Crime Scene Report SQL Query Code:
SELECT *
FROM
crime_scene_report
WHERE
city = 'SQL City' AND
type = 'murder' AND
date = '20180115'
​
Result
​​​
​
​
​
From the description field of the last query, we now have two leads a witness who lives at the last house on "Northwestern Dr" and another witness named Annabel who lives somewhere on "Franklin Ave". Being that the primary clues to investigate are addresses we can see that the only table containing an address field is the person table, with this in mind I will write two queries using WHERE clauses to gather the specific information of the two witnesses
Northwestern Dr SQL Query Code:
SELECT *
FROM
person
WHERE
address_street_name = 'Northwestern Dr'
ORDER BY
address_number DESC
LIMIT 1
Result
​​
​
​
​
Franklin Ave & Annabel SQL Query Code:
SELECT *
FROM
person
WHERE
address_street_name = 'Franklin Ave' AND
name LIKE 'Annabel%'
Result
​
​
​
​
From this query we now know that the two witnesses to the murder were Annabel Miller and Morty Schapiro, we were also able to gather from the results both of their person id that we can now use to query the interview table to get further information from their testimonies
​
Witness Interview SQL Query Code:
SELECT
person.name,
interview.transcript
FROM
interview
JOIN
person ON person.id = interview.person_id
WHERE
person_id = 14887 OR
person_id = 16371
​
Result
​
​
​
​
​
Gym investigation SQL Query Code:
From the previous query I gained a good amount of information about the killer and an avenue for further investigation. From Morty and Annabel’s interviews I was able to gather that:
-
The killer is a man
-
The murder weapon was a gun
-
The killer goes to the “Get Fit Now Gym’
-
Is a gold member of the gym with a membership number starting with 48Z
-
Annabel saw the killer on January the 9th while at the gym
-
The killer’s numberplate includes the characters "H42W".
Given the amount of information gathered this gives me a couple of avenues I could take for further investigation by either going down the gym route and searching for the partial number plate. Due to the gym lead containing more specifics I will start there.
First I will search the get_fit_now_check_in table on January the 9th To find the specific time period Annabel was at the gym for.
​
Annabel’s Gym time SQL Query Code:
SELECT
gfnm.name,
gfnc.membership_id,
gfnc.check_in_date,
gfnc.check_in_time,
gfnc.check_out_time
FROM
get_fit_now_check_in AS gfnc
JOIN
get_fit_now_member AS gfnm ON gfnc.membership_id = gfnm.id
WHERE
check_in_date = 20180109 AND
gfnm.name = 'Annabel Miller'
​
Result
​
​
​​​​
From these results we can see that Annabel checked in at 4pm on January the 9th and checked out at 5pm. With this information for the next query, we can rule out anyone who arrived after 5pm or left before 4pm. However, we are unable to rule anyone out who arrived before 4pm as Annabel didn’t specify if the killer was already at the gym when she got there or arrived after she did.
Searching for the Killer Gym SQL Query Code:
SELECT
gfnm.name,
gfnm.person_id,
gfnc.membership_id,
gfnc.check_in_date,
gfnc.check_in_time,
gfnc.check_out_time
FROM
get_fit_now_check_in AS gfnc
JOIN
get_fit_now_member AS gfnm ON gfnc.membership_id = gfnm.id
WHERE
check_in_date = 20180109 AND
gfnc.Membership_id LIKE '48Z%' AND
gfnc.check_in_time <= 1700 AND
gfnc.check_out_time >= 1600
​
Result
​​
​
​
​
​
Now with these two culprits I will use the drivers licence table with the partial plate characters and the person id field to find our killer.
​
Narrowing down the Killer SQL Query Code:
SELECT
person.name,
person.id,
dl.plate_number
FROM
drivers_license AS dl
JOIN
Person ON dl.id = person.license_id
WHERE
dl.plate_number LIKE '%H42W%' AND
(person.id = 28819 OR person.id = 67318)
​
Result
​​
​
​
​
​​​​
Looks like this is our killer, lets interview him and see what he has to say.
Killer Interview SQL Query Code:
SELECT
person.name,
interview.transcript
FROM
interview
JOIN
person ON interview.person_id = person.id
WHERE
person_id = 67318 TRANSCRIPT
​
Result
​​
​
​
​
​​​​​
​
Wow, looks like this murder isn’t as simple as I initially thought! We have confirmed that Jeremy Bowers is the killer but was hired to do the hit and provided the following details about his contractor:
-
The mastermind is a Rich woman
-
she's around 5'5" (65") or 5'7" (67")
-
Has red hair
-
Drives a Tesla Model S
-
And attended the SQL Symphony Concert 3 times in December 2017
Search for the Master Mind SQL Query Code:
SELECT
person.name,
dl.id,
dl.age,
dl.height,
dl.hair_color,
income.ssn,
income.annual_income
FROM
drivers_license AS dl
JOIN
person ON dl.id = person.license_id
JOIN
income ON person.ssn = income.ssn
WHERE
dl.gender = 'female' AND
dl.hair_color = 'red' AND
dl.car_make = 'Tesla' AND
dl.car_model = 'Model S' AND
dl.height >= 65
​
Result
​
​
​
​​​
Now we have narrowed it down to two culprits lets use their personal id and the clue about the mastermind attending the SQL Symphony Concert 3 times in December 2017 to query the Facebook_Event_checkin table.
Narrowing down the Master Mind SQL Query Code:
SELECT
person.name,
fec.person_id,
COUNT(fec.date) AS Number_of_visits
FROM
facebook_event_checkin AS fec
JOIN
person ON fec.person_id = person.id
WHERE
fec.event_name = 'SQL Symphony Concert' AND
fec.date LIKE '201712%' AND
(person.name LIKE 'Red Korb' OR person.name LIKE 'Miranda Priestly')
GROUP BY
fec.person_id
HAVING
number_of_visits >= 3
​
Result
​​
​
​
​
​
Checking the solution SQL Query Code
INSERT INTO solution VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;
Result
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!
​
Conclusion
We found the mastermind behind the SQL murder mystery! Through investigation and analysis we discovered that Miranda Priestly was the mastermind who hired Jeremy Bowers to murder our victim.
