SQL Murder Mystery — Part 1
Can you solve this murder mystery using SQL? 🔪
The crime was a murder that occurred sometime on Jan.15, 2018, and that it took place in SQL City 🧐
Play this interactive SQLite game on 👉https://lnkd.in/gkaUZrP
I have attached the database scheme here 📚
Step 1
Query the database with the information that we do know. There was a murder that took place on Jan.15, 2018 in SQL City
now we know that there are 2 witnesses: One of them lives on Northwestern Dr, but we don’t know their name. The second witness’s name is Annabel and lives on Franklin Ave
Step 2
Query the person table for the number of people on Northwestern Dr, and find Annabel who lives on Franklin Ave. Now we can narrow our search with the interviews. Lets look in the interview table to find out what her transcript says
Step 3
Lets look at who checked in at gym on January 9th, 2018, membership status, and driving license table for the license id.
Find solution steps for Murderer in the below screenshot 👇
In step 2, I have used Union inside the CTE before joining with the Interview table.
I am sure there would be other ways to write Step2. If you are reading this blog, I would love to know your recommendation.
The murderer was Hired 😱
Read the second part to find who is the real Villain 🤔
If you are a #AdobeAnalytics enthusiast, do join our group AdobeAnalyticsProfessionalAndRecruiters .
If you enjoyed reading check out my other articles,
Visitor Retention Analysis using Adobe Analytics
Customer engagement analysis using SQL
Anomaly Detection in Non-programming way using Excel
#sql #dataanalytics #datascience #analytics #DataAnalysis
#Funlearning #MysteryThriller
