--
-- Steps taken to solve https://mystery.knightlab.com/
--

SELECT description
FROM crime_scene_report
WHERE type = "murder"
  AND city = "SQL City"
  AND date = 20180115
;

--
-- 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 id, name, license_id, address_number, ssn
FROM person
WHERE name LIKE 'Annabel %'
;

--
-- 16371  Annabel Miller  490173  103 318771143
--

SELECT transcript
FROM interview
WHERE person_id = 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 id, name, license_id, address_number, ssn
FROM person
WHERE address_street_name = "Northwestern Dr"
ORDER BY address_number DESC
LIMIT 1
;

--
-- 14887  Morty Schapiro  118009  4919  111564949
--

SELECT transcript
FROM interview
WHERE person_id = 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".
--

SELECT person.name
FROM person
JOIN drivers_license
  ON person.license_id = drivers_license.id
WHERE drivers_license.plate_number LIKE 'H42W%'
;

--
-- Maxine Whitely
--

SELECT id, name
FROM get_fit_now_member
WHERE membership_status = 'gold'
  AND id LIKE '48Z%'
;

--
-- 48Z7A  Joe Germuska
-- 48Z55  Jeremy Bowers
--

SELECT person.name, interview.transcript
FROM interview
JOIN person
  ON interview.person_id = person.id
WHERE person.name IN ('Jeremy Bowers', 'Joe Germuska')
;

--
-- Jeremy Bowers  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 person.name
FROM drivers_license
  AS dl
JOIN person
  ON dl.id = person.license_id
WHERE dl.height BETWEEN 64 AND 67
  AND dl.hair_color = 'red'
  AND dl.car_make = 'Tesla'
  AND dl.car_model = 'Model S'
  AND dl.gender = 'female'
;

--
-- Red Korb
-- Regina George
-- Miranda Priestly
--

SELECT person.name, COUNT(person.name) AS visits
FROM facebook_event_checkin
  AS event
JOIN person
  ON event.person_id = person.id
WHERE event.date BETWEEN 20171201 AND 20171231
  AND event.event_name = 'SQL Symphony Concert'
  AND person.name IN ('Red Korb', 'Regina George', 'Miranda Priestly')
GROUP BY person.name
;

--
-- Miranda Priestly 3
--

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