The DS52 cohort were recently introduced to SQL and after an intensive crash course spanning one and a half days were given a challenge to test what we have learned. The challenge was to solve a murder using SQL with a few tables and a corresponding schema. Not exactly the usual data prepping challenges we were used to. Before we get started a query will generally use the following syntax:
SELECT - This selects what fields are shown in the returned able
FROM - This decides what table the query is getting data from
WHERE - This acts as a filter
GROUP BY - How aggregated fields in SELECT are grouped
HAVING - This acts as a filter for aggregated fields
ORDER BY - This decides the sort order of the returned table
Now let's begin.
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.
Using the first clue and the above schema we can now write our first SQL query.
SELECT * FROM crime_scene_report
WHERE city='SQL City' AND type='murder' AND date=20180115
The * after SELECT denotes that all fields are to be returned.
From the above we are now looking to identify the two witnesses. For the first witness, we can see that they live on the last house on Northwestern Dr so we can write the following query.
SELECT * FROM person
WHERE address_street_name='Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1
This query filters to the specific address street name and is ordered by the street number with the highest number at the top. Including limit 1 just insures we only get that top value as shown below.
To get the second witness we would use the following query given we know the first name of the witness and where they live.
SELECT * FROM person
WHERE address_street_name='Franklin Ave' AND name LIKE 'Annabel%'
The % acts as a wildcard in SQL with LIKE to search for patterns in the name field where Annabel is followed by anything else.
Now that we have identified the two witnesses, we want to obtain their interviews from the interview table. To do this we need to use a key field to to connect from the person table to the interview table. In this case, it is the id in the person table which corresponds to person_id in the interview table. Therefore, we can write the following query:
SELECT
p.name,
i.*
FROM interview AS i
JOIN person AS p ON i.person_id=p.id
WHERE person_id=16371 OR person_id=14887
The JOIN statement will add on information from the person table so we don't have to remember which person_id belongs to which witness. Using AS allows us to assign and alias to a field or table so it is easier to refer to. The condition following ON is where the common fields are matched together. The SELECT statement will now return name from the person table and everything in the interview table.
From the above interviews, it appears the killer is a gold member at the Get Fit Now Gym, was at the gym on January the 9th, and has a membership number starting with 48Z.
SELECT
c.membership_id,
c.check_in_date,
m.person_id,
m.name,
m.membership_status
FROM get_fit_now_check_in AS C
JOIN get_fit_now_member AS M
ON C.membership_id=M.id
WHERE check_in_date=20180109 AND membership_status='gold' AND M.id LIKE '48Z%'
The query above reveals two potential suspects:
Using the above person_id details we can search the drivers_license table to see which of these individuals has a license plate number including H42W.
SELECT
P.name,
P.id,
D.plate_number
FROM drivers_license as D
JOIN person AS P
ON P.license_id=D.id
WHERE plate_number LIKE'%H42W%' AND (P.id=28819 OR P.id=67318)
The result below shows that Jeremy Bowers has a license plate including H42W and therefore must be the killer!
However, if we have a look at Jeremy Bowers' interview we can see that there is a bit of a plot twist...
SELECT * FROM interview
WHERE person_id=67318
We may have found the killer but it appears they were hired by a woman with red hair, of a height 65" to 67", who drives a Tesla Model S.
SELECT
p.id,
p.name,
d.hair_color,
d.height,
d.car_make,
d.car_model
FROM drivers_license as d
JOIN person as p
ON p.license_id=d.id
WHERE hair_color='red' AND (height BETWEEN 65 AND 67) AND car_make='Tesla'
Using the above query we can see that there are three women that fit the above description:
Now if we check for these names and see if any of them attended the SQL Symphony Concert in December three times.
SELECT
fb.event_name,
fb.date,
p.name
FROM facebook_event_checkin AS fb
JOIN person as p
ON p.id=fb.person_id
WHERE name='Miranda Priestly' OR name='Red Korb' OR name='Regina George'
And as we can see there is only one name that appears and this woman did in fact attend the SQL Symphony Concert three times in December.
There we have it, Jeremy Bowers was the killer but was hired by Miranda Priestly.
This was a really fun challenge that helps you understand the fundamentals of working with different tables and combining information from these tables using SQL.