Following the evidence through relational data
I started with three pieces of information: a murder, January 15, 2018, and SQL City. From there, I used SQL to retrieve the crime report, identify witnesses, connect records across multiple tables, test each clue, and eventually uncover both the killer and the person behind the crime.
The Challenge
The challenge was to investigate a murder using information stored across a relational database. The starting information was limited to the type of crime, the date, and the city.
Each query revealed another clue, which determined what I needed to investigate next.
Query by query
Each result produced the information needed to decide what to query next.
Crime Scene Report
Two Witnesses Identified
Morty Schapiro + Annabel Miller
Witness Interviews
Get Fit Now Gym
48Z Membership + Gold Status
January 9 Check-in
Licence Plate containing H42W
Jeremy Bowers
Jeremy's Interview
Female + Red Hair + 65–67 inches + Tesla Model S
SQL Symphony Concert
Miranda Priestly
Selected SQL
SELECT *
FROM crime_scene_report
WHERE type = 'murder'
AND city = 'SQL City'
AND date = 20180115;The investigation began by filtering the crime scene reports using the three details available: crime type, city, and date.
Result
The report revealed two witnesses — one living at the last house on Northwestern Dr and another named Annabel living on Franklin Ave.
Selected SQL
SELECT *
FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1;The report said the first witness lived at the last house on Northwestern Dr. Sorting the address numbers from highest to lowest and limiting the result to one identified the witness.
Result
Morty Schapiro
Selected SQL
SELECT *
FROM get_fit_now_member AS Gm
JOIN get_fit_now_check_in AS Gc
ON Gm.id = Gc.membership_id
JOIN person AS P
ON Gm.person_id = P.id
JOIN drivers_license AS Dl
ON P.license_id = Dl.id
WHERE Gm.id LIKE '48Z%'
AND Gm.membership_status = 'gold'
AND Dl.plate_number LIKE '%H42W%'
AND Gc.check_in_date = 20180109
AND Dl.gender = 'male';At this stage, several clues had to be tested together. I connected the gym membership, gym check-in, person, and driver's licence tables to find the person who matched all of the witness evidence.
Result
Jeremy Bowers
A new lead
After the evidence pointed to Jeremy Bowers, I checked his interview record. His statement revealed that he had been hired by someone else.
SELECT *
FROM interview
WHERE person_id = (
SELECT id
FROM person
WHERE name = 'Jeremy Bowers'
);Selected SQL
SELECT *
FROM drivers_license AS Dl
JOIN person AS P
ON Dl.id = P.license_id
JOIN facebook_event_check_in AS F
ON P.id = F.person_id
WHERE F.event_name = 'SQL Symphony Concert'
AND F.date BETWEEN 20171201 AND 20171231
GROUP BY F.person_id
HAVING COUNT(*) = 3;The concert clue required counting attendance by person. I grouped the event check-ins by person and used HAVING COUNT(*) = 3 to identify someone who attended the SQL Symphony Concert exactly three times during December 2017.
Result
Miranda Priestly
Selected SQL
SELECT P.name, I.annual_income
FROM person AS P
JOIN income AS I
ON P.ssn = I.ssn
WHERE P.name = 'Miranda Priestly';The final clue described the woman as having a lot of money. The person and income tables could be connected through SSN.
Result
Annual Income: $310,000
Conclusion
The investigation identified Jeremy Bowers as the person who carried out the murder.
The witness evidence connected him to the crime through his Get Fit Now Gym membership, January 9 check-in, Gold membership status, membership number beginning with 48Z, and licence plate containing H42W.
Jeremy's interview revealed that he had been hired by a woman.
Following the additional clues through driver's licence records, event check-ins, and income data identified Miranda Priestly as the woman who hired him.
Jeremy Bowers
Identified as the person who carried out the murder
Miranda Priestly
Identified as the person who hired him
Takeaway
This project reinforced an important part of working with relational data: understanding the relationship between tables before writing the query. Rather than matching columns simply because they share similar names, I traced what each identifier represented and how records connected across the database.
As the investigation progressed, I used each result to determine the next question to ask; moving from filtering individual records to joining multiple tables, using subqueries, and grouping event data to narrow the evidence.
Skills demonstrated