Here I will go over how I solved the murder mystery SQL challenge.
1. Get Crime Scene Details
Using the details provided that there was a ‘murder’, that occurred on the ‘Jan.15, 2018' and took place in ‘SQL City’. You can filter for crime reports using this info.
Below is the description of the crime scene report, which suits all the criteria.
2. Find the Witnesses
Using the clue obtained in pt.1, you are able to find witnesses in the person table.
First Witness
To find the first witness, filter by street name "Northwestern Dr" and the Max address number.
Second Witness
To find the second witness, filter by name ‘Annabel’ and street name ‘Franklin Ave’.
Witness Statements
Using the person ids of the witnesses, you can now look into their statements from their interviews to find details on the killer.
3. Find the Killer
Using the details obtained in the witness statements in pt.2, find the killer. Filter by get fit membership id, membership status, drivers license plate number and a get fit check in date.
Verify the Killer
Verify the killer using the Query provided.
Amazing, the killer was confirmed! However it seems that there is more to the crime!
Find the Mastermind
Killers Interview
Using the killers person id, find their interview statement.
Master Mind Description
Using the description above in pt.3 from the killers interview, identify the mastermind. Filter by height, car make & model and the Facebook events attended.
Verify Mastermind
Now there is a possible suspect, verify using the query provided.
Amazing ! The crime is solved !