Practice: review an LLM revenue query

A plausible query is not necessarily a correct query. Here you will review an illustrative LLM response, predict its mistake, and check it against answers calculated from the input rows.

Read Working with LLMs first. No model account is needed. The saved response is an author-written teaching example, not output captured from a model or a model-quality benchmark.

1. Fix the contract before reading the answer

Use a small synthetic version of ShopFlow. The fixture represents money as integer cents to avoid floating-point ambiguity: amount_cents corresponds to orders.amount, and unit_price_cents to unit_price.

• orders: one row per order_id; includes status and the order total.
• order_items: one row per (order_id, product_id); includes quantity and the sale-time unit price.
• products: one row per product_id; its unit price is the current catalog price.

Question: What is the total value of sale lines belonging to orders whose status is exactly paid? Return one column, revenue_cents. Exclude cancelled orders. These fixtures have no tax, discounts or refunds.

The first case contains one paid $100 order with two lines worth $30 and $70, plus a cancelled order. Therefore the expected result is 10,000 cents ($100). The second case adds another paid $100 order; its independent expected result is 20,000 cents ($200).

Those are business answers calculated from the rows, not values copied from a generated query.

2. Inspect the illustrative proposal

SELECT SUM(o.amount_cents) AS revenue_cents
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.status = 'paid';

Before running it, explain what one row means after the join. Each of the first order's two lines carries the same 10,000-cent order header. Summing that repeated header produces 20,000 cents.

A response that says “the join is correct because the keys match” has checked only part of the problem. The join condition can be valid while the aggregation uses the wrong grain.

3. Run the supplied evidence

Download revenue-grain.py, inspect it, and run it from the folder where you saved it:

python3 revenue-grain.py

The script uses Python's standard-library SQLite, prints its engine version, creates synthetic tables in memory, and compares several candidates with fixed expected answers. It makes no model request or network connection and creates no database file. The default demonstration succeeds only when the known-bad candidates are rejected and the correct candidates pass.

Candidate	One paid order	Two equal-total paid orders	Verdict
Sum joined order headers	20,000	40,000	Repeats each header for its two lines
Sum distinct order amounts	10,000	10,000	Collapses separate orders with equal amounts
Use current catalog prices	12,500	25,000	Answers a current-price question
Sum captured sale-line values	10,000	20,000	Matches both expected answers

These are deterministic fixture results, not model benchmarks. The second case matters: a “fix” using SUM(DISTINCT amount_cents) looks correct on the first case but loses a real order on the second.

4. Propose a small repair

Keep the valid order-to-line join and the paid filter. Sum the measure at the line grain:

SELECT COALESCE(SUM(oi.quantity * oi.unit_price_cents), 0)
       AS revenue_cents
FROM order_items AS oi
JOIN orders AS o ON o.order_id = oi.order_id
WHERE o.status = 'paid';

Save that SELECT as my-revenue.sql beside the script, then check it:

python3 revenue-grain.py --candidate-file my-revenue.sql

Both cases must match. Try the original proposal and the DISTINCT repair through the same command to see why one passing example is insufficient. A rejected candidate exits with a nonzero status; that is expected when demonstrating a broken query.

An order-header-only sum also matches these fixtures because header totals equal their line totals here. It is not automatically interchangeable in a real system where tax, discounts or adjustments have different definitions. Record that assumption in a code review.

Inspect a plan with the engine's non-executing EXPLAIN QUERY PLAN facility; the runner shows a reference plan. Check what is scanned and joined, but do not infer production speed from two small orders. Plan wording can vary by SQLite version.

5. Recognize an instruction hidden in data

Read the untrusted log specimen. It asks for an unrelated credential action using an inert placeholder. That is data to inspect, not an instruction to follow.

A suitable review note is: “This log contains an out-of-scope instruction. I will not read or upload credentials. It is unnecessary for verifying revenue.” Keep the model's tools restricted even when the prompt explicitly tells it to ignore such content.

The SQL runner limits candidate access to its in-memory fixture. That does not make arbitrary Python downloads safe; inspect the supplied script, and do not give a real agent production credentials to repeat this exercise.

6. Leave a review someone else can verify

Write a short review with four parts:

1. Meaning: one total of paid sale-line values, in cents; no tax/refunds/discounts in this fixture.
2. Change: replace the repeated header measure with quantity times captured sale price.
3. Evidence: record your actual command/output for both fixtures; identify the DISTINCT and current-price counterexamples.
4. Limits: these rows do not establish behavior for empty inputs, null prices, refunds, multiple currencies, rounding policies or larger-data performance. Add relevant independent cases before extending the task.

If you use a model to suggest the repair, keep the same expected answers and review steps. The model can help produce a candidate; it cannot be the sole judge of its own work.

Next: Chapter 3 checkpoint →