SQL Murder Mystery

-- BACKGROUND: crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. Start by retrieving the corresponding crime scene report from the police department’s database.--

--Below are my process step by step--

-- 1. Recall the memory using a query --

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

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

-- 2. Identify the witnesses --

SELECT *
FROM person
WHERE address_street_name = 'Northwestern Dr' 
   OR address_street_name = 'Franklin Ave' AND name = 'Annabel Miller'
ORDER BY address_number ASC

--I have successfully identifying the witnesses. First person lives at the last house on "Northwestern Dr" named Morty Schapiro and second witness named Annabel who already have her data registered. Both have correspondence ID of 14887 and 16371 and license id of 118009 and 490173--

-- 3. Find information from the witnesses by matching their ID to other tables --

SELECT *
FROM drivers_license
WHERE id = 118009 OR id = 490173

--Got the witnesses physical and vechile reports. But there seems not much lead on it--

SELECT *
FROM get_fit_now_member
WHERE person_id = 14887 OR person_id = 16371

--Only Anna that have Gym membership which membershipe number is 90081--

SELECT *
FROM facebook_event_checkin
WHERE person_id = 14887 OR person_id = 16371

--Both Anna and Morty attended an event named The Funky Grooves Tour which have the same date as the murder occurs. Perhaps this is could be a valuable lead--

SELECT *
FROM interview
WHERE person_id = 14887 OR person_id = 16371

--Both interview transcript indicated that the murderer is a member of Get Fit Now Gym. Additionally, Morty said the murderer have a number bag started with "48Z" and plate number of include "H42W". This is a strong lead.--

-- 4. Find the bag and plate number that match the description --

SELECT *
FROM get_fit_now_member
WHERE membership_status = 'gold' AND id LIKE '%48Z%'

-- 5. There are two suspects. I need to determine which one by searching the suspect's plate number --

SELECT *
FROM drivers_license
WHERE plate_number LIKE "%H42W%"

-- 6. There are three person who have a plate number that inluce "H42W". But there is no name for the person. Therefore, I have to search for it via license_id from other table --

SELECT *
FROM person
WHERE license_id IN ('183779', '423327', '664760')

-- 7. Now the names are available. I need to determine one person who seems to be the suspect by perfom inner join --

SELECT p.name, gym.id, gym.membership_status
FROM drivers_license AS dl
INNER JOIN person AS p ON p.license_id = dl.id
INNER JOIN get_fit_now_member AS gym ON gym.person_id = p.id
WHERE plate_number LIKE '%H42W%'
	AND gym.id LIKE '%48Z%';              

--Jeremy Bowers seems to be the prime suspect, now I need to match the plate number and bag number --

SELECT *
FROM get_fit_now_member
WHERE person_id = 67318

--Jeremy Bowers is the killer. Now let's see what his interview said--

SELECT *
FROM interview
WHERE person_id = 67318

--He said hew was hired by a woman with a lot of money. She's around 5'5" (65") or 5'7" (67"). Has red hair and she drives a Tesla Model S. He know that she attended the SQL Symphony Concert 3 times in December 2017.--

-- 8. Find out the data about the woman that match the previous description --
		
SELECT *
FROM drivers_license
WHERE gender = 'female' AND car_model = 'Model S' AND hair_color = 'red'

--I need to search the suspects name by search for it from other table via their license_id once again--

SELECT *
FROM person
WHERE license_id IN (202298, 291182, 918773)

--Got the ID of the suspects. I want to filter it by verify the event that attended by the suspect by matching her ID to and annual income by verify her SSN to other table--

SELECT *
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert' AND date LIKE '%201712%'
GROUP BY person_id
HAVING COUNT(person_id) = 3

--There are two person who attended the SQL Symphony Concert three times--

SELECT *
FROM income
WHERE ssn IN (961388910, 337169072, 987756388)

--Only two person have their annual income registered on income table--

-- 9. For the final touch, I need to perform final join using id and ssn to determine the brain behind this criminal act --

SELECT f.person_id, p.name, dl.height, dl.hair_color, dl.car_make, dl.car_model
FROM person AS p
INNER JOIN drivers_license AS dl ON p.license_id = dl.id
INNER JOIN facebook_event_checkin AS f ON p.id = f.person_id
INNER JOIN income AS i ON p.ssn = i.ssn
WHERE f.event_name = 'SQL Symphony Concert'
	AND f.date LIKE '%201712%'
GROUP BY p.id
HAVING COUNT(p.id) = 3
	AND dl.gender = 'female'
	AND dl.car_model = 'Model S'
	AND i.ssn IN (961388910, 337169072, 987756388);

--Confirmed. The mastermind behind this murder was Miranda Priestly.--