SQL Murder Mystery.sql

-- 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 * from person
	where address_street_name = "Northwestern Dr"
	order by address_number desc
	limit 1

-- | id    | name           | license\_id | address\_number | address\_street\_name | ssn       |
-- |-------|----------------|-------------|-----------------|-----------------------|-----------|
-- | 14887 | Morty Schapiro | 118009      | 4919            | Northwestern Dr       | 111564949 |

select transcript from interview
	where person_id in (
		select id from person
			where address_street_name = "Northwestern Dr"
			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 transcript from interview
	where person_id in (
		select id from person
			where address_street_name = "Franklin Ave"
			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 * from get_fit_now_member where id like "48Z%" and membership_status = "gold"

-- | id    | person\_id | name          | membership\_start\_date | membership\_status |
-- |-------|------------|---------------|-------------------------|--------------------|
-- | 48Z7A | 28819      | Joe Germuska  | 20160305                | gold               |
-- | 48Z55 | 67318      | Jeremy Bowers | 20160101                | gold               |

select * from drivers_license
	where id in (
		select license_id from person
			where id in (
				select person_id from get_fit_now_member
					where id like "48Z%"
					and membership_status = "gold"
			)
	)
	and plate_number like "%H42W%"

-- | id     | age | height | eye\_color | hair\_color | gender | plate\_number | car\_make | car\_model |
-- |--------|-----|--------|------------|-------------|--------|---------------|-----------|------------|
-- | 423327 | 30  | 70     | brown      | brown       | male   | 0H42W2        | Chevrolet | Spark LS   |

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.

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.