SQL Murder Mystery - My Walkthrough
Luis Donaldo
2024-11-07
Case Overview
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.
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.
This is the schema of the database:
Metodology
The way I worked this case is by asking questions and trying to
answer them through queries:
Question 1: What murders were committed in SQL City?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM crime_scene_report
WHERE type = 'murder' AND city = 'SQL City';"
)## date type
## 1 20180215 murder
## 2 20180215 murder
## 3 20180115 murder
## description
## 1 REDACTED REDACTED REDACTED
## 2 Someone killed the guard! He took an arrow to the knee!
## 3 Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness, named Annabel, lives somewhere on "Franklin Ave".
## city
## 1 SQL City
## 2 SQL City
## 3 SQL CityAnswer: There were three murders in SQL City, however, only one
was on January 15, 2018
Question 2: Who are the witnesses?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM person
WHERE name LIKE '%Annabel%' AND address_street_name = 'Franklin Ave'
UNION
SELECT *
FROM person
WHERE address_street_name = 'Northwestern Dr'
AND address_number = (SELECT
max(address_number)
FROM person
WHERE address_street_name = 'Northwestern Dr');"
)## id name license_id address_number address_street_name ssn
## 1 14887 Morty Schapiro 118009 4919 Northwestern Dr 111564949
## 2 16371 Annabel Miller 490173 103 Franklin Ave 318771143Answer: There are two witnesses who, based on their descriptions,
I conclude are Annabel Miller and Morty Schapiro.
Question 3: What event were they at the night of the murder?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM facebook_event_checkin
WHERE date = 20180115 AND person_id = 14887 OR person_id = 16371;"
)## person_id event_id event_name date
## 1 14887 4719 The Funky Grooves Tour 20180115
## 2 16371 4719 The Funky Grooves Tour 20180115Answer: The Funky Grooves Tour on January 15, 2018
Question 4: Who else attended this event?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM facebook_event_checkin f
LEFT JOIN person p ON f.person_id = p.id
WHERE event_name = 'The Funky Grooves Tour';"
)## person_id event_id event_name date id name
## 1 14887 4719 The Funky Grooves Tour 20180115 14887 Morty Schapiro
## 2 16371 4719 The Funky Grooves Tour 20180115 16371 Annabel Miller
## 3 67318 4719 The Funky Grooves Tour 20180115 67318 Jeremy Bowers
## license_id address_number address_street_name ssn
## 1 118009 4919 Northwestern Dr 111564949
## 2 490173 103 Franklin Ave 318771143
## 3 423327 530 Washington Pl, Apt 3A 871539279Answer: Jeremy Bowers attended the same event
Question 5: What the witnesses said in their respective interviews
dbGetQuery(conn = con,
statement =
"SELECT
person_id,
name,
transcript,
license_id,
ssn
FROM interview i
LEFT JOIN person p ON i.person_id = p.id
WHERE person_id = 14887 OR person_id = 16371;"
)## person_id name
## 1 14887 Morty Schapiro
## 2 16371 Annabel Miller
## transcript
## 1 I heard a gunshot and then saw a man run out. He had a "Get Fit Now Gym" bag. The membership number on the bag started with "48Z". Only gold members have those bags. The man got into a car with a plate that included "H42W".
## 2 I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.
## license_id ssn
## 1 118009 111564949
## 2 490173 318771143Answer:
Annabel Miller claims to have recognised
the killer from her visit to the gym on 9 January.
Morty Schapiro claims he saw a man running away with a Get Fit Now
gym backpack whose membership number was printed on the backpack and
began with ‘48Z’, only gold members have these backpacks and he said he
saw the man get into a car with license plates that included
‘H42W’.
Question 6: Is it possible to confirm what they
said?
Anabel’s Testimony
dbGetQuery(conn = con,
statement =
"SELECT *
FROM get_fit_now_check_in
LEFT JOIN get_fit_now_member ON membership_id = id
WHERE person_id = 16371;"
)## membership_id check_in_date check_in_time check_out_time id person_id
## 1 90081 20180109 1600 1700 90081 16371
## name membership_start_date membership_status
## 1 Annabel Miller 20160208 goldAnswer: Annabelle was at the gym on January 9th from 4-5pm
Morty’s Testimony
dbGetQuery(conn = con,
statement =
"SELECT *
FROM get_fit_now_member
WHERE id LIKE '%48Z%' AND membership_status = 'gold';"
)## id person_id name membership_start_date membership_status
## 1 48Z7A 28819 Joe Germuska 20160305 gold
## 2 48Z55 67318 Jeremy Bowers 20160101 goldAnswer: Joe Germuska and Jeremy Bowers are the only ones whose
membership begins with ‘48Z’ and are Gold level
Question 7: Were either of these two guys at the gym on the same day and at the same time as Annabel?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM get_fit_now_check_in
WHERE membership_id = '48Z7A' OR membership_id = '48Z55';"
)## membership_id check_in_date check_in_time check_out_time
## 1 48Z7A 20180109 1600 1730
## 2 48Z55 20180109 1530 1700Answer: They were both at the gym at the same time as Annabel on
January 9th
Question 8: Who has a car with license plates matching ‘H42W’?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM drivers_license d
LEFT JOIN person p ON d.id = p.license_id
WHERE plate_number LIKE '%H42W%';"
)## id age height eye_color hair_color gender plate_number car_make
## 1 183779 21 65 blue blonde female H42W0X Toyota
## 2 423327 30 70 brown brown male 0H42W2 Chevrolet
## 3 664760 21 71 black black male 4H42WR Nissan
## car_model id name license_id address_number
## 1 Prius 78193 Maxine Whitely 183779 110
## 2 Spark LS 67318 Jeremy Bowers 423327 530
## 3 Altima 51739 Tushar Chandra 664760 312
## address_street_name ssn
## 1 Fisk Rd 137882671
## 2 Washington Pl, Apt 3A 871539279
## 3 Phi St 137882671Answer: Jeremy Bowers is among the owners of one of these
cars
Question 9: Was the prime suspect interviewed?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM interview
WHERE person_id = 67318;"
)## person_id
## 1 67318
## transcript
## 1 I was hired by a woman with a lot of money. I don't know her name but I know she's around 5'5" (65") or 5'7" (67"). She has red hair and she drives a Tesla Model S. I know that she attended the SQL Symphony Concert 3 times in December 2017.\nAnswer: Jeremy Bowers claims he was hired by a wealthy woman, is
known to have red hair, drives a Tesla Model S, and attended the SQL
Symphony Concert 3 times in December 2017
Question 10: Who are the red-haired women who own a Tesla Model S?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM drivers_license d
LEFT JOIN person p
ON d.id = p.license_id
WHERE
gender = 'female'
AND hair_color = 'red'
AND car_make = 'Tesla'
AND car_model = 'Model S';"
)## id age height eye_color hair_color gender plate_number car_make car_model
## 1 202298 68 66 green red female 500123 Tesla Model S
## 2 291182 65 66 blue red female 08CM64 Tesla Model S
## 3 918773 48 65 black red female 917UU3 Tesla Model S
## id name license_id address_number address_street_name
## 1 99716 Miranda Priestly 202298 1883 Golden Ave
## 2 90700 Regina George 291182 332 Maple Ave
## 3 78881 Red Korb 918773 107 Camerata Dr
## ssn
## 1 987756388
## 2 337169072
## 3 961388910Answer: Miranda Priestly, Regina George and Red Korb are the only
ones who meet these conditions
Question 11: Which of these women attended the SQL Symphony Concert in December 2017?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM facebook_event_checkin
WHERE
person_id = 99716
OR person_id = 90700
OR person_id = 78881
AND date BETWEEN 20171201 AND 20171231;"
)## person_id event_id event_name date
## 1 99716 1143 SQL Symphony Concert 20171206
## 2 99716 1143 SQL Symphony Concert 20171212
## 3 99716 1143 SQL Symphony Concert 20171229Answer: Miranda Priestly is the only one who attended the event,
and in fact, she went three times.
Question 12: What is this person’s income level?
dbGetQuery(conn = con,
statement =
"SELECT *
FROM person p
LEFT JOIN income i ON p.ssn = i.ssn
WHERE id = 99716;"
)## id name license_id address_number address_street_name
## 1 99716 Miranda Priestly 202298 1883 Golden Ave
## ssn ssn annual_income
## 1 987756388 987756388 310000Answer: Yes, her income level is well above average.
Question 12: Who was the real killer?
Answer: Although Jeremy Bowers committed the murder, the mastermind is Miranda Priestly
dbGetQuery(conn = con,
statement =
"SELECT *
FROM solution"
)## user
## 1 0
## value
## 1 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!Solution: Miranda was Priestly the real killer