Quackyard
  • Loading…

SQL Detective

Find who took the Golden Duck, one SQL query at a time, on real Postgres in your browser.

Setting up the case board…

Case 1 of 14 · SELECT

The warden’s notebook

Friday 13 March, ten past eleven at night. The Golden Duck is gone from the pond room, and Otto, the night warden, has called you in. Start with his notebook: show when each note was written and what it says.

SELECT lists the columns you want, and FROM names the table they live in. You don’t have to take every column: name just the ones you need, split by commas.

0 characters · par 34
Ctrl+Enter or ⌘+Enter runs it.
Case files
notebookid, page, noted_at, note, ink

The night warden’s notebook.

  • id integer the entry number
  • page integer the page it is on
  • noted_at timestamp when it was written
  • note text what Otto wrote
  • ink text the pen he used
peopleid, name, role, joined_on

Everyone who works in the yard.

  • id integer the person’s number
  • name text full name
  • role text their job
  • joined_on date their first day
badgescode, person_id, issued_on, lost_on

Door badges. A lost badge gets a date in lost_on, and its owner gets a new one.

  • code text the code printed on it
  • person_id integer whose badge it is
  • issued_on date handed out on
  • lost_on date reported lost on; empty while in use
door_logid, badge, door, at, opened

Every badge swipe on 13 March, at every door with a reader.

  • id integer the swipe number
  • badge text the badge swiped
  • door text where: main gate, back gate, workshop, cellar, pond room, boathouse, office
  • at timestamp when
  • opened boolean false when the door refused the badge
cafe_salesid, badge, item, sold_at, price

The café till on 13 March. Staff pay with their badge, and the items are typed by hand.

  • id integer the sale number
  • badge text the badge that paid
  • item text what was bought, as typed
  • sold_at timestamp when
  • price numeric(5,2) in pounds
messagesid, sender_id, receiver_id, sent_at, body

Messages sent on the yard’s phones.

  • id integer the message number
  • sender_id integer who sent it
  • receiver_id integer who got it
  • sent_at timestamp when
  • body text what it says
sightingsid, person_id, camera, seen_at

Who the cameras saw between 22:00 and 23:00. There is no camera in the pond room.

  • id integer the sighting number
  • person_id integer who was seen
  • camera text which camera: boathouse, café or main gate
  • seen_at timestamp when

Opening the case files…

How to play

The Golden Duck is gone from the yard’s pond room, and you find the thief by querying the yard’s records. Across the top, 14 numbered stops make the level map; each case opens once the one before it is solved. Below it, the case board pins a clue card for every solved case. Tap anything on the board to read it again.

Each case gives a short story, a one-paragraph lesson and the answer’s shape, such as “The answer has 2 columns, in this order: noted_at, note”. Type in Your query and press Run query or Ctrl+Enter (⌘+Enter on a Mac). Case files lists the seven tables; click one for its column types. Ask the duck shows three hints, one per press.

A case is judged by its result, not its wording: your rows must match the reference query’s, with the same number of columns. Column names don’t count, and row order matters only in Last ones in. Results come back on an Evidence sheet stamped Match or No match. Errors appear under Postgres says in Postgres’s own words, and writes are turned away under The duck says.

The score is your query’s length in characters, lower being better. At or under par earns gold, within 1.5 times par silver, any other solve bronze. Try for fewer keeps your best length; the 14 pars total 1,354. Copy share link copies a link to a page with your cases solved and total characters. Progress is saved in your browser’s localStorage, so a reload resumes at your first unsolved case.

What you’ll learn

The 14 cases form one path, one SQL idea each, and every answer holds the clue for the next.

  • One table: The warden’s notebook (SELECT), The pond-room door (WHERE), Last ones in (ORDER BY and LIMIT), The crumbs (LIKE and ILIKE), Follow the cake (IN and BETWEEN), Lost and found (NULL and IS NOT NULL).
  • Grouping: Where B-17 goes (COUNT and GROUP BY), The workshop crowd (HAVING).
  • Joins: Names for badges (INNER JOIN), No alibi (LEFT JOIN to find missing rows).
  • Steps: After the splash (a subquery), Two suspects (WITH), The cellar door (LAG, a window function), The thief (LEAD inside WITH, then joins).

By the end you can answer a question about an unfamiliar schema in one query that filters, groups, joins, nests and compares neighboring rows.

Good to know

Queries run on real Postgres in your tab: the game loads PGlite 0.5.8, Postgres compiled to WebAssembly, and SELECT version() reports PostgreSQL 18.3. Each query runs read-only and is rolled back, and nothing goes to a server.

So you learn the Postgres dialect: ILIKE, :: casts, DISTINCT ON and window functions all work. Habits from elsewhere become real errors: MySQL’s LIMIT 2, 3 and SQL Server’s TOP 3 fail, and double quotes name a column, so ink = "red" gets column "red" does not exist. Text comparison with = or LIKE is case-sensitive, unlike MySQL’s default collations.

The game covers reading only: INSERT, UPDATE, DELETE, CREATE TABLE, transactions, indexes and EXPLAIN are refused, and CASE and UNION run but aren’t taught.

SQL Murder Mystery from Knight Lab is the classic: one open-ended case on a larger SQLite database, with a walkthrough for beginners, where you check your suspect by inserting their name into a solution table. SQLBolt is a series of short lessons that also covers writes and creating tables. SQL Detective is a guided story on Postgres, one idea per case, where any query that returns the right rows counts.

Questions

How long does it take to learn SQL?

Most people write useful SELECT queries within a few hours and get comfortable with joins, grouping and subqueries after a few weeks of regular practice. Window functions and query tuning take longer.

What’s the best way to practice SQL?

Write queries against a real database where you can check each answer, then load data you care about into a local Postgres or SQLite and ask it your own questions.

What’s the difference between WHERE and HAVING?

WHERE filters rows before grouping, and HAVING filters groups after it, so only HAVING can test a COUNT or SUM. This returns ana (100) and cy (120), and runs in any case’s query box:

WITH orders(customer, amount) AS (
  VALUES ('ana', 30), ('ana', 70), ('ben', 15), ('ben', 5), ('cy', 120)
)
SELECT customer, SUM(amount) AS total
FROM orders
WHERE amount >= 10
GROUP BY customer
HAVING SUM(amount) > 50;

What’s the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only rows with a match on both sides, and LEFT JOIN keeps every left-side row, with NULL where the right side has no match. Here LEFT JOIN returns Ben with a NULL item; plain JOIN drops him.

WITH people(id, name) AS (VALUES (1, 'Ana'), (2, 'Ben')),
     orders(person_id, item) AS (VALUES (1, 'tea'), (1, 'cake'))
SELECT p.name, o.item
FROM people p
LEFT JOIN orders o ON o.person_id = p.id;