Ghost in the SQL Data Walkthrough: All 9 Case Solutions and SQL Answers

Ghost in the SQL Data is a detective game where you solve crimes by writing real SQL against Paris case records. This Ghost in the SQL Data walkthrough covers all nine cases and 18 checkpoints, so you can see the logic behind each answer instead of just copying a query. If you are stuck on a single checkpoint or want the full solution set, this Ghost in the SQL Data walkthrough gives you both the reasoning and the exact SQL.

How Ghost in the SQL Data Works

The game has two modes. The Tutorial follows the College Jean Moulin school fire across three linked cases (1.1–1.3), where each solved question becomes the setup for the next. Standalone Cases hand you only the brief and the evidence board, testing whether you can build a query without hand-holding. Intermediate and advanced cases unlock after you clear the beginner ones.

The tutorial uses a "Desk" screen: case notes and the expected result shape on the left, your SQL editor on the right, and a results grid below that can switch between table, bar, line, pie chart, and KPI views. Standalone cases use an "Investigation Board" where photos, witness statements, maps, and table excerpts are pinned. You toggle the Case Files and Query Console panels with buttons at the top.

Three rules govern every puzzle:

• Answers are result rows. The expected result panel tells you how many rows and which columns you need.
• Condition words live in the evidence. Dates, times, room names, brands, and badge numbers appear highlighted in the case files and go directly into WHERE clauses.
• Filter one condition at a time. Start with SELECT *, then add date, then location, and watch the row count drop.

Values stay in English exactly as they appear in the tables. Times use the '21:12' format and can be compared with < and >. Dates use '2025-10-14'.

Tutorial Cases: The School Fire (1.1–1.3)

On the night of October 14, the Science Wing of College Jean Moulin burned. The tutorial traces the call, the room it came from, and the caller.

Case 1.1: Introduction

The dispatch_log table holds 2,400 emergency calls from a full year. Your target is the single fire report placed before 21:12, the moment the first witness saw flames.

The final query:

SELECT * FROM dispatch_log
WHERE on_date = '2025-10-14'
  AND kind = 'Phone Call'
  AND subject = 'Fire'
  AND site = 'College Jean Moulin'
  AND at_time < '21:12';

The answer is a 20:58 call from LINE 4417. Skip the site condition and you get two rows, because a fire at Rialto Cinema was reported at 19:32 the same night.

Case 1.2: JOIN

The phone company ledger line_register (30 rows) maps each line to a subscriber and room. dispatch_log.source matches line_register.line.

Joining the two tables on October 14 returns 9 rows instead of 16, because calls from payphones, alarm panels, and patrol cars have no ledger entry and drop out of the join. LINE 4417 is registered to three rooms by time slot:

At 20:58 the call came from the Lodge. The query:

SELECT room, at_time
FROM dispatch_log
JOIN line_register ON source = line
WHERE source = 'LINE 4417'
  AND on_date = '2025-10-14'
  AND hours = '18:00-22:00';

Case 1.3: ORDER BY and LIMIT

The Lodge is the caretaker's room. The keycard_log table (105 rows) records badge swipes. To find who last passed through the Main Gate that day, sort descending and take one row:

SELECT holder, door, at_time
FROM keycard_log
WHERE on_date = '2025-10-14' AND door = 'Main Gate'
ORDER BY at_time DESC
LIMIT 1;

That is Ruth Abasi at 21:19. Swap the door to Lodge and add at_time < '20:58' and you get Victor Pell at 20:44. His movement log shows Boiler Room at 20:35, Science Wing at 20:41, then Lodge — contradicting his statement. The answer row is his Boiler Room swipe:

SELECT holder, door, on_date, at_time FROM keycard_log
WHERE holder = 'Victor Pell' AND on_date = '2025-10-14' AND door = 'Boiler Room';

Standalone Case Solutions

The Blue House (Beginner)

Five houses in Villa Godin were painted blue overnight, leaving a can of Brikoko Blue Paint. shop_sales holds 1,240 hardware store transactions; loyalty_cards maps 260 cards to owners and neighborhoods.

Filtering by brand and item alone gives three cardholders. Adding the neighborhood narrows it to one:

SELECT DISTINCT loyalty_cards.card, holder, house, neighbourhood
FROM shop_sales
JOIN loyalty_cards ON shop_sales.card = loyalty_cards.card
WHERE item = 'Blue Paint' AND brand = 'Brikoko'
  AND neighbourhood = 'Saint Fargeau';

The answer is Miles Greaves, house number 7. Drop the brand and buyers of Dulvo and Greenway blue paint flood the results.

The Hospital Stalker (Beginner)

Three nurses were attacked at Hopital Lamarck right after leaving the Drug Reserve. door_log records entry-only badge swipes; staff_badges holds 20 badge holders and roles.

The answer row comes from the badge table:

SELECT * FROM staff_badges WHERE holder = 'Colin Marsh';

The second checkpoint asks who is next. Only nurses know the Drug Reserve code, so you self-join door_log to find which nurse Colin followed within three minutes on Friday. The result is Owen Clarke:

SELECT * FROM staff_badges WHERE holder = 'Owen Clarke';

The Next Target (Intermediate)

Six Paris banks were robbed from June onward. The banks table covers 100 branches with a security_score assigned by police. Sort the robbed branches by date and a pattern appears: every hit had a vault, Okonjo Insurance, at least 110,000 in cash, and a security score climbing by two each time — 20, 22, 24, 26, 28, 30. The seventh target should score 32.

SELECT bank, bank_id, sector, security_score, vault_cash FROM banks
WHERE status = 'Open' AND kind = 'Vault'
  AND insurer = 'Okonjo Insurance' AND vault_cash >= 110000
ORDER BY security_score;

Only Civique Mozart (B240) sits at 32.

The Orsay Crown (Intermediate)

A ceremonial crown vanished from the Orsay Reserve on November 19. badge_passages logs both in and out directions; badge_holders covers 224 badges.

Checkpoint 1 looks for badges in the Reserve between 17:30 (when the crown was stored) and 18:40 (when the empty case was found). Only registrar Etienne Vasseur (B-0218) appears — but he reported his badge stolen while he was ill in the Restrooms.

Checkpoint 2 finds the thief by counting entries versus exits per badge:

SELECT h.last_name, h.first_name, h.badge_id, h.role
FROM badge_holders h
JOIN badge_passages p ON p.badge_id = h.badge_id
WHERE p.room = 'Restrooms'
GROUP BY h.badge_id
HAVING SUM(p.direction = 'in') > SUM(p.direction = 'out');

The answer is Hugo Delorme, Visitor badge B-0109, who entered the Restrooms at 17:33 and never exited on his own badge.

The Nameless Ticket (Intermediate)

An elderly woman was knocked down on rue Ramponeau, leaving an unregistered 10-ride ticket C-88291. supports groups cards bought in one payment by payment_id; validations holds two months of gate records.

First, find the payment: C-88291 belongs to payment V-88410, which covers 10 cards bought with cash at Gare du Nord on September 28 at 07:12. Then check morning validations for those cards:

SELECT v.on_date, v.at_time, v.station, v.line, v.direction
FROM validations v JOIN supports s ON s.card_id = v.card_id
WHERE s.payment_id = 'V-88410' AND v.at_time < '09:00'
ORDER BY v.on_date, v.at_time;

The man enters on M2 every morning and exits on M13. The only interchange between those lines is Place de Clichy, so you type the station name directly:

SELECT 'Place de Clichy' AS kiosk;

Elixir V (Advanced)

Botanist Odile Vernet wants to know what went into Elixir V, made from three flowers. The flowers table holds 52 analyzed specimens. Two flowers are fresh and one is dried, and the mixture must satisfy three criteria: A > 22, ROUND(K, 2) = 29, and F between 17 and 21. The formulas are pinned to the board, and the dried flower's values are multiplied by (1 - moisture).

The criteria alone leave several combinations. The notebook is dated July 5 and all three flowers bloomed in an unheated greenhouse, so you restrict seasons to summer or year-round:

SELECT a.common_name AS flower_a, b.common_name AS flower_b, c.common_name AS flower_c
FROM flowers AS a, flowers AS b, flowers AS c
WHERE a.state = 'fresh' AND b.state = 'fresh' AND c.state = 'dried'
  AND 300 * (1.0 / a.dpph_ic50 + 1.0 / b.dpph_ic50 + (1 - c.moisture) / c.dpph_ic50) > 22
  AND ROUND(270 * (1.0 / a.nfkb_ic50 + 1.0 / b.nfkb_ic50 + (1 - c.moisture) / c.nfkb_ic50), 2) = 29
  AND (a.phenols_100g + b.phenols_100g + (1 - c.moisture) * c.phenols_100g)
      / (270 * (1.0 / a.nfkb_ic50 + 1.0 / b.nfkb_ic50 + (1 - c.moisture) / c.nfkb_ic50) + 1) BETWEEN 17 AND 21
  AND a.season IN ('summer', 'ALL') AND b.season IN ('summer', 'ALL') AND c.season IN ('summer', 'ALL')
LIMIT 1;

The answer is fresh German Chamomile, fresh Lemon Balm, and dried True Lavender.

Five Techniques That Solve Every Case

These patterns repeat across the whole game, and community reports confirm they cover every checkpoint.

• Suspect missing rows after a JOIN. In Case 1.2 the 16 calls became 9 rows because unregistered sources have no ledger entry. The same thing happens with cash customers in The Blue House.
• Always include the date with time conditions. A bare at_time < '20:58' matches records from other days too.
• Alias the same table repeatedly. The hospital case uses door_log twice, once as m and once as v, joined on matching door and date with a three-minute window.
• Sort before hunting for patterns. The bank case has no answer written anywhere — the rule only appears once you order by robbed_on.
• Use DISTINCT when joins duplicate rows. A cardholder with multiple paint purchases would otherwise appear several times.

For general SQL syntax refreshers, the official SQL SELECT reference from W3Schools covers every clause used here.

Hints, Language, and Quality-of-Life Settings

Hints unlock in order: Clue tells you where to look, Hint 1 explains in words, Hint 2 gives the query skeleton, and Hint 3 is the full answer. You cannot skip ahead. The SQL Guide in the top-right corner offers per-clause cards with short examples and step-by-step results.

The game supports 10 languages including Japanese and English. Menus, case files, guides, and hints translate, but table names, column names, values, and character or place names stay in English — which is exactly what you type into queries. Settings include display options, text size, dark mode, sound and music volume, and a streamer mode.

FAQ

How many cases are in Ghost in the SQL Data? There are nine cases: three tutorial chapters about the school fire and six standalone cases, totaling 18 checkpoints. Every one is solvable with a few lines of SQL.

Do I need SQL experience to finish this Ghost in the SQL Data walkthrough? No. The tutorial teaches SELECT, WHERE, JOIN, ORDER BY, and LIMIT from scratch. Later cases introduce GROUP BY, subqueries, and self-joins, but the staged hints walk you to each answer.

Why does my JOIN return fewer rows than expected? Inner joins drop rows with no match in the second table. Calls from payphones or purchases without a loyalty card simply disappear. If the count looks wrong, check which values exist in only one table.

Can I break the case records with a bad query? No. Only read-only SELECT and WITH statements are allowed, so nothing you type can alter the evidence.

How do I answer with a name or station instead of a table row? Wrap the value in single quotes and give it a column alias, like SELECT 'Place de Clichy' AS kiosk;. The Verify button compares your output rows to the expected shape.

With these solutions and techniques, every case in this Ghost in the SQL Data walkthrough should fall in a few queries. Work the filters one at a time, watch the row counts, and the deductions write themselves.