<jupyter_start><jupyter_text>"SQL Practice"> "Solving murder mysteries one query at a time"- toc: true- branch: master- badges: true- comments: true- categories: [sql, python]- image: images/sql/sql-server.png ![](../images/sql/sql-server.png) Let's solve a mystery hidden in an sql database. The instructions and database was created by <NAME> and <NAME> while fellows at the Knight Lab at Northwesten University. Here's a step by step solution, though if you'd like to try it yourself first, check out [their interactive site](https://mystery.knightlab.com/).Here's the description from the knight lab: > 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.   The setup We'll use pandas to make the queries look nice and sqlite3 to make the connection.I'm also adding a function pq to quickly grab the results of a query based on the value of the query variable.<jupyter_code>#collapse
import pandas as pd
import sqlite3
from sqlite3 import Error
from pathlib import Path
def create_connection(path):

    connection = None

    try:

        connection = sqlite3.connect(path)

        print("Connection to SQLite DB successful")

    except Error as e:

        print(f"The error '{e}' occurred")

    return connection
db = Path("../datasets/sql-murder-mystery.db")

conn = create_connection(db)<jupyter_output>Connection to SQLite DB successful<jupyter_text>Taking a look at all the tables by selecting _name_ filtering by _table_.<jupyter_code>def pq(query, conn=conn):
    """Returns a sql query as a pandas dataframe"""
    return pd.read_sql(query, conn)
query = """SELECT name 
  FROM sqlite_master
 where type = 'table'"""
pq(query)<jupyter_output><empty_output><jupyter_text>In order to determine the structure of each table, we can run a query like this:<jupyter_code>query = """
    SELECT sql 
    FROM sqlite_master
    WHERE name = 'crime_scene_report'
    """
print(pq(query).iloc[0, 0])<jupyter_output>CREATE TABLE crime_scene_report (
        date integer,
        type text,
        description text,
        city text
    )<jupyter_text>Now that we have the database loaded and the query print function, we're ready to tackle this murder mystery.   Solving the mystery  Finding the witnesses<jupyter_code>query = """
SELECT *
FROM crime_scene_report
LIMIT 5"""
pq(query)<jupyter_output><empty_output><jupyter_text>Let's start with what we know. According to the prompt, we know that this murder took place on Jan. 15 2018 in SQL City. This should be enough info to explore crime scene reports. We care about the description, and want to filter by __city__, __date__, and __type__.<jupyter_code>query = """
SELECT description 
FROM crime_scene_report 
WHERE city = 'SQL City' AND type = 'murder' AND date = 20180115"""

print(pq(query).iloc[0, 0])<jupyter_output>Security footage shows that there were 2 witnesses. The first witness lives at the last house on "Northwestern Dr". The second witness, named Annabel, lives somewhere on "Franklin Ave".<jupyter_text>Finding the interviews Located witnesses presumably interviewed by detectives. Let's take a quick look the interview table<jupyter_code>query = """
SELECT * 
FROM interview
LIMIT 5
"""

pq(query)<jupyter_output><empty_output><jupyter_text>Not too useful (yet). We need to figure out how the __person_id__ connects<jupyter_code>#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'interview'"""
print(pq(query).iloc[0, 0])<jupyter_output>CREATE TABLE interview (
        person_id integer,
        transcript text,
        FOREIGN KEY (person_id) REFERENCES person(id)
    )<jupyter_text>AHA! So let's connect person_id from the person table.<jupyter_code>#collapse
query = """
SELECT sql 
FROM sqlite_master
WHERE name = 'person'"""
print(pq(query).iloc[0, 0])<jupyter_output>CREATE TABLE person (
        id integer PRIMARY KEY,
        name text,
        license_id integer,
        address_number integer,
        address_street_name text,
        ssn integer,
        FOREIGN KEY (license_id) REFERENCES drivers_license(id)
    )<jupyter_text>The clues we have are: *  Annabel, who lives on "Franklin Ave" *  Someone else who lives on the last house on "Northwestern Dr" Once we join both tables, a subquery will help capture the last house by finding the largest street number for Northwestern Dr. We can use an fstring to inject the subquery, making it a bit more readable.<jupyter_code>subq = """
SELECT MAX(address_number) 
FROM person 
WHERE address_street_name = 'Northwestern Dr'
"""

query = f"""
SELECT transcript
FROM person AS p
INNER JOIN interview AS i ON p.id = i.person_id
WHERE (address_street_name = 'Franklin Ave' AND name LIKE 'Annabel%') OR (address_street_name = 'Northwestern Dr' AND address_number IN ({subq}));
"""
[print(f"{v}\n") for v in pq(query)["transcript"]]<jupyter_output>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".

I saw the murder happen, and I recognized the killer from my gym when I was working out last week on January the 9th.<jupyter_text>Examining the crime scene  Let's extract the salient points: * Suspect was at the gym on __Jan 9th__* Suspect (same?) __male__ and has __Get Fit Now Bag__, presumably a member with __"48Z"__ as the starting membership id. __Gold member__ bag. Car connected to suspect has plate number partial of __"H42W"__ Taking a look at gym data:<jupyter_code>#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'get_fit_now_check_in'"""
print(pq(query).iloc[0, 0])
#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'get_fit_now_member'"""
print(pq(query).iloc[0, 0])
query = """
SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND membership_id LIKE '48Z%'
"""
pq(query)<jupyter_output><empty_output><jupyter_text>This is useful. Let's see if we can combine this with the info from the other witness to isolate a single suspect.<jupyter_code>subq = """
SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND membership_id LIKE '48Z%'
"""

query = f"""
SELECT *
FROM ({subq}) AS c
INNER JOIN get_fit_now_member AS m ON m.id = c.membership_id
"""
pq(query)<jupyter_output><empty_output><jupyter_text>We can take advantage of the 'free' filtration by performing an inner join on the membership id. Unfortunately we're stuck between two people. Our last resort is the driver's license.   Finding the murderer<jupyter_code>#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'drivers_license'"""
print(pq(query).iloc[0, 0])
#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'person'"""
print(pq(query).iloc[0, 0])<jupyter_output>CREATE TABLE person (
        id integer PRIMARY KEY,
        name text,
        license_id integer,
        address_number integer,
        address_street_name text,
        ssn integer,
        FOREIGN KEY (license_id) REFERENCES drivers_license(id)
    )<jupyter_text>Lots of interesting info in these tables, so lets select everything that has a pretty good match.<jupyter_code>query = """
SELECT *
FROM drivers_license
WHERE plate_number LIKE '%H42W%' AND gender = 'male'
"""
pq(query)<jupyter_output><empty_output><jupyter_text>Interesting... lets see if it matches either of our gym members.<jupyter_code>subq = """
SELECT *
FROM drivers_license
WHERE plate_number LIKE '%H42W%' AND gender = 'male'
"""

query = f"""
SELECT *
FROM ({subq}) d
INNER JOIN person p ON d.id = p.license_id"""

pq(query)<jupyter_output><empty_output><jupyter_text>Ah! We found the suspect in the list, but let's create a query that combines it all.<jupyter_code>g_subq = """
SELECT *
FROM get_fit_now_check_in
WHERE check_in_date = 20180109 AND membership_id LIKE '48Z%'
"""

g_query = f"""
SELECT name
FROM ({g_subq}) AS c
INNER JOIN get_fit_now_member AS m ON m.id = c.membership_id
"""


subq = """
SELECT *
FROM drivers_license
WHERE plate_number LIKE '%H42W%' AND gender = 'male'
"""

query = f"""
SELECT *
FROM ({subq}) d
INNER JOIN person p ON d.id = p.license_id
WHERE name IN ({g_query})
"""
pq(query)<jupyter_output><empty_output><jupyter_text>Perhaps we found the murderer? Let's see if there's anything else we can find out about the suspect. Perhaps he was interviewed?<jupyter_code>query = """
SELECT *
FROM interview
WHERE person_id = 67318
"""
print(pq(query).iloc[0, 1])<jupyter_output>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.<jupyter_text>It turns out we're not done yet!  Finding the real murderer (bonus round) * Between 65" AND 67"* Red hair* High income * Tesla Model S* Checkin SQL Symphony Concert 3 times in December 2017<jupyter_code>query = """
SELECT * 
FROM drivers_license 
WHERE height BETWEEN 65 AND 67 
    AND car_make = 'Tesla' 
    AND gender = 'female' 
    AND hair_color = 'red'

"""

pq(query)
#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'facebook_event_checkin'"""
print(pq(query).iloc[0, 0])
#collapse
query = """SELECT sql 
  FROM sqlite_master
 where name = 'income'"""
print(pq(query).iloc[0, 0])<jupyter_output>CREATE TABLE income (
        ssn integer PRIMARY KEY,
        annual_income integer
    )<jupyter_text>We can create a single query and combine all<jupyter_code>query = """
SELECT name, annual_income
FROM facebook_event_checkin f
INNER JOIN person p ON p.id = f.person_id 
INNER JOIN drivers_license d ON p.license_id = d.id
INNER JOIN income i ON p.ssn = i.ssn
WHERE date BETWEEN 20171200 AND 20171231
    AND event_name = 'SQL Symphony Concert'
    AND height BETWEEN 65 AND 67
    AND hair_color = 'red'
    AND gender = 'female'
    AND car_make = 'Tesla'
    AND car_model = 'Model S'
GROUP BY person_id
HAVING COUNT(person_id) > 2
"""

pq(query)<jupyter_output><empty_output>
StarcoderdataJupyterStructuredCleanDedup