SQL Murder Mystery Solution: Step-by-Step Walkthrough + Hints

Why does the SQL Murder Mystery reject the correct answer?

If you found the right person and the SQL Murder Mystery still says "That's not the right person. Try again!", look at these four causes first.

• You did height IN (65, 67)

  You get One woman, Red Korb. Check her name, and the game replies "That's not the right person. Try again!"

  Do this height BETWEEN 65 AND 67

  The killer says that she is "around" 65 or 67 inches tall. With IN, you keep only those two numbers, and the woman who hired him is 66 inches tall. See Part 2, step 2.

• You did INSERT INTO solution VALUES (1, 'jeremy bowers');

  You get "That's not the right person. Try again!" You get the same reply for a space after the name, or for two spaces between the names.

  Do this Type the name exactly as the person table has it, with a capital letter on each name and no extra spaces.

  SQLite compares your text with the answer character by character, so a different letter case or one extra space is a different name.

• You did The answer check, in the sqlite3 command-line tool on your computer

  You get An error that starts with no such column: "Congrats, you found the murderer!

  Do this .dbconfig dqs_dml on

  The replies in the answer check are in double quotes. New versions of SQLite read text in double quotes as a column name, so you get the error even for the correct name. Run the setting once, then run the INSERT again. In the browser game and in Python, you do not need the setting. See how to play on your computer.

• You did A date filter in step 1 of Knight Lab's own walkthrough page

  You get The word "Incorrect", although your query finds the correct report.

  Do this Keep date = 20180115. One report is the correct result.

  There is no date filter in the "show solution" query on that page, so you get 3 murders in SQL City. Two of them are from February 15, 2018. On the same page, Knight Lab tells you to disregard the word "incorrect" if you find one report. See Part 1, step 1.

SQL from other answers that fails in the SQL Murder Mystery

Some walkthroughs and AI answers print SQL with a wrong column name or the wrong letter case. Here is what you get from the game's database for each one.

Where are you stuck in the SQL Murder Mystery?

Find the line that matches what you have now in the SQL Murder Mystery, and go to that step.

How do you play the SQL Murder Mystery?

You play the SQL Murder Mystery in your browser, with no account and no install. You play in five steps:

1. Open mystery.knightlab.com and read the prompt: a murder, on January 15, 2018, in SQL City.
2. Look at the tables. The game runs on SQLite, so SELECT name FROM sqlite_master WHERE type = 'table' lists them. The diagram below shows how they link.
3. Write a query in one of the boxes and run it. Each result gives you a clue for the next query.
4. Follow the clues through the tables until one person is left.
5. Check your answer with the game's solution table.

To check an answer, run these two statements with the name that you found:

INSERT INTO solution VALUES (1, 'Name Here');
SELECT value FROM solution;

For a wrong name, you get the reply "That's not the right person. Try again!" For the killer's name, you get "Congrats, you found the murderer! But wait, there's more..." and that reply starts part 2.

What tables are in the SQL Murder Mystery database?

The SQL Murder Mystery database has 9 tables. Most of the case is about connecting them by their ids, so keep this diagram open while you play. The blue columns are the ones that you join on, with a line between each pair.

The links between tables: person.license_id = drivers_license.id, person.ssn = income.ssn, and person_id in the other tables = person.id. A gym visit links to a member by membership_id = get_fit_now_member.id.

Why does my SQL Murder Mystery query return no rows?

You get no rows in the SQL Murder Mystery when a filter does not match the data exactly. Look at three things first: the dates are numbers such as 20180115, the = sign matches letter case exactly, and a LIKE pattern can be too narrow. Each item is a wrong turn, with what you get and what to write.

• You typed date = '2018-01-15'

  You get 0 rows

  Write date = 20180115

  The date column is a number, not a date, so text never matches it. See Part 1, step 1.

• You typed type = 'Murder' or city = 'sql city'

  You get 0 rows

  Write type = 'murder' AND city = 'SQL City'

  With =, the letter case must match the data exactly. See Part 1, step 1.

• You typed address_street_name = 'Northwestern Drive'

  You get 0 rows

  Write address_street_name = 'Northwestern Dr'

  The data uses the short form of the street name, as the report does. See Part 1, step 2.

• You typed ORDER BY address_number LIMIT 1

  You get the wrong person: Kinsey Erickson, house 309

  Write ORDER BY address_number DESC LIMIT 1

  Without DESC, the sort goes from low to high, so you get the first house, not the last. See Part 1, step 2.

• You typed name = 'Annabel'

  You get 0 rows

  Write name LIKE 'Annabel%'

  The name column holds full names, so the first name alone matches nobody. See Part 1, step 3.

• You typed check_in_date = '2018-01-09'

  You get 0 rows

  Write check_in_date = 20180109

  Gym dates are numbers too. See Part 1, step 5.

• You typed plate_number LIKE 'H42W%'

  You get one plate, H42W0X, and the driver is a woman

  Write plate_number LIKE '%H42W%'

  The witness said the plate includes H42W, not that it starts with H42W. The pattern without the first % finds only one plate, and it belongs to a woman. See Part 1, step 6.

• You typed hair_color = 'Red' or car_model = 'Tesla Model S'

  You get 0 rows

  Write hair_color = 'red' AND car_make = 'Tesla' AND car_model = 'Model S'

  Hair colors are lowercase, and the make and the model are two columns. See Part 2, step 2.

• You typed JOIN income ON income.ssn = person.ssn

  You get 2 women, not 3

  Write LEFT JOIN income, or leave income out until the last query

  2,497 of the 10,011 people in the database have no income row. With an inner join, you lose them with no warning, and one of the three women is in that group. See Part 2, step 2.

• You typed WHERE event_name = 'SQL Symphony Concert' (no date)

  You get 25 people with 3 visits, not 2

  Write date BETWEEN 20171201 AND 20171231

  Without the date filter, you count visits from all of the data, not from December 2017. See Part 2, step 3.

SQL Murder Mystery walkthrough, part 1: who is the killer?

The killer in the SQL Murder Mystery is one man, and 7 queries get you from the crime scene report to his name. Each step shows a first hint, then the query and its real result. Open the second hint if the first one is not enough.

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

SELECT id, name, address_number, address_street_name
FROM person
WHERE address_street_name = 'Northwestern Dr'
ORDER BY address_number DESC
LIMIT 1;

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

SELECT person_id, transcript
FROM interview
WHERE person_id IN (14887, 16371);

SELECT get_fit_now_member.id, get_fit_now_member.name,
       get_fit_now_member.membership_status
FROM get_fit_now_member
JOIN get_fit_now_check_in
  ON get_fit_now_check_in.membership_id = get_fit_now_member.id
WHERE get_fit_now_member.id LIKE '48Z%'
  AND get_fit_now_member.membership_status = 'gold'
  AND get_fit_now_check_in.check_in_date = 20180109;

SELECT person.name, drivers_license.plate_number, drivers_license.gender
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE drivers_license.plate_number LIKE '%H42W%';

SELECT person.id, person.name
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
JOIN get_fit_now_member ON get_fit_now_member.person_id = person.id
WHERE drivers_license.plate_number LIKE '%H42W%'
  AND get_fit_now_member.id LIKE '48Z%'
  AND get_fit_now_member.membership_status = 'gold';

SELECT person.id, person.name
FROM facebook_event_checkin
JOIN person ON person.id = facebook_event_checkin.person_id
WHERE facebook_event_checkin.date = 20180115
  AND facebook_event_checkin.event_id IN (
    SELECT event_id
    FROM facebook_event_checkin
    WHERE person_id IN (14887, 16371)
      AND date = 20180115
  );

SQL Murder Mystery walkthrough, part 2: who hired the killer?

Part 2 of the SQL Murder Mystery starts from the killer's interview, and it ends with the name of the person who paid him. Each step shows a first hint, then the query and its real result.

SELECT transcript
FROM interview
WHERE person_id = 67318;

SELECT person.id, person.name, drivers_license.height
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
WHERE drivers_license.gender = 'female'
  AND drivers_license.hair_color = 'red'
  AND drivers_license.car_make = 'Tesla'
  AND drivers_license.car_model = 'Model S'
  AND drivers_license.height BETWEEN 65 AND 67;

SELECT person_id, COUNT(*) AS visits
FROM facebook_event_checkin
WHERE event_name = 'SQL Symphony Concert'
  AND date BETWEEN 20171201 AND 20171231
GROUP BY person_id
HAVING COUNT(*) = 3;

SELECT person.name, income.annual_income
FROM person
JOIN drivers_license ON drivers_license.id = person.license_id
JOIN income ON income.ssn = person.ssn
WHERE drivers_license.gender = 'female'
  AND drivers_license.hair_color = 'red'
  AND drivers_license.car_make = 'Tesla'
  AND drivers_license.car_model = 'Model S'
  AND person.id IN (
    SELECT person_id
    FROM facebook_event_checkin
    WHERE event_name = 'SQL Symphony Concert'
      AND date BETWEEN 20171201 AND 20171231
    GROUP BY person_id
    HAVING COUNT(*) = 3
  );

Who are the suspects in the SQL Murder Mystery, and why is each one out?

Six people in the SQL Murder Mystery match a clue but not the full case. For each person, you see the clue that they match and the reason that they are out. Only Jeremy Bowers matches every clue in part 1, and only Miranda Priestly matches every clue in part 2.

How does the SQL Murder Mystery check your answer?

Spoiler: this section shows how to read both names with no detective work.

The answer check in the SQL Murder Mystery is a SQLite trigger named check_solution on the solution table. You can read the trigger with this query:

SELECT sql FROM sqlite_master WHERE type = 'trigger';

Here is the start of the trigger, with the two long replies cut:

CREATE TRIGGER check_solution AFTER INSERT ON solution
    WHEN new.user==1
    BEGIN
        DELETE FROM solution;
        INSERT INTO solution VALUES (0,
        CASE WHEN hex(new.value)=='4A6572656D7920426F77657273' THEN "Congrats, you found the murderer! ..."
             WHEN hex(new.value)=='4D6972616E6461205072696573746C79' THEN "Congrats, you found the brains behind the murder! ..."
             ELSE "That's not the right person. Try again!"

• WHEN new.user==1: SQLite runs the check only when the first value is 1. With INSERT INTO solution VALUES (2, 'Jeremy Bowers'), you get your own text back, not a reply.
• hex(new.value): SQLite changes your text to hexadecimal and compares it with a fixed value. For this reason, a different letter case or an extra space is a wrong answer.
• The two fixed values are the two names in hexadecimal. Run this query to get the first one back as text:

SELECT CAST(X'4A6572656D7920426F77657273' AS TEXT) AS name;

Players call this the zero-query solution. You get the names, but you get no practice with SQL, and the practice is the point of the game.

How do you play the SQL Murder Mystery on your computer?

Download the SQL Murder Mystery database, sql-murder-mystery.db (3.6 MB). It is the same file that the browser game loads, and the game's GitHub repository has it too. The file is a SQLite database, so you can open it in SQLite, or copy it into PostgreSQL or MySQL. We ran each walkthrough query in SQLite 3.50, PostgreSQL 17.5 and MySQL 8.4, and we got the same rows in all three.

SQLite

Open the file with the sqlite3 command-line tool, list the tables, and turn on the setting for the answer check:

sqlite3 sql-murder-mystery.db
.tables
.dbconfig dqs_dml on

Then type the queries at the sqlite> prompt. Without .dbconfig dqs_dml on, you get the error "no such column" when you check an answer. A desktop program such as SQLiteStudio, which the game's repository suggests, also opens the file.

Python and pandas

In Python, you need only pandas to read the file:

import sqlite3
import pandas as pd

con = sqlite3.connect('sql-murder-mystery.db')
print(pd.read_sql("SELECT * FROM crime_scene_report WHERE date = 20180115 AND type = 'murder' AND city = 'SQL City'", con))

# The answer check works in Python with no extra setting
con.execute("INSERT INTO solution VALUES (1, 'Name Here')")
print(con.execute("SELECT value FROM solution").fetchone()[0])

PostgreSQL and MySQL

Make an empty database named smm, then copy the tables with this Python script. To run it, install pandas, SQLAlchemy and a driver: psycopg2-binary for PostgreSQL, or PyMySQL for MySQL. If your server needs a password, put it in the address, for example postgres:your_password@localhost.

import sqlite3
import pandas as pd
from sqlalchemy import create_engine

source = sqlite3.connect('sql-murder-mystery.db')
target = create_engine('postgresql+psycopg2://postgres@localhost/smm')
# For MySQL: create_engine('mysql+pymysql://root@localhost/smm')

tables = pd.read_sql("SELECT name FROM sqlite_master WHERE type = 'table' AND name != 'solution'", source)['name']
for table in tables:
    pd.read_sql(f'SELECT * FROM {table}', source).to_sql(table, target, index=False)

You can run the walkthrough queries with no change. Five things are different:

Which SQL do you need for the SQL Murder Mystery?

You can solve the SQL Murder Mystery with WHERE, LIKE and one join at a time. The walkthrough also joins three tables and uses GROUP BY, HAVING and one subquery, so that each step is one query. Each row names the first walkthrough step that uses the SQL, with a free lesson for it.

What should you play after the SQL Murder Mystery?

After the SQL Murder Mystery, play SQL Block Game. The murder case uses WHERE in every step, and ORDER BY, LIMIT and GROUP BY too. SQL Block Game drills those clauses at speed. Query cards fall, and you write the SQL that answers each one before it lands. It runs real PostgreSQL in your browser, it is free, and every run shuffles the cards.

For more detective games and how they compare, read our ranking of the best SQL games. For a story that teaches window functions and recursive CTEs, play Station Zero.