SQL Murder Mystery: hints, walkthrough and solution

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 page also has a schema diagram.
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;

A wrong name gets the reply "That's not the right person. Try again!" The killer's name gets "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 list open while you play.

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?

A SQL Murder Mystery query returns no rows 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 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. Open the second hint, and then the query, only when you need them.

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';

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, and the query is one click further.

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
  );

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.