SQL Murder Mystery — Case Solved

A crime has taken place and the detective needs your help. The crime scene report was lost — all we know is that the crime was a murder that occurred on January 15, 2018 in SQL City. Using only SQL queries against the police department's database, identify the murderer and uncover the true villain pulling the strings.

STEP 01

Retrieve the Crime Scene Report

Query the crime_scene_report table filtering by date, type, and city to recover the lost report.

SQL

SELECT *
FROM   crime_scene_report
WHERE  date = 20180115
  AND  type = "murder"
  AND  city = "SQL City"

▶ RESULT 1 row

date	type	description	city
20180115	murder	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".	SQL City

🔎

Key Clue: Two witnesses identified. Witness 1 — last house on Northwestern Dr. Witness 2 — named Annabel, lives on Franklin Ave.

STEP 02

Identify the Two Witnesses

Look up each witness in the person table using the clues from the report.

// Query 1 — Highest address number on Northwestern Dr

SQL

SELECT *
FROM   person
WHERE  address_street_name = "Northwestern Dr"
ORDER BY address_number DESC
LIMIT  1

// Query 2 — Annabel on Franklin Ave

SQL

SELECT *
FROM   person
WHERE  address_street_name = "Franklin Ave"
  AND  name LIKE "Annabel%"

▶ RESULT 2 rows

id	name	license_id	address_number	address_street_name	ssn
14887	Morty Schapiro	118009	4919	Northwestern Dr	111564949
16371	Annabel Miller	490173	103	Franklin Ave	318771143

🧑‍⚖️

Witnesses confirmed: Morty Schapiro (ID: 14887) and Annabel Miller (ID: 16371).

STEP 03

Read the Witness Interviews

Pull the interview transcripts for both witnesses from the interview table.

SQL

SELECT *
FROM   interview
WHERE  person_id = 14887
   OR  person_id = 16371

🎙 Morty Schapiro (ID: 14887) 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".

🎙 Annabel Miller (ID: 16371) I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.

📌

Leads extracted: Gym bag starts with "48Z" Gold member Plate contains "H42W" At gym on Jan 9th

STEP 04

Cross-Reference All Clues

Use all witness clues to narrow the suspect pool via three separate queries, then find the intersection.

// Query 1 — Gold members whose ID starts with "48Z"

SQL

SELECT *
FROM   get_fit_now_member
WHERE  id LIKE "48Z%"
  AND  membership_status = "gold"

▶ RESULT 2 rows

id	person_id	name	membership_start_date	membership_status
48Z7A	28819	Joe Germuska	20160305	gold
48Z55	67318	Jeremy Bowers	20160101	gold

// Query 2 — License plates containing "H42W"

SQL

SELECT *
FROM   drivers_license
WHERE  plate_number LIKE "%H42W%"

▶ RESULT 3 rows

license_id	age	gender	plate_number	car_make	car_model	person_id
183779	21	female	H42W0X	Toyota	Prius	78193
423327	30	male	0H42W2	Chevrolet	Spark LS	67318
664760	21	male	4H42WR	Nissan	Altima	51739

// Query 3 — Gym check-in on January 9th (cross-reference with 48Z members)

SQL

SELECT gc.membership_id, gm.person_id, gm.name
FROM   get_fit_now_check_in gc
JOIN   get_fit_now_member gm
         ON gc.membership_id = gm.id
WHERE  gc.check_in_date = 20180109
  AND  gm.id LIKE "48Z%"
  AND  gm.membership_status = "gold"

🎯

The only person who appears in all three result sets — gold gym member with "48Z" bag, license plate containing "H42W", and present at the gym on Jan 9th — is Jeremy Bowers (person_id: 67318).

// SUSPECT IDENTIFIED — PART 1

The Murderer is
Jeremy Bowers

person_id: 67318 · Gold Member: 48Z55 · Plate: 0H42W2

◆ PART 2 — THE REAL VILLAIN ◆

STEP 05

Interview the Murderer

Retrieve the killer's own interview transcript — he reveals who hired him.

SQL

SELECT *
FROM   interview
WHERE  person_id = 67318

🎙 Jeremy Bowers (ID: 67318) — The Murderer 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.

📌

Profile of the mastermind: Female Red hair Height 65–67" Tesla Model S SQL Symphony Concert × 3 in Dec 2017

STEP 06

Uncover the Mastermind

Combine all clues into a single optimized query — joining person, drivers_license, and facebook_event_checkin at once.

// Optimized single query — solves Part 2 in one shot

SQL

SELECT  p.name
FROM    person p
JOIN    facebook_event_checkin fc
          ON p.id = fc.person_id
JOIN    drivers_license dl
          ON p.license_id = dl.id
WHERE   dl.gender       = "female"
  AND   dl.hair_color   = "red"
  AND   dl.car_make     = "Tesla"
  AND   dl.car_model    = "Model S"
  AND   dl.height BETWEEN 65 AND 67
  AND   fc.event_name   = "SQL Symphony Concert"
  AND   fc.date LIKE    "201712%"
GROUP BY fc.person_id
HAVING   COUNT(fc.person_id) = 3
ORDER BY fc.person_id

▶ RESULT 1 row

id	name	license_id	address	ssn	annual_income
99716	Miranda Priestly	202298	1883 Golden Ave	987756388	$310,000

💰

Income confirmed at $310,000/year — consistent with being able to hire a contract killer. Miranda Priestly attended the SQL Symphony Concert exactly 3 times in December 2017, matching the murderer's description precisely.

// CASE CLOSED — THE REAL VILLAIN

The Mastermind is
Miranda Priestly

person_id: 99716 · Annual Income: $310,000 · Tesla Model S — Plate: 500123

The Murderer isJeremy Bowers

The Mastermind isMiranda Priestly

The Murderer is
Jeremy Bowers

The Mastermind is
Miranda Priestly