AlephB · Articles

The Ritz Affair

A mystery you solve with SQL, in the browser · Play it

Paris, May 1912. The Blue Star, a famous sapphire, disappears from a suite at the Hotel Ritz, and Inspector Ganimard has more paper than men. You are his new clerk. You have no badge and no gun, only the archives: police reports, the hotel register, cab rides, telegrams, bank transfers. And the archives are a database.

The Ritz Affair is a detective story told in twelve chapters, where every step of the investigation is a query you write yourself. It runs entirely in the browser, needs no account and costs nothing.

Why a mystery

Most first SQL exercises ask a question whose answer nobody cares about: the average salary of a department that does not exist, the third most expensive product of an invented shop. Students write the query, check it against the correction, and forget it.

A mystery changes what a result means. When a query returns three rows instead of one, a student no longer asks "is this what the teacher wanted?" but "which of these three is lying?" They read their results, they doubt them, they refine the filter. That is the habit an analyst needs, and a story gives a reason to practise it.

The game also keeps the answer out of reach of guessing. Each chapter ends with a single fact to type in: a date, a name, a number plate, an address. Nothing is multiple choice, and the database holds some ninety thousand rows, so the only reliable way to the answer is the query.

One construct per chapter

The chapters follow the order of a first SQL course. Each one is built around one new construct, and the story is written so that this construct is the natural tool for the question it asks.

New tables appear in the schema beside the terminal as the story needs them. A student who starts the game sees two tables, not twenty, and the diagram grows chapter by chapter, with its keys and relationships drawn so the joins can be read off the picture.

When a student is stuck, the game does not give hints that hand out the answer. It names the chapter's construct and points at the tables just revealed, and The Clerk's Handbook explains each construct on a separate example database, a small bakery with customers, products and orders, so the explanations never spoil the plot.

Keeping the puzzles honest

A detective game is only as good as its clues, and a database makes cheating easy: sort by the newest row, search for the victim's name, try the obvious guess. A lot of the work on The Ritz Affair went into closing those doors.

The rows that are not evidence are not dull either. Among the hotel guests, cab fares and telegrams are references to films, novels and games, and a set of badges rewards the students who find them.

In class

The game has two modes. In learning mode a student plays alone at their own pace; progress is kept in their browser, a wrong answer costs nothing, and the twelve chapters make a good homework series or a self-study course.

In compete mode a teacher runs a timed session for the whole class. From a small admin page, the teacher creates a season and shares a link or a QR code. Each team picks a pseudonym and joins, and when the teacher opens the session every clock starts at once. A leaderboard, made to be shown on a projector, ranks the teams as they solve each chapter, with a time penalty for wrong answers.

Each season plays its own version of the database: the same story and the same tables, but every piece of evidence redrawn and a different question in each chapter, so a class that played an earlier season cannot pass its answers on.

Privacy

In learning mode nothing you type leaves your browser: the database is downloaded once and every query runs on your own machine. Compete mode sends only what the leaderboard needs: a pseudonym, the time each chapter was solved, and how many queries and wrong answers it took, never the queries or the answers themselves. Anonymous statistics are collected only if you agree to them. The details are in the game's privacy policy.

The story

The detective and the thief are borrowed from Maurice Leblanc: Ganimard, the patient inspector, and Arsène Lupin, the gentleman burglar who has made a fool of him for years. Everyone else is invented, and every name, sum and timetable in the archives is fiction. The pages look like a 1912 Paris newspaper, the suspects have painted portraits, and each part closes with a short film.

The game is free and needs nothing but a recent browser. Open the case.