SQL Murder Mystery - Review

2025-08-29

Found at mystery.knightlab.com, the SQL Murder Mystery game is a fun little exercise in using SQL to solve a murder mystery. The game is good for both beginners and masters alike, since there is no required way to solve the mystery and there is no time or query limit preventing your exploration. I highly recommend anyone with any SQL skills to give it a go. The rest of this article will serve as to explain how I solved it. Spoilers ahead!

Solution

Your first step will be to investigate what tables you can explore. The game provides your first query to list all the tables available to you:


SELECT name 
FROM sqlite_master
WHERE type = 'table'

The tables listed will include crime_scene_report, which the game prompts you to optionally explore the structure of. Since the game tells you that the murder happened in SQL City on January 15th 2018, that should inform you how to query the crime_scene_report table:


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

The result of the above query points you to the next steps of the investigation: finding the appropriate witness statements. We're told the first witness lives at the last house on Northwestern Dr, and the second witness is called Annabel and lives somewhere on Franklin Ave. Terrible police work by the crime scene investigator, but thankfully enough that we can find the first and second witnesses within the person table:


WITH first_witness AS (
    SELECT * 
    FROM person 
    WHERE address_street_name = 'Northwestern Dr' 
    ORDER BY address_number DESC 
    LIMIT 1
),
second_witness AS (
    SELECT * 
    FROM person 
    WHERE name LIKE 'Annabel%' 
    AND address_street_name = 'Franklin Ave'
)
SELECT * FROM first_witness
UNION
SELECT * FROM second_witness

The next step is to look at the interviews of our two witnesses. A simple query using the ids of the witnesses found in the previous query would suffice but you could definitely JOIN it with the interview table to get more content in one query, up to you. Once you found the interviews, you'll be given a series of clues to find the killer from each witness. You can find a single individual who fits all the clues, as follows:


SELECT name
FROM person
WHERE license_id in (
    SELECT id
    FROM drivers_license
    WHERE plate_number LIKE '%H42W%'
)
INTERSECT
SELECT name
FROM get_fit_now_member
WHERE membership_status = 'gold'

This gives you the single individual who fits all the clues, and is thus the killer. Entering the name into the solution checker will reveal that there was a mastermind behind the operation. Identifying the real villain can be done in two queries as the game suggests. Your first step is to find the interview with the killer and discover the clues to their master's identity, easily done by querying the interview table with the killer's id. The second step involves using all the clues from that interview. My approach is to create a subquery with a list of suspects from one query and intersecting it with another query with the suspects from the other clue, as follows:


WITH attendees AS (
SELECT person.name AS name, COUNT(*)
    FROM facebook_event_checkin
    JOIN person
    ON facebook_event_checkin.person_id = person.id
    WHERE event_name = 'SQL Symphony Concert'
    AND date >=20171200
    AND date <=20171231
    GROUP BY person_id
    HAVING count(*) = 3
)
SELECT name FROM attendees
INTERSECT
SELECT person.name
FROM person
JOIN drivers_license
ON person.license_id = drivers_license.id
WHERE height >= 65
AND height <=67
AND car_model = 'Model S'
AND hair_color = 'red'

Entering the resulting name into the solution checker will give you the game's victory speech, proclaiming you the greatest SQL detective of all time. Huzzah!

I had good fun playing this game and solving the mystery. I recommend it to anyone with any level of knowledge of SQL, as you can solve the mystery with simple queries and brute force or you can construct elaborate queries to solve it creatively and complicatedly.