[Solved, Spoilers] SQL Murder Mystery – Can you find out whodunnit?

Here: https://mystery.knightlab.com/

So, couple of friends of mine send me this challenge, and it’s really fun. Go there and try it!

The plot is: someone killed someone else, and you, as the detective, need to find out who did it. Your weapons are SQL, table and relationships. Off you go!

My solution to this

On the code below you will find all code, with comments, that I’ve used to came up with the solution to this mystery. I hope you enjoy like I did.

What about you, have you done anything differently?

Note: this is SQLite code. Also, all code is multi-line commented using /**/. If you plan on running this code make sure to remove comments when needed.

-- INTRO
/*
Experienced SQL sleuths start here
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.
*/

-- FIRST QUERY, CHECKING CRIME SCENE REPORT

/*
select * from crime_scene_report
where 1=1
and lower( type ) like '%murder%'
and lower( city ) like '%sql%city%'
and date = 20180115;
*/

-- CRIME SCENE REPORT RESULT
/*
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".
*/

-- FIRST WITNESS 
/*
select * from person
where 1=1
and lower( address_street_name ) like '%northwestern%'
order by address_number desc
limit 1;
*/
-- FIRST WITNESS RESULT
/*
id		name			license_id	address_number	address_street_name	ssn
14887	Morty Schapiro	118009		4919			Northwestern Dr		111564949
*/

-- SECOND WITNESS 
/*
select * from person
where 1=1
and lower( address_street_name ) like '%franklin%'
and lower( name ) like '%annabel%'
*/

-- SECOND WITNESS RESULT
/*
id		name			license_id	address_number	address_street_name	ssn
16371	Annabel Miller	490173		103				Franklin Ave		318771143
*/

-- INTERVIEW FROM 1ST WITNESS
/*
select * from interview
where person_id = 14887
*/

-- 1ST WITNESS INTERVIEW REPORT
/*
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".
*/

-- INTERVIEW FROM 2ND WITNESS
/*
select * from interview
where person_id = 16371
*/

-- 2ND WITNESS INTERVIEW REPORT
/*
I saw the murder happen, and I recognized the killer from my gym 
when I was working out last week on January the 9th.
*/

-- SEARCHING FOR A PERSON MEMBER OF GYM THAT MATCHES INTERVIEW REPORT - 1ST WITNESS
/*
select 
g.*, p.*, d.*
from get_fit_now_member g
left join person p on p.id = g.person_id
left join drivers_license d on d.id = p.license_id
where 1=1
and lower( g.membership_status ) like '%gold%'
and g.id like '48Z%'
and d.plate_number like '%H42W%'
*/

-- RESULT FROM 1ST WITNESS INTERVIEW
/*
person_id 67318
name Jeremy Bowers
*/

-- SEARCHING FOR A PERSON MEMBER OF GYM THAT MATCHES INTERVIEW REPORT - 2ND WITNESS
/*
select c1.*, c2.*, g1.*
from get_fit_now_check_in c1
inner join 
(
  select c.check_in_date, c.check_in_time, c.check_out_time
  from get_fit_now_check_in c
  left join get_fit_now_member g on g.id = c.membership_id
  where 1=1
	  -- date as text to search using like for anything on january 09th
  and cast(check_in_date as text) like '%0109' 
	  -- checking that our witness Annabelle person_id 16371 checked in 16h and left 17h
  and g.person_id = 16371
) c2 
 on c1.check_in_date  = c2.check_in_date 
and c1.check_in_date  >= c2.check_in_time 
and c1.check_out_time <= c2.check_out_time
left join get_fit_now_member g1 on g1.id = c1.membership_id 
-- here we also have Jeremy Bowers showing up. He might be the killer.
*/

/*
FINAL RESULT
Congrats, you found the murderer! 
But wait, there's more... If you think you're up for a challenge, try querying the 
interview transcript of the murderer to find the real villain behind this crime. 
If you feel especially confident in your SQL skills, 
try to complete this final step with no more than 2 queries. 
*/

-- QUERYING Jeremy Bowers, 67318, INTERVIEW
/*
select * from interview where person_id = 67318
*/

-- INTERVIEW OS JEREMY RESULT
/*
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.
*/

/*
QUERY TO FIND THE TRUE MASTER MIND BEHIND THE MURDER

EXPLANATION:
I START FROM FACEBOOK_EVENT_CHECKIN TO MAKE SURE THAT ALL 3 ROWS WILL ALWAYS BE 
VISIBLE (AS RIGHT JOIN DOESN'T WORK HERE).
THEN I MOVE TO PERSON, THEN INCOME AND DRIVERS LICENSE, 
SO I HAVE ALL TABLES AND ATTRIBUTES AT MY DISPOSAL.
THEN I FILTER EVERYTHING ACCORDING TO JEREMY INTERVIEW, FILTERING CAR, MODEL, ETC.
BY THE END OF ALL FILTERS ONLY ONE PERSON REMAINS, Miranda Priestly
*/

select
f.event_id, f.event_name, f.date, 
p.name, d.height, d.hair_color, d.gender, d.car_make, d.car_model, i.annual_income
from facebook_event_checkin f
left join person p on f.person_id = p.id
left join income i on i.ssn = p.ssn
left join drivers_license d on p.license_id = d.id
where 1=1
and lower( car_make ) like '%tesla%'
and lower( car_model ) like '%model%s%'
and hair_color = 'red'
and gender = 'female'
and (height >= 65 and height < 68)
and lower( f.event_name ) like '%sql%symphony%concert%'
and cast( f.date as text ) like '201712%'

/* 
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!
*/

Reference

https://mystery.knightlab.com/