For teachers
A detective game solved by writing real SQL against a real database, not by picking multiple-choice answers.
Every case in Caso Abierto is a database. The student writes real SQL, using DuckDB, which follows the SQL standard for the basics.
To close a case they submit the query that proves their answer, and the game rejects it if it only states the answer instead of proving it with data.
Four seasons. The table comes from the same catalog the game uses. The "trap" column links to the page that explains, with real SQL, the mistake that case is built to catch.
| No. | Case | Difficulty | Techniques | Trap |
|---|---|---|---|---|
| 0 | The First Query | ◆◇◇◇◇ | SELECT, WHERE, a time condition | |
| 1 | The File Nobody Asked For | ◆◇◇◇◇ | SELECT, WHERE, date ranges, ORDER BY | Why does BETWEEN eat the last day of the range? |
| 2 | The Witness's Description | ◆◇◇◇◇ | compound WHERE, LIKE, IN, BOOLEAN, NULL | |
| 3 | The 23:40 Call | ◆◆◇◇◇ | JOIN, table aliases, chaining two joins | |
| 4 | The Regular | ◆◆◇◇◇ | GROUP BY, COUNT, HAVING, filtering before and after grouping | Why doesn't WHERE find the customer who 'always' pays cash? |
| 5 | Eleven Alibis and One That Isn't | ◆◆◆◇◇ | LEFT JOIN, NOT EXISTS, anti-join, interval overlap | When do two time intervals actually overlap? |
| 6 | Private Room 3 | ◆◆◆◇◇ | self-join, WITH (CTE), interval overlap, excluding yourself | Why does a self-join always bring back one extra row? |
| 7 | The Card That Was in Two Places | ◆◆◆◆◇ | window functions, LAG, PARTITION BY, QUALIFY, date_diff | Why does the running balance repeat the exact same number on two different rows? · Why can't WHERE filter on a window function's result? |
| 8 | What the Anacleta Brought | ◆◆◆◆◆ | chained CTEs, multiple JOINs, overlap, date arithmetic, reasoning in steps |
| No. | Case | Difficulty | Techniques | Trap |
|---|---|---|---|---|
| 1 | The Account That's Bleeding | ◆◆◇◇◇ | SUM, GROUP BY, cost arithmetic, ORDER BY | |
| 2 | Trading Against the Mirror | ◆◆◆◇◇ | joining one table by two keys, comparing both sides of the same row, GROUP BY | |
| 3 | The Entry Nobody Wrote | ◆◆◆◇◇ | anti-join, NOT EXISTS, LEFT JOIN ... IS NULL, the NOT IN with NULL trap | Why does NOT IN return zero rows? |
| 4 | The Collector That Got Stuck | ◆◆◆◇◇ | LAG, gaps and islands, row_number, streak detection, data quality | |
| 5 | The One Who Knew | ◆◆◆◆◇ | interval join, ASOF JOIN, hit rate, sample size | A rate needs a denominator |
| 6 | The Decoy | ◆◆◆◆◇ | FILTER, conditional ratios, percentiles, combining several conditions | |
| 7 | The Toll | ◆◆◆◆◆ | date_part, hourly cohorts, theoretical vs. actual, decomposing a result, basis points | |
| 8 | Follow the Money | ◆◆◆◆◆ | WITH RECURSIVE, graphs in SQL, cycle control, terminal account |
| No. | Case | Difficulty | Techniques | Trap |
|---|---|---|---|---|
| 1 | The Report That Multiplied Itself | ◆◆◆◇◇ | cardinality, fan-out, aggregate before joining, COUNT DISTINCT | The join that multiplies rows |
| 2 | The Double | ◆◆◆◇◇ | deduplication, row_number, QUALIFY, business key vs. row | |
| 3 | Yesterday's Rate | ◆◆◆◆◇ | tables with history, temporal join, valid_from / valid_to, recompute and compare | |
| 4 | The Mute Unit | ◆◆◆◆◇ | normalizing units, currency conversion, reading the data dictionary, CASE | |
| 5 | The Twenty-Five-Hour Day | ◆◆◆◆◇ | time zones, AT TIME ZONE, date_trunc, defining the day before grouping | Time zones: store UTC, show local |
| 6 | The Ones Who Aren't There Anymore | ◆◆◆◆◆ | survivorship bias, historical universe, join with validity, average of averages | Survivorship bias lives in the JOIN |
| No. | Case | Difficulty | Techniques | Trap |
|---|---|---|---|---|
| 1 | The Night in March | ◆◆◆◆◇ | time windows, JOIN between tables without a key, subquery as a reference, strftime | |
| 2 | The Cash | ◆◆◆◆◆ | JOIN by amount and date window, INTERVAL, GROUP BY with HAVING, chain: till → carrier → account | |
| 4 | The Weight of the Anacleta | ◆◆◆◆◆ | JOIN by date with tolerance, aggregation by port call, HAVING against a subquery, several chained CTEs | |
| 5 | The Cards | ◆◆◆◆◆ | LAG window function, temporal self-join, date_diff, JOIN by date with tolerance | |
| 8 | File 88-C | ◆◆◆◆◆ | several CTEs in one query, conditional aggregates, HAVING against a subquery, one row with the whole truth |
The teacher asks for a group code and gets a link. That link opens the full game in each student's browser: no accounts, nothing to install, works on the school lab computers.
Each student's progress is saved only in that browser. If a student switches computers, they can copy their progress as text from Settings → Progress ("Copy" button) and paste it on the other computer ("Paste…" button); the game merges it with whatever was already there, keeping whichever case is further along.
The classroom version, the game that opens from the group code link, has no accounts, asks the student for no data, has no ads and carries no analytics.
This public page you're reading is different: it counts visits with Cloudflare Web Analytics, with no cookies.
We don't measure how long a student takes or how much they learn, not yet. Cases 0 through 4 are short. Difficulty 4 and 5 cases can take a full class session. Not measured yet.
Write from your institutional email with the course, the institution, the approximate number of students and the semester dates.
Write to christianvadillo@hotmail.com. Free for the classroom.
A complete lesson plan, with three levels and discussion questions. Printable. 50-minute class guide →
The mistakes hidden in the cases, each on its own page: what breaks, a minimal SQL example that reproduces it, the corrected version and why.
The first case runs free in your browser, no signup.
Play the first case free →