sql_murder_mystery.sql

/*
select * from crime_scene_report where type='murder' and city='SQL City';

date	type	description	city
20180215	murder	REDACTED REDACTED REDACTED	SQL City
20180215	murder	Someone killed the guard! He took an arrow to the knee!	SQL 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

select * from interview where person_id in (select id from person where address_street_name like '%Northwestern Dr%' or address_street_name like '%Franklin Ave%');
Perhaps a way to narrow it down more, but the last two rows say:

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".
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.

select * from person where id in (14887, 16371);

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

select * from get_fit_now_check_in WHERE check_in_date=20180109 and membership_id like '48Z%';

membership_id	check_in_date	check_in_time	check_out_time
48Z7A	20180109	1600	1730
48Z55	20180109	1530	1700

select * from drivers_license where plate_number like '%H42W%';

id	age	height	eye_color	hair_color	gender	plate_number	car_make	car_model
183779	21	65	blue	blonde	female	H42W0X	Toyota	Prius
423327	30	70	brown	brown	male	0H42W2	Chevrolet	Spark LS
664760	21	71	black	black	male	4H42WR	Nissan	Altima


select * from get_fit_now_check_in where membership_id in (select id from get_fit_now_member where person_id in (select id from person where license_id in (423327, 664760)));

membership_id	check_in_date	check_in_time	check_out_time
48Z55	20180109	1530	1700

select * from person where id in (select person_id from get_fit_now_member where id='48Z55');

id	name	license_id	address_number	address_street_name	ssn
67318	Jeremy Bowers	423327	530	Washington Pl, Apt 3A	871539279

Check your solution
Did you find the killer?

INSERT INTO solution VALUES (1, 'Jeremy Bowers');
        
        SELECT value FROM solution;
		
value
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 villian behind this crime. If you feel especially confident in your SQL skills, try to complete this final step with no more than 2 queries. Use this same INSERT statement to check your answer.

select * from interview where person_id=67318;

person_id	transcript
67318	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. 

select * from drivers_license where hair_color='red' and car_make='Tesla';

id	age	height	eye_color	hair_color	gender	plate_number	car_make	car_model
202298	68	66	green	red	female	500123	Tesla	Model S
291182	65	66	blue	red	female	08CM64	Tesla	Model S
736081	79	69	brown	red	male	GCAQ6Y	Tesla	Model S
918773	48	65	black	red	female	917UU3	Tesla	Model S

select * from facebook_event_checkin where person_id in (select id from person where license_id in (202298, 291182, 736081, 918773));

person_id	event_id	event_name	date
57410	3841	harder. 	20170610
57410	6505	God did not create the world in 7 days; he screwed around for 6 days 	20171119
57410	1649	knows what it is. 	20170701
99716	1143	SQL Symphony Concert	20171206
99716	1143	SQL Symphony Concert	20171212
99716	1143	SQL Symphony Concert	20171229

select * from person where id=99716;

id	name	license_id	address_number	address_street_name	ssn
99716	Miranda Priestly	202298	1883	Golden Ave	987756388

INSERT INTO solution VALUES (1, 'Miranda Priestly');
        
        SELECT value FROM solution;
		
value
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!

*/

From https://mystery.knightlab.com/