sql_murder_mystery

--Read crime report
SELECT *
FROM crime_scene_report
WHERE date LIKE '%20180115%' 
and city = 'SQL City' 
and type = 'murder';
--Security footage shows two witnesses 
--1st lives at the last house on Northwestern Dr
--2nd witness named Annabel lives somewhere on Franklin Ave


SELECT *
FROM person
WHERE name LIKE '%Annabel%'
AND address_street_name LIKE '%Franklin Ave%';
--Annabel Miller 103 Franklin Ave ssn 318771143 license 490173
SELECT *
FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number desc;
--EITHER Morty Schapiro, 118009, 4919 Northwestern Dr ssn 111564949


--Look at interviews join with people.
SELECT person.name, interview.transcript 
FROM person
JOIN interview
ON person.id=interview.person_id
WHERE name = 'Annabel Miller' or 'Morty Schapiro'
GROUP by person_id;
-- Morty:
--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".
--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


--Join get-fit now member with logins on jan 9th and are gold members
SELECT get_fit_now_member.name, 
get_fit_now_member.membership_status, 
get_fit_now_check_in.check_in_date
FROM get_fit_now_member
LEFT JOIN get_fit_now_check_in
ON get_fit_now_member.id = get_fit_now_check_in.membership_id
WHERE check_in_date LIKE '%20180109%' AND membership_status = 'gold'
GROUP BY name
--Suspects: Annabel Miller, Burton Grippe, Carmen Dimick, Jeremy Bowers
--Joe Germuska, Sarita Bartosh (mainly a man; Burton, Jeremy, Joe)


--Look back at interviews
SELECT person.name, interview.transcript 
FROM person
JOIN interview
ON person.id=interview.person_id
WHERE name = 'Jeremy Bowers'
GROUP by name
--Results From Jeremy: 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.


--Join drivers license and person then person and facebook
SELECT drivers_license.hair_color, 
drivers_license.height,
drivers_license.gender, 
drivers_license.car_model,
person.name
FROM drivers_license
JOIN person
ON drivers_license.id = person.license_id
WHERE 
gender = 'female' 
AND 
hair_color = 'red'
AND
car_model = 'Model S'
GROUP BY name
--Results from above = miranda priestly 66, red korb 65, regina george 66


--Go through facebook event check in to see if the above three were in attendance
SELECT facebook_event_checkin.event_name, 
facebook_event_checkin.date, 
person.name
FROM facebook_event_checkin
JOIN person
ON facebook_event_checkin.person_id = person.id
WHERE event_name LIKE '%SQL Symphony Concert%'
AND name = 'Miranda Priestly'
GROUP BY name
--Miranda Priestly and Jermey Bowers both appear in the facebook checkin


--Double check Miranda's income to see if it is in the wealth/rich range
SELECT person.name, 
income.annual_income
FROM person
JOIN income
ON person.ssn = income.ssn
WHERE name = 'Miranda Priestly'
--Miranda makes $310,000/ year
--Miranda Priestly is the brains behind the murder but Jeremy Bowers actually did it