SQL Murder Mystery

I was given the crime scene report but unfortunately lost it. I however remember two(2) key details -

1. The murder occurred in SQL city.
2. It occurred sometime on 15th January, 2018.

Fortunately for me, this precinct has gone digital already so all the clues needed to solve the case are buried in a large database. I will be using SQL to navigate through this vast network of information. My first step is to retrieve the crime scene report that corresponds to the case.

SELECT *

FROM crime_scene_report;

Great! We have seen a snapshot of what kind of data our crime scene report contains. I will now filter this report to show me only entries that match the information I recall : day (2018-01-15), location (sql city) and type of crime (murder). An entry must meet all three criteria before it is selected.

SELECT *

FROM crime_scene_report

WHERE date = '20180115'

AND type = 'murder'

AND city LIKE '%SQL%'

I opted to use ‘LIKE’- keyword so that I do not miss any data because of spelling, capitalization and so on. We notice that there is only one entry that meets all 3 requirements. I want to read the entire description to get more clues so I will dive further into the description only.

SELECT description

FROM crime_scene_report

WHERE date = '20180115'

AND type = 'murder'

AND city LIKE '%SQL%'

Awesome ! I finally have more information than what I started out with. The crime occurred in a spot with CCTV. I have the first witness’ home address (Northwestern Dr) and the second witness’ name and home radius (Annabel, Franklin Ave)

Armed with this update, I head over to the ‘person’ database. Let’s see a snapshot of that database to be sure it can help us in our investigation.

SELECT *

FROM person

LIMIT 5;

The database contains information that will help me move forward in our investigation. I want to get all entries in that database containing information of any one who lives at Northwestern Dr and any Annabel’s living at Franklin Ave.

SELECT *

FROM person

WHERE address_street_name = 'Northwestern Dr'

or (name LIKE '%Annabel%'

    AND address_street_name = 'Franklin Ave');

That is too many people to sift through to arrive at the relevant two. What can I do to cut down on that ? Viola ! The first nameless witness is the last house on Northwestern Dr so it is reasonable to assume that their home will have the largest address number.

SELECT *

FROM person

WHERE (address_street_name = 'Northwestern Dr'

   AND address_number = (

     SELECT MAX(address_number)

     FROM person

     WHERE address_street_name = 'Northwestern Dr')

or (name LIKE '%Annabel%'

    AND address_street_name = 'Franklin Ave'));

Yes ! We have our two key witnesses now: Morty Schapiro (with id 14887) and Annabel Miller (with id 16371). All interviews are stored in the interview database using person id as the identifier to record what was divulged during the interview. Let’s find out what our witnesses said.

SELECT *

FROM interview

WHERE person_id IN ('14887' , '16371');

This is what the detective got from Annabel

“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".”

This is what the detective got from Morty

“I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.”

From the corroborated witness statements, we gather the following leads :

1. Suspect likely works out at a gym specifically ‘Get Fit Now Gym’
2. Suspect is possibly a Gold member of that gym (48Z).
3. Suspect’s car plate number contains ‘H42W’
4. Suspect worked out on January 9th

We look through ‘Get Fit Now Guy’s check-in database for who worked out on January 9th and with a Gold membership.

SELECT *

FROM get_fit_now_check_in

WHERE check_in_date = '20180109'

AND membership_id LIKE '%48Z%';

Only two members with id 48Z7A and 48Z55 met the required criteria. To get more information about these members, we will look into their member database.

SELECT *

FROM get_fit_now_member

WHERE id IN ('48Z7A' , '48Z55');

The id’s obtained match to the members Jeremy Bowers and Joe Germuska. Their id’s in our police database are 67318 and 28819 respectively.

Now that we have their person’s ids, we can look through the Driver and Vehicle Licensing Authority to see if we can match the witness’ statement to a vehicle.

SELECT *

FROM drivers_license

WHERE id IN ('67318 ','28819');

Curiously, this search did not yield any results. Our two persons of interest are not registered under DVLA. Where do we go from here ? Let’s do a quick search of all the people we have interacted with during this investigation in the DVLA database.

SELECT *

FROM drivers_license

WHERE id IN ('67318 ','28819', '14887' , '16371');

Still no dice. Since we are hitting a wall here, let’s tackle the problem from another angle. Let’s see which cars in the DVLA database have license plates matching the witness description.

SELECT *

FROM drivers_license

WHERE plate_number LIKE '%H42W%'

Ohk, we have obtained 3 people registered on the system that meet the required criteria. They have the license ids : 183779, 423327, and 664760. Let’s look through the person table to find out who they are.

It is noteworthy to mention that the reason I was previously hitting a wall was because the id’s in the drivers_license table were license_id, not person_id. I would have avoided the time wastage if I had confirmed this from the database schema before making that query.

SELECT *

FROM person

WHERE license_id IN (183779 , 423327 , 664760);

One name comes up again, Jeremy Bowers. Could he be the guy ? Let’s confirm with all the information we have collected so far.

SELECT p.*

FROM drivers_license AS dl

INNER JOIN person AS p

ON dl.id = p.license_id

INNER JOIN get_fit_now_member AS g

ON p.id = g.person_id

INNER JOIN get_fit_now_check_in AS c

ON g.id = c.membership_id

WHERE plate_number LIKE '%H42W%'

AND membership_status = 'gold'

AND check_in_date = '20180109';

Confirmed ! Jeremy committed the murder. I am ,however, not satisfied with this result. I have a hunch that there is more to this crime than meets the eye. Could Jeremy have an accomplice ?

Jeremy was taken in for questioning by the detective, let’s see what he had to say. We will query the interview table using his person id (67318) to achieve this.

SELECT *

FROM interview

WHERE person_id = '67318';

This was Jeremy’s confession :

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.

Searching through the DVLA database with Jeremy’s description, we discover that there are three red haired women of height range 65” to 67” who drive a Tesla.

SELECT *

FROM drivers_license

WHERE hair_color = 'red'

AND gender = 'female'

AND car_make = 'Tesla'

AND height >=65

AND height <=67;

There women have ids : 202298, 291182 and 918773. Let’s look through facebook event checkin to see who was at the SQL Symphony Concert.

SELECT p.name, fc.event_name ,COUNT(fc.person_id) AS number_of_events

FROM person AS p

INNER JOIN drivers_license AS dl

ON p.license_id = dl.id

INNER JOIN facebook_event_checkin AS fc

ON p.id = fc.person_id

WHERE license_id IN (202298,291182,918773)

GROUP BY p.name, fc.event_name;

Well ….. Well …. well , who do we have here ? Miranda Priestly seems to be the mastermind behind the murder.

Just to cover our bases, let us check to see if Miranda is indeed rich. What is the average income in our database ?

SELECT dls.gender, AVG(inco.annual_income) AS avgfem_annual_income

FROM drivers_license AS dls

INNER JOIN person AS ps

ON dls.id = ps.license_id

INNER JOIN income AS inco

on ps.ssn = inco.ssn

GROUP BY dls.gender;

The average income for women in our database is 53559.09. Miranda Priestly earns well above that with a whooping 310,000.

SELECT p.id, p.name, p.address_street_name, ssn, inc.annual_income

FROM person AS p

INNER JOIN income AS inc

USING (ssn)

WHERE name LIKE '%Miranda%';

She is definitely rich and checks all our boxes. My work is done, and I can go home now. See you next time a mystery needs solving with SQL.