Insights with Iman

Detective SQL

Detective SQL

Iman Adeko's photo
Iman Adeko
·

5 min read

Problem Statement

A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. 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.

You can download the database file here. If you're interested in coding along while you read, you can visit www.sqliteonline.com and load the database you downloaded earlier.

Before I begin to solve the crime, we need to familiarize ourselves with the tables in the police department's database. Below is the schema diagram.

The two details we remembered of the crime were that:

  • it was a murder, and

  • it took occurred on Jan 15th, 2018, in SQL City.

From the crime_scene_report table, I checked for crimes that fit this description.

SELECT *
FROM crime_scene_report
WHERE date = 20180115
    AND type = 'murder'
    AND city = 'SQL City';

Only 1 crime fits all the descriptions above. In the police's description of that crime, there were 2 witnesses:

  • the first witness lives on Northwestern Dr Street,

  • while the second witness goes by the name Annabel and lives on Franklin Ave.

I checked the person table for anyone whose name is Annabel and lives on Franklin Ave.

SELECT id, name
FROM person
WHERE name LIKE '%Annabel%'
        AND address_street_name = 'Franklin Ave';

The second witness' full name is Annabel Miller and her id is 16371.

To know the location of the crime scene, I checked the facebook_event_checkin table to know which event Annabel attended on January 15th, 2018.

SELECT event_id, event_name
FROM facebook_event_checkin
WHERE person_id = 16371
        AND date = 20180115;

The event Annabel attended on the day of the crime is The Funky Grooves Tour, and the event has an event_id 4719. This implies that the murder occured at this event.

I proceeded to check the names and ids of other attendees of The Funky Grooves Tour.

SELECT p.id, p.name
FROM facebook_event_checkin AS f
JOIN person AS p
    ON f.person_id = p.id
WHERE event_id = 4719
    AND date = 20180115
    AND person_id != 16371;          -- Excluding Annabel's id

The other people present at The Funky Grooves Tour are:

  • Morty Schapiro with id 14887

  • Jeremy Bowers with id 67318

Since the first witness, the one who lives on Northwestern Dr, will be either Morty or Jeremy, I checked to see which one of them it is.

SELECT name
FROM person
WHERE id IN (14887, 67318)                -- IDs of Morty and Jeremy
    AND address_street_name = 'Northwestern Dr';

The first witness is Morty Schapiro.

Now that we know who the two witnesses are, I checked the interview table for the transcript of the statement they gave to the police.

SELECT p.name, i.transcript
FROm interview AS i
JOIN person AS p
    ON i.person_id = p.id
WHERE p.id IN (14887, 16371);          --IDs of Morty and Annabel

Morty told the police that he heard a gunshot, and then saw a man with a gym bag running out of the building. Annabel said that she recognized the killer from her gym on January 9th. They're most likely referring to the same person.

The full description Morty and Annabel gave of the killer are:

  • he is a male

  • he had a gym bag with a membership number starting with 48Z

  • only gold members of the gym had the bag he was carrying

  • he got into a car with a number plate that included H42W

  • Annabel saw him in the gym on January 9th, 2018

I included these descriptions in the WHERE clause of my query to find out the killer's name and ID.

SELECT p.id, p.name 
FROM person as p 
JOIN drivers_license AS dl
    ON p.license_id = dl.id
JOIN get_fit_now_member AS gm
    ON gm.person_id = p.id 
JOIN get_fit_now_check_in AS gc
    ON gc.membership_id = gm.id
WHERE dl.gender = 'male'
    AND gm.id LIKE '48Z%'
    AND gm.membership_status = 'gold'
    AND dl.plate_number LIKE '%H42W%'
    AND gc.check_in_date = 20180109;

Jeremy Bowers (ID 67318) fits all the descriptions Morty and Annabel gave of the killer. He was also an attendee of the event where the crime happened, so he is killer.

I didn't stop here though, I went ahead to check the interview table for the transcript of his statement.

SELECT *
FROM interview
WHERE person_id = 67318;           --Jeremy's ID

Jeremy said he was hired by a woman, and he described the woman as follows:

  • she has a lot of money

  • her height is between 5'5" (65") and 5'7" (67")

  • she has red hair

  • she drives a Tesla Model S

  • she attended the SQL Symphony Concert three times in December 2017

Before adding the descriptions to my WHERE clause, I checked the income table to find the minimum and maximum annual income, so that I could know their range.

SELECT MIN(annual_income), MAX(annual_income)
FROM income;

The annual income ranged from 10k to about 500k. Therefore, the woman who hired Jeremy should have an annual income closer to 500k than to 10k.

SELECT p.id, p.name, i.annual_income, f.date
FROM facebook_event_checkin as f
JOIN person AS p
    ON f.person_id = p.id
JOIN drivers_license as d 
     ON p.license_id = d.id
JOIN income as i 
     ON p.ssn = i.ssn
WHERE d.gender = 'female'
    AND d.height BETWEEN 65 AND 67
    AND d.hair_color = 'red'
    AND d.car_make = 'Tesla'
    AND d.car_model = 'Model S'
    AND f.event_name LIKE '%SQL Symphony Concert%';

Only one woman who matches the descriptions above attended the SQL Symphony Concert three times in December 2017. She is Miranda Priestlyand she earns over 300k annually.

In conclusion, a murder was committed at The Funky Grooves Tour event in SQL City on January 15, 2018. Annabel Miller and Morty Schapiro, both attendees at the event, witnessed the crime. Jeremy Bowers, also an attendee, committed the murder. Miranda Priestly hired Jeremy Bowers to commit the murder.

I hope you enjoyed reading my blog post on how I solved a murder mystery using SQL. I was really happy to be able to share my experience with you, and I hope that you learned a thing or two about how to use SQL to solve problems.

I would love to hear your feedback on my blog post. Did you find it helpful? Did you learn anything new? Please leave a comment below and let me know what you think.

Thank you for reading!

SQLSQL Server