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