A refresher on SQL – The SQL Murder Mystery

Image from The SQL Murder Mystery Website

In my current role, I do not use SQL at all. I have always loved SQL. It is quite the language and I was very happy when @jessica_xls shared the link to The SQL Murder Mystery. I decided to relive my SQL days and what better way to do it than to solve a MYSTERY!! It was also a great way to use Common Table Expressions(CTE) – this would be found below.

Shall we dive into it? I promise I would make it fun.

The website makes it interesting to solve the case. Before we start getting the name of the murderer, The website gives this cool feature of running 2 queries to know the names of the tables in the database and the data structure of the crime_scene_report table(of course we do need that in order to solve this case as our detective instincts grow). Note: The SQL Murder Mystery is built using SQLite.

Step 1: Let’s take a look at the Crime Report. The only information we have is that the crime was murder, which took place in SQL City on Jan 15 2018.

Select *
From crime_scene_report
Where date = '20180115' 
And city = 'SQL City'
And type = 'murder'

 

Good news! We have just one murder case on Jan 15 2018. It narrows down our work, doesn’t it?

What is next you may ask?

Step 2:  Getting the transcripts of the witnesses from the person and interview tables.

Let’s get the transcript of the first witness. Assuming the houses are arranged in ascending order, let’s get the last house on Northwestern Dr address.

Select *
From person
Join interview on id = interview.person_id
Where address_street_name = 'Northwestern Dr'
Order by 4 desc
Limit 1

 

Our first witness Morty Schapiro definitely heard a gunshot being fired that day. Also what a perfect description to get of the suspect.

Can we check in on our second witness Annabel of Franklin Ave?

Select *
From person
Join interview on id = interview.person_id
Where name like '%Annabel%'
And address_street_name = 'Franklin Ave'

 

Annabel Miller also did confirm that there was a murder. With Morty’s description, we have some good leads on the case.

Step 3: Let’s find the murderer using the person table and the drivers_license table.

Select name
From (
Select *
From get_fit_now_member
Join person on person_id = person.id
Where get_fit_now_member.id like '48Z%'
) as results 
Join drivers_license on results.license_id = drivers_license.id
Where plate_number like '%H42W%'

The murderer was Jeremy Bowers but is he the mastermind behind this crime? Let’s find out by checking his transcript.

Select transcript
From interview
Join person on interview.person_id = person.id
Where person.name = 'Jeremy Bowers'

 

We are getting warmer. Let’s find the Real Villain, the Mastermind of this mystery.

Select name
From(
Select *, count(*) as number_of_attendance
From person
Join facebook_event_checkin on person.id = facebook_event_checkin.person_id
Where event_name = 'SQL Symphony Concert'
And date like '201712%' group by name
) as results
Join drivers_license on results.license_id = drivers_license.id
Where number_of_attendance = 3 
And 65 <= height <= 67
And gender = 'female'
And hair_color = 'red'
And car_make = 'Tesla' 
And car_model = 'Model S'

Miranda Priestly is the brains behind this murder!

 

 

Great work. We solve the murder mystery in SQL City. Now let’s look at some Common Table Expressions(CTE)

• • Finding the murderer

With 

suspect as (
Select *
From get_fit_now_member
Join person on person_id = person.id
Where get_fit_now_member.id like '48Z%'), 

the_suspect as (
Select *
From suspect
Join drivers_license on license_id = drivers_license.id
Where plate_number like '%H42W%') 

Select transcript
From interview
Join the_suspect on interview.person_id = the_suspect.person_id;

• • Finding the mastermind

With 

short_list as (
Select *
From facebook_event_checkin
Where event_name = 'SQL Symphony Concert'
And date like '201712%'), 

persons_shortlist as (
Select *
From person
Join short_list on id = person_id), event_info as (
Select *, count(*) as num_attedance
From persons_shortlist join drivers_license on license_id = drivers_license.id 
Group by name) 

Select name
From event_info
Where 65<= height <= 67
And hair_color = 'red'
And gender = 'female'
And num_attedance = 3
And car_make = 'Tesla'
And car_model = 'Model S'

 

If you enjoyed this, do check out The SQL Murder Mystery and do give @jessica_xls a follow(She is AWESOME)