tcard · December 17, 2019 09:57
diff --git a/mystery.sql b/mystery.sql
 -- My solution to https://mystery.knightlab.com/

 SELECT * FROM crime_scene_report WHERE date = 20180115 AND type = 'murder' AND city = 'SQL City';

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

 SELECT p.id, transcript
 FROM person p
 JOIN interview i ON p.id = i.person_id
 WHERE address_street_name LIKE '%Franklin%'
 AND name LIKE '%Annabel%';

 -- 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 p.id, transcript
 FROM person p
 JOIN interview i ON p.id = i.person_id
 WHERE address_street_name LIKE '%Northwestern%'
 ORDER BY address_number DESC
 LIMIT 1;

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

 SELECT p.id, p.name FROM person p
 JOIN get_fit_now_member m ON p.id = m.person_id
 JOIN drivers_license dl ON p.license_id = dl.id
 JOIN get_fit_now_check_in ci ON ci.membership_id = m.id
 WHERE m.id LIKE '48Z%'
 AND m.membership_status = 'gold'
 AND dl.plate_number LIKE '%H42W%'
 AND ci.check_in_date = 20180109;

 -- id: 67318, name: Jeremy Bowers

 -- 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 transcript FROM interview WHERE person_id = 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
 p.id,
 p.name
 FROM person p
 JOIN facebook_event_checkin ec ON p.id = ec.person_id
 JOIN drivers_license dl ON p.license_id = dl.id
 WHERE ec.event_name LIKE '%Symphony%'
 AND ec.date >= 20171201 AND ec.date < 20180101
 AND dl.height >= 65
 AND dl.hair_color = 'red'
 AND dl.car_model LIKE '%Model S%'
 GROUP BY p.id, p.name
 HAVING COUNT(*) >= 3;

 -- id: 99716, name: Miranda Priestly

 -- 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!
	-- My solution to https://mystery.knightlab.com/

	SELECT * FROM crime_scene_report WHERE date = 20180115 AND type = 'murder' AND city = 'SQL City';

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

	SELECT p.id, transcript
	FROM person p
	JOIN interview i ON p.id = i.person_id
	WHERE address_street_name LIKE '%Franklin%'
	AND name LIKE '%Annabel%';

	-- 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 p.id, transcript
	FROM person p
	JOIN interview i ON p.id = i.person_id
	WHERE address_street_name LIKE '%Northwestern%'
	ORDER BY address_number DESC
	LIMIT 1;

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

	SELECT p.id, p.name FROM person p
	JOIN get_fit_now_member m ON p.id = m.person_id
	JOIN drivers_license dl ON p.license_id = dl.id
	JOIN get_fit_now_check_in ci ON ci.membership_id = m.id
	WHERE m.id LIKE '48Z%'
	AND m.membership_status = 'gold'
	AND dl.plate_number LIKE '%H42W%'
	AND ci.check_in_date = 20180109;

	-- id: 67318, name: Jeremy Bowers

	-- 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 transcript FROM interview WHERE person_id = 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
	p.id,
	p.name
	FROM person p
	JOIN facebook_event_checkin ec ON p.id = ec.person_id
	JOIN drivers_license dl ON p.license_id = dl.id
	WHERE ec.event_name LIKE '%Symphony%'
	AND ec.date >= 20171201 AND ec.date < 20180101
	AND dl.height >= 65
	AND dl.hair_color = 'red'
	AND dl.car_model LIKE '%Model S%'
	GROUP BY p.id, p.name
	HAVING COUNT(*) >= 3;

	-- id: 99716, name: Miranda Priestly

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