AI-agent skill · Claude Code · Codex · Cursor · Gemini CLI
Trusted SQL Analyst turns plain-English questions into SQL, then double-checks the answer like a careful senior analyst. It maps the database first, keeps a glossary of what columns and business terms mean, runs 25 automatic checks for silent mistakes, proves the number with a second query written a different way, and explains the result in plain English with its assumptions. Read-only and local: SQLite, DuckDB, CSV/Parquet/Excel folders, Postgres and MySQL.
"Revenue by genre" on the Chinook music-store database. Summing the invoice total after joining to invoice lines repeats every invoice once per line - a classic AI-written SQL mistake that returns a confident number.
The check: FAIL - "each Invoice row appears 5.44 times on average after the joins (2,240 joined rows for 412 invoices)".
Summed from invoice lines at their own grain. Check: PASS; the 24 genres add up to the true total 2,328.60; a second query by another route agrees.
The raw check output for the wrong query:
## SQL check: FAIL ❌
Rows returned: **24** · dialect sqlite · sqlanalyst 1.1.0
| # | severity | rule | problem |
|---|---|---|---|
| 1 | high | TSQ105 Join multiplies rows before SUM/AVG/COUNT (fan-out) | After the joins, each `Invoice` row appears 5.44 times on average (2,240 joined rows for 412 Invoice rows). SUM(i.total) therefore adds up the same `Invoice` rows several times. |
**Fixes**
1. Aggregate `Invoice` at its own grain before joining (subquery/CTE), or use COUNT(DISTINCT key); never SUM/AVG a parent-table column after joining to its child rows.
**Verified**
- Reconciled (internal consistency): the 24 group totals of `revenue` add up to the ungrouped total of the same rows, 20,848.62. This does not prove the rows themselves are right.
**Result preview**
| Name | revenue |
|---|---|
| Rock | 7,720.02 |
| Latin | 3,472.55 |
| Metal | 2,093.13 |
| Alternative & Punk | 1,961.66 |
| TV Shows | 817.71 |
| Jazz | 746.46 |
| Drama | 544.61 |
| Blues | 429.66 |
| R&B/Soul | 338.62 |
| Reggae | 332.64 |
| Classical | 317.04 |
| Soundtrack | 242.55 |
| Pop | 239.75 |
| Alternative | 211.17 |
| Sci Fi & Fantasy | 198.87 |
| World | 182.18 |
| Hip Hop/Rap | 166.41 |
| Heavy Metal | 161.37 |
| Electronica/Dance | 149.62 |
| Easy Listening | 138.6 |
A separate AI agent that had never seen the tool wrote 24 new realistic wrong queries (with the correct query for each) on three real databases. The checker was frozen and run once.
| Case | Type | Mistake (written by the test agent) | Result |
|---|---|---|---|
| B01 | sql-mechanics | Sums the invoice-level Total after joining to invoice lines, so each invoice total is counted once per line (join fan-out) and attributed to every genre on the invoice. | ✅ caught |
| B02 | sql-mechanics | NOT IN against a subquery that contains a NULL (the General Manager's ReportsTo) is never true, so the count is 0. | ✅ caught |
| B03 | sql-mechanics | All operands are integers, so the division truncates and the percentage loses its decimals (6.0 instead of 6.11). | ✅ caught |
| B04 | sql-mechanics | COUNT(*) after a LEFT JOIN counts the NULL-padded row, so artists with no albums are counted as having 1 album. | ✅ caught |
| B05 | business-meaning | Sums catalog list prices of all Jazz tracks (the value of the catalog) instead of what was actually sold on invoice lines. | — missed |
| B06 | sql-mechanics | State <> 'CA' evaluates to NULL for customers with no state, silently dropping 29 customers. | ✅ caught |
| B07 | business-meaning | Averages individual invoice totals (average order value) rather than each customer's summed lifetime spend. | — missed |
| B08 | sql-mechanics | Joins Customer to Employee on CustomerId = EmployeeId instead of SupportRepId, pairing each employee with an unrelated customer. | — missed |
| B09 | sql-mechanics | OrderDate includes a time of day, so BETWEEN ... '2021-12-31' excludes orders placed later on December 31. | ✅ caught |
| B10 | business-meaning | Uses the product's current list price (Products.UnitPrice) instead of the price actually charged on each order line. | — missed |
| B11 | sql-mechanics | AND binds tighter than OR, so the freight filter only applies to United Package and every Speedy Express order is counted. | ✅ caught |
| B12 | sql-mechanics | Exact string match misses a supplier whose Country is stored as 'Sweden ' with a trailing space. | ✅ caught |
| B13 | business-meaning | Groups by the shipping destination country on the order rather than the customer's own country, which differ for most orders. | — missed |
| B14 | sql-mechanics | The self-join roles are swapped: it groups by the subordinate (m) instead of the manager (e), so every employee shows 1 report. | — missed |
| B15 | sql-mechanics | Order-level Freight is summed once per matching beverage line, so orders with several beverage products have their freight counted multiple times. | ✅ caught |
| B16 | business-meaning | Compares only units in stock to the reorder level and ignores units already on order, so products that are being replenished are flagged. | — missed |
| B17 | sql-mechanics | Groups by actor name instead of actor_id, merging the two different actors named SUSAN DAVIS into one person with 54 films. | ✅ caught |
| B18 | sql-mechanics | Filtering the right-hand table in WHERE turns the LEFT JOIN into an inner join, so customers with zero rentals are dropped from the average. | ✅ caught |
| B19 | sql-mechanics | Counts rental rows instead of distinct films (missing DISTINCT), so a film rented many times is counted many times. | ✅ caught |
| B20 | sql-mechanics | COUNT(*) on the LEFT JOIN counts the NULL row, so the 42 films with no inventory are counted as having one copy. | ✅ caught |
| B21 | sql-mechanics | payment_date is a timestamp, so BETWEEN ... '2005-07-31' drops every payment made during July 31. | ✅ caught |
| B22 | business-meaning | Attributes payments to the store of the staff member who took the payment, not the store that owns the rented inventory. | — missed |
| B23 | business-meaning | Counts the films in each category (catalog size) instead of the number of rentals. | — missed |
| B24 | business-meaning | Ranks customers by number of payments instead of the sum of amounts paid. | — missed |
With and without the skill: two fresh agents answered the same 12 business questions; both got 12 / 12. A strong model working carefully does not need the skill to be accurate on questions like these - the measured value is catching mistakes before you trust a number, and answers that state their assumptions and checks. Databases: Chinook, Northwind (609,283 order lines) and Sakila.
Fan-out double counting (SUM/AVG/COUNT after a one-to-many join, with the measured factor) · rows dropped by INNER JOIN · join without a condition · LEFT JOIN undone by WHERE · COUNT(*) after LEFT JOIN
NOT IN with a NULL in the subquery · = NULL · negative filters (<>, NOT LIKE) that drop NULL rows · AVG skipping NULLs
'usa' vs 'USA' vs 'USA ' · values that do not exist (closest matches shown) · LIKE that also matches other categories · list columns ('a,b,c') matched with =
BETWEEN / <= that drops the last day when values have a time · = on a timestamp · dates stored as non-ISO text
Columns neither grouped nor aggregated · grouping by a name that merges different people · integer division · AND/OR without brackets · LIMIT without ORDER BY
Empty result: which filter emptied it · duplicate groups · groups that do not add up · NULL groups · impossible percentages
Refuses INSERT/UPDATE/DELETE/DDL, file-reading functions and multiple statements; every query has a timeout.
Paste a query from a colleague or another AI: you get the problems, the measured impact and a fixed query.
pip install sqlglot (+ duckdb for files, SQLAlchemy for servers). Tested on SQLite and DuckDB.
It cannot know your business meaning; a PASS is never proof the question was understood. That is why the glossary and the second query exist.
Made by Muhammad Shahmeer · Data Science MSc, University of Naples Federico II · © 2026, all rights reserved.