Solution to SQL The Midnight Masquerade Murder

                                          

A walkthrough of the solution to SQL The Midnight Masquerade Murder by Northwestern University Knight Lab. Solution by Vladimir Kashisky                             

Prompt

Case #004: The Midnight Masquerade Murder On October 31, 1987, at a Coconut Grove mansion masked ball, Leonard Pierce was found dead in the garden. Can you piece together all the clues to expose the true murderer?

Objectives

1. Reveal the true murderer of this complex case

Witness reports

You began by retrieving the crime scene record, that shows that during a masked ball, a body was found in the garden. Witnesses mentioned a hotel booking and suspicious phone activity.

select description from crime_scene
where date = '19871031' and location like '%Coconut Grove%'

Then you examine witness statements, which mentioned a hotel booking at The Grand Regency (room 707 on 19871030).

select w.id, w.witness_id, w.clue
from crime_scene c 
join witness_statements w on c.id = w.crime_scene_id
where date = 19871031
and location like '%Coconut Grove%'

Further investigation

Filtering hotel check-ins by these clues returned multiple
entries.

select *
from hotel_checkins
where date = 19871030
order by room_number

A JOIN with surveillance records narrowed the field to one key entry that noted a subject with person_id 11 that is yelling on a phone (“I’m gonna kill him!”).

select *
from hotel_checkins h
join surveillance_records s on h.id = s.hotel_checkin_id
where check_in_date = 19871030
and hotel_name = 'The Grand Regency'
and room_number = 707

Reviewing phone records, you found a call containing the phrase “Why did you kill him, bro? You should have let the carpenter do his job!” to recipient_id 58 Victor DiMarco.

select p.name, f.*
from person p 
join phone_records f on p.id = f.recipient_id
where call_date = 19871030
and f.caller_id = 11

Identifying the recipient of that call and interviewing him revealed that he was not the killer but just a middleman and hinted that the true murderer is a carpenter.

select *
from final_interviews
WHERE person_id = 58

Suspects

Reviewing phone records of Victor DiMarco revealed that someone with caller_id 133 phoned to DiMarco a call containing the phrase “I will do it. Only if you give me that nice Lambo of yours.” We now know that the killer is a carpenter driving a Lamborghini and he has fake id 133.

select p.name, f.*
from person p 
join phone_records f on p.id = f.recipient_id
where call_date = 19871030
and p.id = 58

name	id	caller_id	recipient_id	call_date	call_time	note
Victor DiMarco	163	133	58	19871030	22:15	I will do it. Only if you give me that nice Lambo of yours.

Joining the vehicle registry with the person table (filtering for occupation “Carpenter” and car model “Lamborghini”) yielded one candidate with real person id 97.

select *
from person p 
join vehicle_registry v on p.id = v.person_id
where p.occupation = 'Carpenter'
and v.car_make like '%lambo%'

id	name	occupation	address	person_id	plate_number	car_make	car_model
97	Marco Santos	Carpenter	112 Forestwoodwoodwood Way	97	EFG901	Lamborghini	Countach

A final interview with that candidate resulted in a confession “I ordered the hit. It was me. You caught me”. Thus, the true murderer is Marco Santos.

select *
from final_interviews
WHERE person_id = 97

id	person_id	confession
97	97	I ordered the hit. It was me. You caught me.

Case Solved! Outstanding detective work! The evidence conclusively shows that Marco Santos is the true murderer.