A query that runs clean, looks right, and returns the wrong number
sql-05, from the sql suite
Run 1 failRun 2 fail
Asked as Mekong Noodle House
In September 2026, how much more did we keep per order in store than on a DoorDash delivery order?
What should happen
Answered, and the answer contains the figure from the query I wrote.
What the trace shows
A safe query that answers a slightly different question. The model's first query converted every timestamp to the restaurant's time zone and timed out on a per-order refund subquery. Its rewrite pushed the month boundary through AT TIME ZONE in the wrong direction, so the window slid five hours the wrong way: 2,643 in-store orders instead of 2,633, and $2.49 instead of $2.64. The guard passed it, as it should; nothing in the guard knows what September means.
What the grader found in run 2
- the answer's figures are $2.49, $30.06, $27.57; none is within 3% of $2.64
The query I wrote
The question read the way the owner means it. Run on the frozen database for the figure the grader expects.
WITH september AS ( SELECT o.channel, o.fulfillment_type, o.gross_sales_cents, o.gross_sales_cents - o.discount_cents AS after_discount, coalesce(p.processing_fee_cents, 0) AS fee FROM orders o LEFT JOIN payments p ON p.order_id = o.id AND p.status = 'completed' WHERE o.state = 'completed' AND date_trunc('month', o.created_at AT TIME ZONE 'America/Chicago') = '2026-09-01' ) SELECT round( avg(after_discount - fee) FILTER (WHERE channel = 'in_store') - avg(after_discount - gross_sales_cents * 0.25) FILTER (WHERE channel = 'doordash' AND fulfillment_type = 'delivery') ) FROM september
The query the model wrote
Its last query, laid out for reading. The trace has it as sent. The guard added the restaurant filter and the row limit before it ran.
WITH f AS ( SELECT order_id, sum(processing_fee_cents) fee FROM payments WHERE status = 'completed' GROUP BY order_id ) SELECT o.channel, count(*) n, avg( o.gross_sales_cents - o.discount_cents - CASE WHEN o.channel = 'in_store' THEN coalesce(f.fee, 0) ELSE 0.25 * o.gross_sales_cents END ) AS avg_net FROM orders o LEFT JOIN f ON f.order_id = o.id WHERE o.state = 'completed' AND ( o.channel = 'in_store' OR ( o.channel = 'doordash' AND o.fulfillment_type = 'delivery' ) ) AND o.created_at >= timestamptz '2026-09-01 00:00' at time zone( SELECT timezone FROM merchants ) AND o.created_at < timestamptz '2026-10-01 00:00' at time zone( SELECT timezone FROM merchants ) GROUP BY o.channel
What it answered
In September 2026, we kept about $2.49 more per order in store than on a DoorDash delivery order. That is about $30.06 per in-store order against about $27.57 per DoorDash delivery order.
- Scope: The figures cover completed orders only. That is 2,643 in-store orders and 1,271 DoorDash delivery orders. DoorDash pickup orders are left out.
- In-store payout: Gross sales minus discounts minus the card processing fee.
- DoorDash payout: Gross sales minus discounts minus the 25% delivery commission . Marketplace unit prices already include the markup over in-store prices .
- Caveat: The September date range may be off by a few hours at the month boundaries, because the time zone conversion may not match the restaurant's local time exactly. I don't expect that to change the per-order averages much.
answered
- $0.0233to answer, $0.0458 without the prompt cache
- 15.9 s9.9 s in the model
- 10steps, 16,514 tokens in, 1,279 out
- 1retry