Solution for https://mystery.knightlab.com/

solution.sql

SELECT name
FROM sqlite_master
where type = 'table'

# crime_scene_report
# drivers_license
# person
# facebook_event_checkin
# interview
# get_fit_now_member
# get_fit_now_check_in
# income
# solution

SELECT * FROM crime_scene_report
WHERE 1=1
  AND 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 * FROM person
WHERE 1=1
	AND address_street_name ='Northwestern Dr'
GROUP BY address_street_name
HAVING MAX(address_number)=address_number
UNION
SELECT * FROM person
WHERE 1=1
  AND name LIKE 'Annabel%'
  AND address_street_name='Franklin Ave';

# 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 interview WHERE person_id IN (14887,16371)

#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 drivers_license dl
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 1=1
  AND 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 get_fit_now_member
where id IN ('48Z7A','48Z55');
# id	person_id	name	membership_start_date	membership_status
# 48Z55	67318	Jeremy Bowers	20160101	gold
# 48Z7A	28819	Joe Germuska	20160305	gold

SELECT *
FROM person
where id IN ('67318','28819');
# id	name	license_id	address_number	address_street_name	ssn
# 28819	Joe Germuska	173289	111	Fisk Rd	138909730
# 67318	Jeremy Bowers	423327	530	Washington Pl, Apt 3A	871539279

SELECT *
FROM facebook_event_checkin
where person_id IN ('67318','28819');

# person_id	event_id	event_name	date
# 67318	4719	The Funky Grooves Tour	20180115
# 67318	1143	SQL Symphony Concert	20171206

SELECT *
FROM income WHERE ssn IN ('138909730','871539279')
# ssn	annual_income
# 871539279	10500

SELECT p.name
FROM drivers_license dl
         JOIN person p ON p.license_id=dl.id
WHERE 1=1
  AND dl.plate_number LIKE '%H42W%';
AND p.name IN
(
    SELECT m.name
    FROM get_fit_now_check_in ci
             JOIN get_fit_now_member m ON ci.membership_id = m.id
    WHERE 1 = 1
      AND check_in_date = 20180109
      AND membership_id LIKE '48Z%';
)
# name
# Jeremy Bowers


INSERT INTO solution VALUES (1, 'Jeremy Bowers');
SELECT value FROM solution;
# 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.

INSERT INTO solution VALUES (1, 'Jeremy Bowers');

SELECT value FROM solution;

SELECT * 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.

#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 dl.id,p.name,i.annual_income
FROM drivers_license dl
         JOIN facebook_event_checkin fb
         JOIN person p ON p.ID = fb.person_id
         JOIN income i ON i.ssn=p.ssn
WHERE 1=1
  AND dl.car_make='Tesla'
  AND dl.car_model='Model S'
  AND dl.gender='female'
  AND dl.height BETWEEN 64 AND 68
  AND dl.hair_color = 'red'
  AND dl.id = p.license_id
  AND fb.event_name = 'SQL Symphony Concert'
  AND fb.date BETWEEN 20171201 AND 20171231
GROUP BY dl.id,p.name
HAVING i.annual_income = MAX(i.annual_income)

# id	name	annual_income
# # 202298	Miranda Priestly	310000


INSERT INTO solution VALUES (1, 'Miranda Priestly');

SELECT value FROM solution;

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