#Exercises

The kind that comes from knowing the schema and not the data. The generated query joins orders to order_items, which is a completely reasonable thing to do with two tables in that relationship, and it inflates the total by 1.72x. Nothing in the DDL says how many line items a typical order has, or that summing a parent column across that join double counts.

The general form: a schema tells the model what exists. It doesn't say what's true. Cardinality, which statuses mean money, whether refunds are already netted out, which of two date columns the business uses. All absent, all decisive.

It adds the metric definitions: revenue is gross, booked on ordered_at, and net revenue subtracts refunds. Neither is a prompting technique. Both are decisions someone in finance made, transcribed into the prompt.

Worth sitting with, because the accuracy jump from 83% to 100% is entirely attributable to them. If you reported that as "prompt engineering improved accuracy by 17 points" you'd be describing the work incorrectly, and the next team to try it would go looking for the technique instead of the meeting.

The rankings are opposite. The broken one is a 67% text match and returns $1,629.98 too much. The correct one is a 43% match and returns the identical answer.

The consequence is stronger than "string matching is noisy". Because the wrong query is more similar than the right one, there's no threshold anywhere that accepts one and rejects the other. The metric can't be tuned into working. It's measuring the wrong property, and the only repair is to measure a different one.

One character will do it: change 'completed' to 'completed ' and the filter matches nothing. SUM over no rows returns NULL rather than 0, so the answer isn't merely wrong, it's a different type, and any code adding it to another figure will fail somewhere far away from the cause.

Comparisons like >= to > on a date boundary, or AND to OR in a two-clause WHERE, are similarly tiny and similarly total.

Not 11/7 = 1.57x. The actual figure is 1.72x.

Each order is over-counted in proportion to its own line count, so the result is an average weighted by order value. Here the weighting works against you: order 1002 has both the most line items and the largest total, pushing the error above the row ratio. Flip that (many small orders with many lines, one large order with one line) and the error lands below it.

Which is the real lesson. The size of this bug depends on which rows happen to have many children, so it moves month to month and there's no constant that corrects it.

Aggregate the child side to one row per parent first:

SELECT SUM(o.total_cents)
FROM orders o
JOIN (SELECT order_id, COUNT(*) AS lines FROM order_items GROUP BY order_id) i
  ON i.order_id = o.id
WHERE o.status = 'completed';

You wouldn't ship it because the join contributes nothing to the answer. orders already holds the total. The best fix for a fan-out bug is usually to delete the join, and a query carrying a join it doesn't need is a fan-out bug waiting for the next person to add a SUM.

No. It picks shipped_at and comes in $450 low, because one order was placed on March 31st and shipped April 1st.

None of the four warnings is a ruling on when revenue is booked, and no number of additional careful sentences would be, because the information isn't the kind that can be inferred. Someone has to decide. The defined level works for exactly one reason: somebody did, and wrote it down.

Would two analysts, given this question, have written the same query?

If the answer is no, the system is working correctly and the specification is missing. Chasing it as a model problem will consume a sprint and produce a prompt that encodes one analyst's preference without anyone agreeing to it.

Whether the system should have been allowed to do what it did. Correctness asks whether the answer was right. SELECT email FROM customers is a completely correct answer to "list every customer's email address", and it's the query you least want your system to run successfully.

Scored on the analyst connection, the same run reports 92% with one BLOCKED, and the missing 8% is the safety property becoming visible in the metric.

None. Including the last one, because SELECT * resolves to a column list that includes email, and including the fourth, which would have returned no rows anyway; the authorizer fires during statement preparation, before anything runs.

The reason all six fail is that nothing in askdb/permissions.py reads the SQL. SQLite calls the authorizer with the tables and columns the parsed statement resolved to, so aliases, subqueries, functions, case, and whitespace are all already gone by then.

Try writing a regex that catches all six and nothing else. The exercise is worth ten minutes precisely because of how it goes.

BLOCKED, and that's the right outcome rather than a gap. The question is answerable by the schema and not answerable by this role, which is a distinction worth having in the output. A system that reports it as an ERROR loses the information that the query was fine and the permissions stopped it, and that's exactly the signal you want to watch.

The third, then the second, then the first.

The first invents marketing_channels, which fails loudly. Nothing bad happens.

The second is worse because half of it is answerable: country is right there, so the query has a real GROUP BY and a shape that looks correct, and only the measure is fictional.

The third is the worst, because counting orders in March returns a number that is completely plausible, correctly computed, and about a different thing. Nothing in the output indicates that "tickets" and "orders" aren't the same concept. This is the near miss, and it's the reason abstention matters more than one more point of accuracy.

Telling it that refusal is an allowed output: If the schema cannot answer the question, output exactly: CANNOT ANSWER: <reason>.

A model asked only to write SQL will write SQL, because that's compliance. Giving it a named, licensed way to decline changes refusal from a failure to follow instructions into following them. The surrounding code then has to pass that through as an abstention rather than trying to parse it as SQL, which is the unglamorous half.

The owner connection reports 100%; the analyst connection reports 92%. The lower one is the number that describes the system you deploy, so it's the only one worth reporting.

The general rule is worth more than this example. An eval that runs with more privilege than production is measuring a system you don't ship, and it'll always report a better number than the truth, which is the direction that never prompts anyone to investigate.