Data Analytics Super League Competition (Challenge 2)

The second challenge was quite a doozy but I scaled through!

Question:

A 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.

Follow these steps to solve this challenge:

• Download the sql-murder-mystery.db file here https://drive.google.com/drive/folders/1SLlSSzIqhu9m4p8HmoJYjn5X_GTYdDsf?usp=share_link
• Visit www.sqliteonline.com
• Click on file, then open db and load in the database file you downloaded above
• Write your SQL queries to see the different tables and the content
• Use the Schema Diagram from the Google Drive Folder above to navigate the different tables
• Figure out who committed the crime with the details you remembered above
• Create a Word or txt document that contains your thought process, narrative, and SQLcodes written to arrive at the solution to the murder mystery
• Submit a Google Drive or OneDrive link to the Word or text document

Solution: link to Word doc, which contains all the necessary images.(https://docs.google.com/document/d/15Pyzv_XhMsHFcz81iOS90StZRZUlb2T8Sn57jmXfBe8/edit?usp=sharing)

Key Points:

• Retrieving the corresponding crime scene report from the police department’s database.
• Figure out who committed the crime with the details you remembered above

Answer:

The Answer is “Miranda Priestly” who was the real villain, while “Jeremy Bowers” committed the murder. But here is my thought process;

• Firstly, since I vaguely remember some information, which will be used to search out the crime scene report, I went ahead to use this query to get the information I needed.

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

From that, I could pick the third one because the type was murder and it is a murder case.

• From the report, I was able to see that I had two witnesses, one name unknown but lives at the last house on Northwestern Dr, and the other having Annabel in the name but lives somewhere at Franklin Ave.
• I have to know the name of the first witness. Hence, this query;

SELECT * FROM person WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC;

• From here, I can see that Morty Schapiro is the first witness, now I go ahead to know the second witness who has Annabell in her name and lives somewhere on Franklin Ave.

SELECT * FROM person
WHERE name LIKE 'Annabel%' 
AND address_street_name = 'Franklin Ave' 
ORDER BY name;

• Now, I know her full name is Annabel Miller, now I need one table showing all our witnesses' details and also to save it as a table-Witness for future reference.

CREATE TABLE Witness AS 
SELECT * FROM person 
WHERE address_street_name = 'Northwestern Dr' AND name = 'Morty Schapiro'
UNION
SELECT * FROM person 
WHERE name ='Annabel Miller' AND address_street_name = 'Franklin Ave';

• checking our new table.

SELECT * FROM Witness;

• So the new witness table connects to the interview table, so I can know what was said during their cross-examination to have a hit on who the murderer is, by performing a JOIN.

SELECT Witness.*, interview.transcript FROM Witness
INNER JOIN interview ON Witness.id = interview.person_id;

• Based on their testimony, I have to get data and see whether what they said matches.
• Morty said from the transcript column- “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”. All the text highlighted will serve as a clue on what to do next therefore I will look for this info from the get_fit_now_member, driverdrivers_license, and person tables.

SELECT get_fit_now_member.*, drivers_license.id, drivers_license.plate_number,drivers_license.car_make, drivers_license.car_model
FROM get_fit_now_member
JOIN person ON get_fit_now_member.person_id = person.id
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE drivers_license.plate_number LIKE '%H42W%';

• Now I know the murder is likely Jeremy Bowers from Morty’s testimony but I have to confirm from Annabel's testimony as well.
• Annabel said, “I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January 9th.” This states that she saw the murder happen and it is the same person from her gym where she worked out on the 9th of January, the previous week before the murder. I just need to check the get_fit_now_member and get_fit_now_check_in tables to be sure that her testimony aligns with what she said, also what Morty said as well will lead me to the murder.

SELECT get_fit_now_check_in.*, get_fit_now_member.*
FROM get_fit_now_check_in
JOIN get_fit_now_member
ON get_fit_now_check_in.membership_id = get_fit_now_member.id
where get_fit_now_check_in.check_in_date = 20180109;

• From the data, we can see that Annabel actually saw the suspect from Morty’s testimony at the gym a week before, so Morty and Annabel's testimony match, making sure that Jeremy Bowers committed the crime.
• Now we need to know who instigated the murder- the real villain. I went to the solution table and discovered there was a trigger, which had a clue.

INSERT INTO solution (user, value) 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 villain 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 with your new suspect to check your answer.”
• After querying the interview table, I found more clues, Jeremy bowers said “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 used those clues to find out that the real villain is “Miranda Priestly” with the query below;

SELECT facebook_event_checkin.*, drivers_license.*, person.name
FROM facebook_event_checkin
JOIN person ON facebook_event_checkin.person_id = person.id
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE facebook_event_checkin.event_name ='SQL Symphony Concert'
AND facebook_event_checkin.date like '201712%'
AND drivers_license.hair_color = 'red'
AND drivers_license.car_make= 'Tesla'
AND drivers_license.car_model= 'Model S';

• Now to check if I am correct, I need to go back to the solution table.

INSERT INTO solution (user, value) VALUES (1, 'Miranda Priestly');
SELECT value FROM solution;