Agent-ready data marts
Documentation raised correct SQL answers from 27 to 33 of 40. Listing the allowed values alone reached 31.
One model answered 40 questions about a synthetic banking ledger, with the same tables and SQL interface in every arm. Full documentation gave 6 more correct answers than the bare schema. Giving the bare schema only the values each category column holds, such as direction being 'credit' or 'debit', closed 4 of those 6. No arm handled the 4 ambiguous questions.
| Bare schema27/4067.5% correct | Schema plus allowed values31/4077.5% correct | Full documentation33/4082.5% correct | Documentation gain+15.0 ppfull documentation minus bare; interval 0.0 to +30.0 pp |
Main limitation One model run on 40 related, purposively written questions over synthetic data, so the intervals describe this question set and support no general claim about documentation. | |||
Where the gain came from
Paired differences in percentage points (pp) on 40 questions, each with the question-level bootstrap interval and a twin-clustered interval that resamples the CAD and USD versions of a question together.
Show the data
| Comparison | Correct, first arm | Correct, second arm | Difference | Interval, lower | Interval, upper | Twin-clustered, lower | Twin-clustered, upper | Fixed | Broken |
|---|---|---|---|---|---|---|---|---|---|
| Full documentation minus bare schema | 33 | 27 | 0.1499999999999999 | 0.0 | 0.3 | 0.0 | 0.3142857142857143 | 8 | 2 |
| Schema plus allowed values minus bare schema | 31 | 27 | 0.09999999999999998 | 0.0 | 0.225 | −0.02564102564102564 | 0.24324324324324326 | 5 | 1 |
| Full documentation minus schema plus allowed values | 33 | 31 | 0.04999999999999993 | −0.075 | 0.175 | −0.075 | 0.175 | 4 | 2 |
Download results-three-arm.json · Download summary-three-arm.csv
Source: results-three-arm.json, comparisons; 10,000 bootstrap resamples, seed 7.
- Against the bare schema, allowed values fixed 5 answers and broke 1; full documentation fixed 8 and broke 2.
- Eight questions come as CAD and USD twins that tend to succeed or fail together. Resampling each pair as one unit gives the dashed intervals. This check was added after the first results were seen.
Why answers were wrong
The bare schema guessed category values that do not exist in 6 of its 9 wrong analytical answers (direction = 'inbound', direction = 'outbound', event_kind = 'external', status = 'completed'). With the value lists, no answer guessed a value. Using occurred_at instead of the posting date remained in 3 answers with allowed values and in none with full documentation, which defines the posting rule. Each arm made one grain error.
| Cause | Bare schema | Schema plus allowed values | Full documentation |
|---|---|---|---|
| Guessed a category value that does not exist | 6 | 0 | 0 |
| Used occurred_at for a posted-period question | 7 | 3 | 0 |
| Wrong grain (duplicated rows or a stock summed across dates) | 1 | 1 | 1 |
| Other definition error | 1 | 2 | 0 |
| SQL failed to run | 0 | 1 | 2 |
| Incorrect analytical answers | 9 | 5 | 3 |
Every incorrect analytical answer and its cause
| Question | Arm | Causes | What went wrong |
|---|---|---|---|
| B06 | Bare | used occurred_at for a posted-period question | Counted purchase events by the month of occurred_at instead of the posted spend date. |
| B19 | Bare | used occurred_at for a posted-period question, other definition error | Treated purchases as credits and refunds as debits, so the join matched nothing, and filtered refunds by occurred_at. |
| B20 | Bare | guessed a category value that does not exist, used occurred_at for a posted-period question | Filtered direction = 'inbound', a value that does not exist, and used occurred_at for the period. |
| B21 | Bare | guessed a category value that does not exist, used occurred_at for a posted-period question | Filtered direction = 'inbound', a value that does not exist, and used occurred_at for the period. |
| B22 | Bare | guessed a category value that does not exist, used occurred_at for a posted-period question | Filtered direction = 'outbound', a value that does not exist, and used occurred_at for the period. |
| B23 | Bare | guessed a category value that does not exist, used occurred_at for a posted-period question | Used direction 'inbound' and event_kind 'external', neither of which exists, and used occurred_at for the period. |
| B23 | Documentation | sql failed to run | Joined accounts with USING, then filtered an ambiguous currency column; the query failed. |
| B24 | Bare | guessed a category value that does not exist, used occurred_at for a posted-period question | Used direction 'inbound' and event_kind 'external', neither of which exists, and used occurred_at for the period. |
| B25 | Documentation | sql failed to run | Referenced a column through an alias that was not defined in that scope; the query failed. |
| B26 | Bare | guessed a category value that does not exist | Filtered status = 'completed'; the posted value is 'posted', so no rows matched. |
| B35 | Documentation | wrong grain (duplicated rows or a stock summed across dates) | Joined cards before card spend, so an account with two cards and no July purchases was listed twice. |
| B36 | Bare | wrong grain (duplicated rows or a stock summed across dates) | Summed opening and closing balances over all 31 days of July instead of taking July 1 and July 31. |
| B06 | Allowed values | used occurred_at for a posted-period question | Counted purchase events by the month of occurred_at instead of the posted spend date. |
| B17 | Allowed values | sql failed to run | Used UNION ALL, which the prompt does not allow; the validator denied the query. |
| B23 | Allowed values | used occurred_at for a posted-period question, other definition error | Counted deposits, withdrawals and transfers as external cash flow, left out purchases and refunds, and used occurred_at for the period. |
| B24 | Allowed values | used occurred_at for a posted-period question, other definition error | Counted only deposits and withdrawals as external cash flow, left out purchases and refunds, and used occurred_at for the period. |
| B36 | Allowed values | wrong grain (duplicated rows or a stock summed across dates) | Took the minimum opening and maximum closing daily balance across accounts instead of summing July 1 opening and July 31 closing. |
By question type
| Question type | Questions | Bare schema | Schema plus allowed values | Full documentation |
|---|---|---|---|---|
| All questions | 40 | 27 | 31 | 33 |
| Analytical SQL | 36 | 27 | 31 | 33 |
| Clarification | 4 | 0 | 0 | 0 |
No arm passed a clarification question. Three were answered with SQL instead of a question. On the fourth, about high-risk accounts, the bare and allowed-values arms asked for the wrong definitions and the documented arm used a label the answer format does not allow. One allowed-values request failed at the provider (HTTP 503) and counts as incorrect; it was a clarification question the other arms also missed.
The original scoring, before the correction below, was 26/40 for the bare schema and 33/40 with documentation (+17.5 pp, interval +5.0 to +32.5). It stays in results.json.
A scoring correction
The SQL validator treated EXISTS as a function call and denied it, although the prompt permits ordinary subqueries. The validator was corrected for EXISTS only and all 80 saved run 1 answers were rescored, with no new model calls.
One answer changed: B35 (bare schema), "Which CAD accounts had no purchases contributing to July 2026 card spend? Return account_id, including accounts with other ledger activity." It used NOT EXISTS with a subquery, which the prompt allows, but the original validator denied it as a forbidden function. Run in the same sandbox, it returns the expected accounts, so it is now scored correct.
The bare schema moved from 26/40 to 27/40, and the documentation gain from +17.5 to +15.0 points (interval +5.0 to +32.5 became 0.0 to +30.0). The original scores and every original answer are kept.
What each arm received
- Bare schema: the answer rules, the allowed SQL functions, and table and column names with their types.
- Schema plus allowed values: the same, plus one list of the distinct values of every text column that is not an identifier, read from the database: currencies, account and card statuses, category names and groups, event kinds, posting statuses and directions. Nothing else. The exact list.
- Full documentation: the bare schema plus a bundle that names the allowed values of the status, event kind, direction and currency columns and defines metrics, table grain, currency, posting time, refunds, transfers and safe joins. The exact bundle.
Three examples from the bundle, chosen before any answers were seen
- Posting time: financial periods follow posted_at, even when the initiating event happened in another month.
- Balance grain: a daily account balance must not repeat when an account has several cards.
- Refunds and transfers: refunds reduce spend when they post; internal transfers move money between accounts and are not spend.
These examples illustrate the bundle. Apart from the declared allowed-values arm, no ablation measures what any single part of it contributes.
Every question and answer
Open a question to compare the three original model responses, the rows each query returned and the expected answer. Responses are shown as text; this page runs no SQL and calls no model.
B01What was gross card spend in CAD during July 2026? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(purchased_minor) FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1342646]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(purchased_minor) FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1342646]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) AS gross_card_spend_cad_july_2026 FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1342646]]
Expected result
[[1342646]]
Gold SQL
SELECT COALESCE(SUM(purchased_minor),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='CAD'
B02What was gross card spend in USD during July 2026? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(purchased_minor) FROM card_spend_daily WHERE currency = 'USD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[580559]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(purchased_minor) FROM card_spend_daily WHERE currency = 'USD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[580559]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) AS gross_card_spend_usd_july_2026\nFROM card_spend_daily\nJOIN cards ON card_spend_daily.card_id = cards.card_id\nJOIN accounts ON cards.account_id = accounts.account_id\nWHERE card_spend_daily.spend_date >= DATE '2026-07-01'\n AND card_spend_daily.spend_date < DATE '2026-08-01'\n AND accounts.currency = 'USD'"}Executed rows
[[580559]]
Expected result
[[580559]]
Gold SQL
SELECT COALESCE(SUM(purchased_minor),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='USD'
B03What was the refunded card amount in CAD during July 2026? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(refunded_minor), 0) FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[64654]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(refunded_minor), 0) AS refunded_amount_minor FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[64654]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(refunded_minor), 0) AS refunded_amount_cad FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[64654]]
Expected result
[[64654]]
Gold SQL
SELECT COALESCE(SUM(refunded_minor),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='CAD'
B04What was net card spend in CAD during July 2026? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_cad_july_2026 FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1277992]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_cad_july_2026 FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1277992]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_cad FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[1277992]]
Expected result
[[1277992]]
Gold SQL
SELECT COALESCE(SUM(net_spend_minor),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='CAD'
B05What was net card spend in USD during July 2026? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_usd_july_2026 FROM card_spend_daily WHERE currency = 'USD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[554332]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_usd_july_2026 FROM card_spend_daily WHERE currency = 'USD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
[[554332]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(net_spend_minor), 0) AS net_card_spend_usd_july_2026\nFROM card_spend_daily\nWHERE currency = 'USD'\n AND spend_date >= DATE '2026-07-01'\n AND spend_date < DATE '2026-08-01'"}Executed rows
[[554332]]
Expected result
[[554332]]
Gold SQL
SELECT COALESCE(SUM(net_spend_minor),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='USD'
B06How many CAD purchase events contributed to July 2026 card spend? Return one count.Bare: wrong answer · Allowed values: wrong answer · Documentation: correct
Rubric: analytical SQL. Expected columns: purchase_count. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7"}Executed rows
[[342]]
Schema plus allowed values
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) FROM transactions WHERE event_kind = 'purchase' AND currency = 'CAD' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7"}Executed rows
[[342]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) FROM transactions WHERE event_kind = 'purchase' AND status = 'posted' AND currency = 'CAD' AND posted_at >= '2026-07-01' AND posted_at < '2026-08-01' AND card_id IS NOT NULL"}Executed rows
[[286]]
Expected result
[[286]]
Gold SQL
SELECT COALESCE(SUM(purchase_count),0) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='CAD'
B07Break down July 2026 net card spend in CAD by category label, including the uncategorized bucket. Return category label and net minor units for categories with activity.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: category, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units FROM card_spend_daily cs LEFT JOIN categories c ON cs.category_id = c.category_id WHERE cs.currency = 'CAD' AND cs.spend_date >= DATE '2026-07-01' AND cs.spend_date < DATE '2026-08-01' GROUP BY category_label HAVING SUM(cs.net_spend_minor) <> 0"}Executed rows
[["Software", 224575], ["Travel", 249615], ["Supplies", 219068], ["Meals", 293557], ["Uncategorized", 291177]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'CAD' AND EXTRACT(YEAR FROM cs.spend_date) = 2026 AND EXTRACT(MONTH FROM cs.spend_date) = 7\nGROUP BY category_label\nHAVING SUM(cs.net_spend_minor) != 0"}Executed rows
[["Software", 224575], ["Travel", 249615], ["Supplies", 219068], ["Meals", 293557], ["Uncategorized", 291177]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'CAD' AND cs.spend_date >= DATE '2026-07-01' AND cs.spend_date < DATE '2026-08-01'\nGROUP BY category_label\nHAVING SUM(cs.net_spend_minor) != 0"}Executed rows
[["Software", 224575], ["Travel", 249615], ["Supplies", 219068], ["Meals", 293557], ["Uncategorized", 291177]]
Expected result
[["Software", 224575], ["Travel", 249615], ["Supplies", 219068], ["Meals", 293557], ["Uncategorized", 291177]]
Gold SQL
SELECT COALESCE(c.category_name,'Uncategorized'), SUM(s.net_spend_minor) FROM card_spend_daily s LEFT JOIN categories c ON s.category_id=c.category_id WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND s.currency='CAD' GROUP BY COALESCE(c.category_name,'Uncategorized')
B08Break down July 2026 net card spend in USD by category label, including the uncategorized bucket. Return category label and net minor units for categories with activity.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: category, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'USD' AND cs.spend_date >= DATE '2026-07-01' AND cs.spend_date < DATE '2026-08-01'\nGROUP BY category_label\nHAVING SUM(cs.net_spend_minor) <> 0"}Executed rows
[["Meals", 111588], ["Travel", 117967], ["Software", 101312], ["Supplies", 94490], ["Uncategorized", 128975]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'USD' AND EXTRACT(YEAR FROM cs.spend_date) = 2026 AND EXTRACT(MONTH FROM cs.spend_date) = 7\nGROUP BY category_label\nHAVING SUM(cs.net_spend_minor) != 0"}Executed rows
[["Meals", 111588], ["Travel", 117967], ["Software", 101312], ["Supplies", 94490], ["Uncategorized", 128975]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nJOIN cards cd ON cs.card_id = cd.card_id\nJOIN accounts a ON cd.account_id = a.account_id\nWHERE cs.spend_date >= DATE '2026-07-01' AND cs.spend_date < DATE '2026-08-01' AND a.currency = 'USD'\nGROUP BY category_label\nHAVING SUM(cs.net_spend_minor) != 0"}Executed rows
[["Meals", 111588], ["Travel", 117967], ["Software", 101312], ["Supplies", 94490], ["Uncategorized", 128975]]
Expected result
[["Meals", 111588], ["Travel", 117967], ["Software", 101312], ["Supplies", 94490], ["Uncategorized", 128975]]
Gold SQL
SELECT COALESCE(c.category_name,'Uncategorized'), SUM(s.net_spend_minor) FROM card_spend_daily s LEFT JOIN categories c ON s.category_id=c.category_id WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND s.currency='USD' GROUP BY COALESCE(c.category_name,'Uncategorized')
B09Break down July 2026 net card spend in CAD by organization. Return organization_id and net minor units for organizations with card activity.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: organization_id, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN accounts a ON csd.account_id = a.account_id\nWHERE csd.currency = 'CAD'\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\nGROUP BY a.organization_id\nHAVING SUM(csd.net_spend_minor) != 0"}Executed rows
[["org_01", 230735], ["org_02", 203006], ["org_03", 253869], ["org_04", 250691], ["org_05", 243071], ["org_06", 96620]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN accounts a ON csd.account_id = a.account_id\nWHERE csd.currency = 'CAD'\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\nGROUP BY a.organization_id\nHAVING SUM(csd.net_spend_minor) != 0"}Executed rows
[["org_01", 230735], ["org_02", 203006], ["org_03", 253869], ["org_04", 250691], ["org_05", 243071], ["org_06", 96620]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN cards c ON csd.card_id = c.card_id\nJOIN accounts a ON c.account_id = a.account_id\nWHERE csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01'\n AND csd.currency = 'CAD'\nGROUP BY a.organization_id\nHAVING SUM(csd.purchase_count) > 0"}Executed rows
[["org_01", 230735], ["org_02", 203006], ["org_03", 253869], ["org_04", 250691], ["org_05", 243071], ["org_06", 96620]]
Expected result
[["org_01", 230735], ["org_02", 203006], ["org_03", 253869], ["org_04", 250691], ["org_05", 243071], ["org_06", 96620]]
Gold SQL
SELECT a.organization_id,SUM(s.net_spend_minor) FROM card_spend_daily s JOIN accounts a ON s.account_id=a.account_id WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND s.currency='CAD' GROUP BY a.organization_id
B10Break down July 2026 net card spend in USD by organization. Return organization_id and net minor units for organizations with card activity.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: organization_id, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN accounts a ON csd.account_id = a.account_id\nWHERE csd.currency = 'USD'\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\nGROUP BY a.organization_id\nHAVING SUM(csd.net_spend_minor) != 0"}Executed rows
[["org_01", 106600], ["org_02", 127490], ["org_03", 88313], ["org_05", 127957], ["org_04", 103972]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN accounts a ON csd.account_id = a.account_id\nWHERE csd.currency = 'USD'\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\nGROUP BY a.organization_id\nHAVING SUM(csd.net_spend_minor) != 0"}Executed rows
[["org_01", 106600], ["org_02", 127490], ["org_03", 88313], ["org_05", 127957], ["org_04", 103972]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.organization_id, SUM(csd.net_spend_minor) AS net_spend_minor\nFROM card_spend_daily csd\nJOIN cards c ON csd.card_id = c.card_id\nJOIN accounts a ON c.account_id = a.account_id\nWHERE csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01'\n AND csd.currency = 'USD'\nGROUP BY a.organization_id\nHAVING SUM(csd.purchase_count) > 0"}Executed rows
[["org_01", 106600], ["org_02", 127490], ["org_03", 88313], ["org_05", 127957], ["org_04", 103972]]
Expected result
[["org_01", 106600], ["org_02", 127490], ["org_03", 88313], ["org_05", 127957], ["org_04", 103972]]
Gold SQL
SELECT a.organization_id,SUM(s.net_spend_minor) FROM card_spend_daily s JOIN accounts a ON s.account_id=a.account_id WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND s.currency='USD' GROUP BY a.organization_id
B11For every CAD account, including zero-spend accounts, show July 2026 net card spend. Return account_id and net minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: account_id, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_minor_units\nFROM accounts a\nLEFT JOIN card_spend_daily csd ON a.account_id = csd.account_id AND csd.currency = 'CAD' AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date <= DATE '2026-07-31'\nWHERE a.currency = 'CAD'\nGROUP BY a.account_id"}Executed rows
[["acct_001", 129813], ["acct_002", 100922], ["acct_004", 106104], ["acct_005", 96902], ["acct_007", 130636], ["acct_008", 123233], ["acct_011", 126577], ["acct_013", 120981], ["acct_014", 122090], ["acct_016", 96620], ["acct_010", 124114], ["acct_019", 0], ["acct_017", 0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_spend_minor\nFROM accounts a\nLEFT JOIN card_spend_daily csd ON a.account_id = csd.account_id\n AND csd.currency = 'CAD'\n AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date <= DATE '2026-07-31'\nWHERE a.currency = 'CAD'\nGROUP BY a.account_id"}Executed rows
[["acct_001", 129813], ["acct_002", 100922], ["acct_004", 106104], ["acct_005", 96902], ["acct_007", 130636], ["acct_008", 123233], ["acct_011", 126577], ["acct_013", 120981], ["acct_014", 122090], ["acct_016", 96620], ["acct_010", 124114], ["acct_019", 0], ["acct_017", 0]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_card_spend_minor\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id\nLEFT JOIN card_spend_daily csd ON c.card_id = csd.card_id AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01'\nWHERE a.currency = 'CAD'\nGROUP BY a.account_id"}Executed rows
[["acct_001", 129813], ["acct_002", 100922], ["acct_004", 106104], ["acct_005", 96902], ["acct_007", 130636], ["acct_008", 123233], ["acct_011", 126577], ["acct_013", 120981], ["acct_014", 122090], ["acct_016", 96620], ["acct_010", 124114], ["acct_017", 0], ["acct_019", 0]]
Expected result
[["acct_001", 129813], ["acct_002", 100922], ["acct_004", 106104], ["acct_005", 96902], ["acct_007", 130636], ["acct_008", 123233], ["acct_011", 126577], ["acct_013", 120981], ["acct_014", 122090], ["acct_016", 96620], ["acct_010", 124114], ["acct_019", 0], ["acct_017", 0]]
Gold SQL
SELECT a.account_id,COALESCE(SUM(s.net_spend_minor),0) FROM accounts a LEFT JOIN card_spend_daily s ON a.account_id=s.account_id AND s.spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' WHERE a.currency='CAD' GROUP BY a.account_id
B12For every USD account, including zero-spend accounts, show July 2026 net card spend. Return account_id and net minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: account_id, net_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_spend_minor\nFROM accounts a\nLEFT JOIN card_spend_daily csd ON a.account_id = csd.account_id AND csd.currency = 'USD' AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date <= DATE '2026-07-31'\nWHERE a.currency = 'USD'\nGROUP BY a.account_id"}Executed rows
[["acct_003", 106600], ["acct_006", 127490], ["acct_009", 88313], ["acct_015", 127957], ["acct_012", 103972], ["acct_018", 0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_spend_minor\nFROM accounts a\nLEFT JOIN card_spend_daily csd ON a.account_id = csd.account_id\n AND csd.currency = 'USD'\n AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date <= DATE '2026-07-31'\nWHERE a.currency = 'USD'\nGROUP BY a.account_id"}Executed rows
[["acct_003", 106600], ["acct_006", 127490], ["acct_009", 88313], ["acct_015", 127957], ["acct_012", 103972], ["acct_018", 0]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COALESCE(SUM(csd.net_spend_minor), 0) AS net_card_spend_minor\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id\nLEFT JOIN card_spend_daily csd ON c.card_id = csd.card_id AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01'\nWHERE a.currency = 'USD'\nGROUP BY a.account_id"}Executed rows
[["acct_003", 106600], ["acct_006", 127490], ["acct_009", 88313], ["acct_015", 127957], ["acct_012", 103972], ["acct_018", 0]]
Expected result
[["acct_003", 106600], ["acct_006", 127490], ["acct_009", 88313], ["acct_015", 127957], ["acct_012", 103972], ["acct_018", 0]]
Gold SQL
SELECT a.account_id,COALESCE(SUM(s.net_spend_minor),0) FROM accounts a LEFT JOIN card_spend_daily s ON a.account_id=s.account_id AND s.spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' WHERE a.currency='USD' GROUP BY a.account_id
B13Show daily CAD net card spend during July 2026, only dates with card activity, in chronological order. Return UTC date as YYYY-MM-DD and net minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: date, net_minor. Row order required.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT STRFTIME(spend_date, '%Y-%m-%d') AS date, SUM(net_spend_minor) AS net_spend_minor FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' GROUP BY spend_date ORDER BY spend_date ASC"}Executed rows
[["2026-07-01", 66420], ["2026-07-02", 48706], ["2026-07-03", 29351], ["2026-07-04", 43817], ["2026-07-05", 25257], ["2026-07-06", 47767], ["2026-07-07", 54913], ["2026-07-08", 37942], ["2026-07-09", 37747], ["2026-07-10", 42269], ["2026-07-11", 58518], ["2026-07-12", 27667], ["2026-07-13", 52979], ["2026-07-14", 49789], ["2026-07-15", 25343], ["2026-07-16", 29279], ["2026-07-17", 41628], ["2026-07-18", 46129], ["2026-07-19", 34962], ["2026-07-20", 43742], ["2026-07-21", 40948], ["2026-07-22", 45552], ["2026-07-23", 26826], ["2026-07-24", 49751], ["2026-07-25", 30147], ["2026-07-26", 34592], ["2026-07-27", 61314], ["2026-07-28", 38322], ["2026-07-29", 37432], ["2026-07-30", 30821], ["2026-07-31", 38062]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT STRFTIME(spend_date, '%Y-%m-%d') AS date, SUM(net_spend_minor) AS net_spend_minor\nFROM card_spend_daily\nWHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'\nGROUP BY spend_date\nORDER BY spend_date ASC"}Executed rows
[["2026-07-01", 66420], ["2026-07-02", 48706], ["2026-07-03", 29351], ["2026-07-04", 43817], ["2026-07-05", 25257], ["2026-07-06", 47767], ["2026-07-07", 54913], ["2026-07-08", 37942], ["2026-07-09", 37747], ["2026-07-10", 42269], ["2026-07-11", 58518], ["2026-07-12", 27667], ["2026-07-13", 52979], ["2026-07-14", 49789], ["2026-07-15", 25343], ["2026-07-16", 29279], ["2026-07-17", 41628], ["2026-07-18", 46129], ["2026-07-19", 34962], ["2026-07-20", 43742], ["2026-07-21", 40948], ["2026-07-22", 45552], ["2026-07-23", 26826], ["2026-07-24", 49751], ["2026-07-25", 30147], ["2026-07-26", 34592], ["2026-07-27", 61314], ["2026-07-28", 38322], ["2026-07-29", 37432], ["2026-07-30", 30821], ["2026-07-31", 38062]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT spend_date AS date, SUM(net_spend_minor) AS net_card_spend_minor\nFROM card_spend_daily\nWHERE currency = 'CAD' AND spend_date >= '2026-07-01' AND spend_date < '2026-08-01'\nGROUP BY spend_date\nORDER BY spend_date ASC"}Executed rows
[["2026-07-01", 66420], ["2026-07-02", 48706], ["2026-07-03", 29351], ["2026-07-04", 43817], ["2026-07-05", 25257], ["2026-07-06", 47767], ["2026-07-07", 54913], ["2026-07-08", 37942], ["2026-07-09", 37747], ["2026-07-10", 42269], ["2026-07-11", 58518], ["2026-07-12", 27667], ["2026-07-13", 52979], ["2026-07-14", 49789], ["2026-07-15", 25343], ["2026-07-16", 29279], ["2026-07-17", 41628], ["2026-07-18", 46129], ["2026-07-19", 34962], ["2026-07-20", 43742], ["2026-07-21", 40948], ["2026-07-22", 45552], ["2026-07-23", 26826], ["2026-07-24", 49751], ["2026-07-25", 30147], ["2026-07-26", 34592], ["2026-07-27", 61314], ["2026-07-28", 38322], ["2026-07-29", 37432], ["2026-07-30", 30821], ["2026-07-31", 38062]]
Expected result
[["2026-07-01", 66420], ["2026-07-02", 48706], ["2026-07-03", 29351], ["2026-07-04", 43817], ["2026-07-05", 25257], ["2026-07-06", 47767], ["2026-07-07", 54913], ["2026-07-08", 37942], ["2026-07-09", 37747], ["2026-07-10", 42269], ["2026-07-11", 58518], ["2026-07-12", 27667], ["2026-07-13", 52979], ["2026-07-14", 49789], ["2026-07-15", 25343], ["2026-07-16", 29279], ["2026-07-17", 41628], ["2026-07-18", 46129], ["2026-07-19", 34962], ["2026-07-20", 43742], ["2026-07-21", 40948], ["2026-07-22", 45552], ["2026-07-23", 26826], ["2026-07-24", 49751], ["2026-07-25", 30147], ["2026-07-26", 34592], ["2026-07-27", 61314], ["2026-07-28", 38322], ["2026-07-29", 37432], ["2026-07-30", 30821], ["2026-07-31", 38062]]
Gold SQL
SELECT CAST(spend_date AS VARCHAR),SUM(net_spend_minor) FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND currency='CAD' GROUP BY spend_date ORDER BY spend_date
B14Which three CAD accounts had the highest July 2026 net card spend? Include all accounts as candidates. Return account_id and net minor units, descending by spend and then ascending by account_id to break ties.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: account_id, net_minor. Row order required.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT account_id, SUM(net_spend_minor) AS net_spend_minor FROM card_spend_daily WHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date <= DATE '2026-07-31' GROUP BY account_id ORDER BY net_spend_minor DESC, account_id ASC LIMIT 3"}Executed rows
[["acct_007", 130636], ["acct_001", 129813], ["acct_011", 126577]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT csd.account_id, SUM(csd.net_spend_minor) AS net_minor_units\nFROM card_spend_daily csd\nJOIN accounts a ON csd.account_id = a.account_id\nWHERE csd.currency = 'CAD' AND a.currency = 'CAD' AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01'\nGROUP BY csd.account_id\nORDER BY net_minor_units DESC, csd.account_id ASC\nLIMIT 3"}Executed rows
[["acct_007", 130636], ["acct_001", 129813], ["acct_011", 126577]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT account_id, SUM(net_spend_minor) AS net_spend_minor\nFROM card_spend_daily\nWHERE currency = 'CAD' AND spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'\nGROUP BY account_id\nORDER BY net_spend_minor DESC, account_id ASC\nLIMIT 3"}Executed rows
[["acct_007", 130636], ["acct_001", 129813], ["acct_011", 126577]]
Expected result
[["acct_007", 130636], ["acct_001", 129813], ["acct_011", 126577]]
Gold SQL
SELECT a.account_id,COALESCE(SUM(s.net_spend_minor),0) AS net FROM accounts a LEFT JOIN card_spend_daily s ON a.account_id=s.account_id AND s.spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' WHERE a.currency='CAD' GROUP BY a.account_id ORDER BY net DESC,a.account_id LIMIT 3
B15Which three category labels had the highest July 2026 net card spend in CAD? Include the uncategorized bucket. Return category label and net minor units, descending by spend and then ascending by label to break ties.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: category, net_minor. Row order required.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'CAD' AND EXTRACT(YEAR FROM cs.spend_date) = 2026 AND EXTRACT(MONTH FROM cs.spend_date) = 7\nGROUP BY category_label\nORDER BY net_minor_units DESC, category_label ASC\nLIMIT 3"}Executed rows
[["Meals", 293557], ["Uncategorized", 291177], ["Travel", 249615]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.currency = 'CAD' AND EXTRACT(YEAR FROM cs.spend_date) = 2026 AND EXTRACT(MONTH FROM cs.spend_date) = 7\nGROUP BY category_label\nORDER BY net_minor_units DESC, category_label ASC\nLIMIT 3"}Executed rows
[["Meals", 293557], ["Uncategorized", 291177], ["Travel", 249615]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(c.category_name, 'Uncategorized') AS category_label, SUM(cs.net_spend_minor) AS net_minor_units\nFROM card_spend_daily cs\nLEFT JOIN categories c ON cs.category_id = c.category_id\nWHERE cs.spend_date >= DATE '2026-07-01' AND cs.spend_date < DATE '2026-08-01' AND cs.currency = 'CAD'\nGROUP BY category_label\nORDER BY net_minor_units DESC, category_label ASC\nLIMIT 3"}Executed rows
[["Meals", 293557], ["Uncategorized", 291177], ["Travel", 249615]]
Expected result
[["Meals", 293557], ["Uncategorized", 291177], ["Travel", 249615]]
Gold SQL
SELECT COALESCE(c.category_name,'Uncategorized') AS label,SUM(s.net_spend_minor) AS net FROM card_spend_daily s LEFT JOIN categories c ON s.category_id=c.category_id WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' AND s.currency='CAD' GROUP BY label ORDER BY net DESC,label LIMIT 3
B16At the snapshot, how many active cards does every CAD account have? Include accounts with none. Return account_id and active-card count.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: account_id, active_cards. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COUNT(c.card_id) AS active_card_count FROM accounts a LEFT JOIN cards c ON a.account_id = c.account_id AND c.card_status = 'active' WHERE a.currency = 'CAD' GROUP BY a.account_id"}Executed rows
[["acct_001", 2], ["acct_002", 2], ["acct_004", 2], ["acct_005", 1], ["acct_007", 2], ["acct_008", 2], ["acct_010", 1], ["acct_011", 2], ["acct_013", 2], ["acct_014", 2], ["acct_016", 2], ["acct_017", 2], ["acct_019", 0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COUNT(c.card_id) AS active_card_count\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id AND c.card_status = 'active'\nWHERE a.currency = 'CAD'\nGROUP BY a.account_id"}Executed rows
[["acct_001", 2], ["acct_002", 2], ["acct_004", 2], ["acct_005", 1], ["acct_007", 2], ["acct_008", 2], ["acct_010", 1], ["acct_011", 2], ["acct_013", 2], ["acct_014", 2], ["acct_016", 2], ["acct_017", 2], ["acct_019", 0]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id, COUNT(c.card_id) AS active_card_count\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id AND c.card_status = 'active'\nWHERE a.currency = 'CAD'\nGROUP BY a.account_id"}Executed rows
[["acct_001", 2], ["acct_002", 2], ["acct_004", 2], ["acct_005", 1], ["acct_007", 2], ["acct_008", 2], ["acct_010", 1], ["acct_011", 2], ["acct_013", 2], ["acct_014", 2], ["acct_016", 2], ["acct_017", 2], ["acct_019", 0]]
Expected result
[["acct_001", 2], ["acct_002", 2], ["acct_004", 2], ["acct_005", 1], ["acct_007", 2], ["acct_008", 2], ["acct_010", 1], ["acct_011", 2], ["acct_013", 2], ["acct_014", 2], ["acct_016", 2], ["acct_017", 2], ["acct_019", 0]]
Gold SQL
SELECT a.account_id,COUNT(c.card_id) FROM accounts a LEFT JOIN cards c ON a.account_id=c.account_id AND c.card_status='active' WHERE a.currency='CAD' GROUP BY a.account_id
B17At the snapshot, how many active cards are there in each account currency? Return currency and active-card count, retaining currencies with no active cards.Bare: correct · Allowed values: query denied · Documentation: correct
Rubric: analytical SQL. Expected columns: currency, active_cards. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT currency, COUNT(*) FILTER (WHERE card_status = 'active') AS active_card_count FROM cards RIGHT JOIN accounts USING(account_id) GROUP BY currency ORDER BY currency"}Executed rows
[["CAD", 22], ["USD", 5]]
Schema plus allowed values
query denied
Original model response
{"kind":"sql","sql":"SELECT currency, COUNT(*) AS active_card_count FROM cards JOIN accounts USING(account_id) WHERE card_status = 'active' GROUP BY currency UNION ALL SELECT currency, 0 FROM accounts WHERE currency NOT IN (SELECT currency FROM cards JOIN accounts USING(account_id) WHERE card_status = 'active') GROUP BY currency ORDER BY currency"}Executed rows
null
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT a.currency, COUNT(c.card_id) AS active_card_count\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id AND c.card_status = 'active'\nGROUP BY a.currency\nORDER BY a.currency"}Executed rows
[["CAD", 22], ["USD", 5]]
Expected result
[["CAD", 22], ["USD", 5]]
Gold SQL
SELECT a.currency,COUNT(c.card_id) FROM accounts a LEFT JOIN cards c ON a.account_id=c.account_id AND c.card_status='active' GROUP BY a.currency
B18For each CAD purchase that received a refund in July 2026, show original transaction_id, original purchase amount and the total refunded during July, all amounts in minor units. The original purchase may be in another reporting period.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: transaction_id, original_minor, refunded_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"WITH RefundsInJuly AS (\n SELECT\n related_transaction_id AS original_transaction_id,\n SUM(amount_minor) AS total_refunded_july\n FROM transactions\n WHERE direction = 'credit'\n AND currency = 'CAD'\n AND status = 'posted'\n AND event_kind = 'refund'\n AND EXTRACT(YEAR FROM occurred_at) = 2026\n AND EXTRACT(MONTH FROM occurred_at) = 7\n GROUP BY related_transaction_id\n),\nOriginalPurchases AS (\n SELECT\n transaction_id,\n amount_minor AS original_purchase_amount\n FROM transactions\n WHERE direction = 'debit'\n AND currency = 'CAD'\n AND status = 'posted'\n AND event_kind = 'purchase'\n)\nSELECT\n o.transaction_id AS original_transaction_id,\n o.original_purchase_amount,\n r.total_refunded_july\nFROM OriginalPurchases o\nJOIN RefundsInJuly r ON o.transaction_id = r.original_transaction_id"}Executed rows
[["txn_00007", 1520, 506], ["txn_00015", 7754, 2584], ["txn_00030", 8191, 2730], ["txn_00041", 9577, 3192], ["txn_00049", 1443, 481], ["txn_00057", 4566, 1522], ["txn_00065", 546, 182], ["txn_00118", 5858, 1952], ["txn_00126", 3656, 1218], ["txn_00134", 6827, 2275], ["txn_00145", 5990, 1996], ["txn_00153", 3000, 1000], ["txn_00212", 7369, 2456], ["txn_00220", 6360, 2120], ["txn_00228", 9820, 3273], ["txn_00236", 5333, 1777], ["txn_00247", 6544, 2181], ["txn_00262", 5017, 1672], ["txn_00270", 6727, 2242], ["txn_00316", 960, 320], ["txn_00324", 7040, 2346], ["txn_00332", 3863, 1287], ["txn_00340", 3740, 1246], ["txn_00351", 5838, 1946], ["txn_00359", 1907, 635], ["txn_00367", 2869, 956], ["txn_00418", 5596, 1865], ["txn_00426", 5476, 1825], ["txn_00434", 5894, 1964], ["txn_00442", 4394, 1464], ["txn_00453", 8460, 2820], ["txn_00461", 7231, 2410], ["txn_00469", 1055, 351], ["txn_00477", 2564, 854], ["txn_00522", 3059, 1019], ["txn_00530", 3720, 1240], ["txn_00538", 4208, 1402], ["boundary_june_occurrence", 12345, 3345]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"WITH RefundsInJuly AS (\n SELECT\n related_transaction_id AS original_transaction_id,\n SUM(amount_minor) AS total_refunded_july\n FROM transactions\n WHERE event_kind = 'refund'\n AND currency = 'CAD'\n AND status = 'posted'\n AND EXTRACT(YEAR FROM occurred_at) = 2026\n AND EXTRACT(MONTH FROM occurred_at) = 7\n GROUP BY related_transaction_id\n),\nOriginalPurchases AS (\n SELECT\n transaction_id,\n amount_minor AS original_purchase_amount\n FROM transactions\n WHERE event_kind = 'purchase'\n AND currency = 'CAD'\n AND status = 'posted'\n)\nSELECT\n o.transaction_id AS original_transaction_id,\n o.original_purchase_amount,\n r.total_refunded_july\nFROM OriginalPurchases o\nJOIN RefundsInJuly r ON o.transaction_id = r.original_transaction_id;"}Executed rows
[["txn_00007", 1520, 506], ["txn_00015", 7754, 2584], ["txn_00030", 8191, 2730], ["txn_00041", 9577, 3192], ["txn_00049", 1443, 481], ["txn_00057", 4566, 1522], ["txn_00065", 546, 182], ["txn_00118", 5858, 1952], ["txn_00126", 3656, 1218], ["txn_00134", 6827, 2275], ["txn_00145", 5990, 1996], ["txn_00153", 3000, 1000], ["txn_00212", 7369, 2456], ["txn_00220", 6360, 2120], ["txn_00228", 9820, 3273], ["txn_00236", 5333, 1777], ["txn_00247", 6544, 2181], ["txn_00262", 5017, 1672], ["txn_00270", 6727, 2242], ["txn_00316", 960, 320], ["txn_00324", 7040, 2346], ["txn_00332", 3863, 1287], ["txn_00340", 3740, 1246], ["txn_00351", 5838, 1946], ["txn_00359", 1907, 635], ["txn_00367", 2869, 956], ["txn_00418", 5596, 1865], ["txn_00426", 5476, 1825], ["txn_00434", 5894, 1964], ["txn_00442", 4394, 1464], ["txn_00453", 8460, 2820], ["txn_00461", 7231, 2410], ["txn_00469", 1055, 351], ["txn_00477", 2564, 854], ["txn_00522", 3059, 1019], ["txn_00530", 3720, 1240], ["txn_00538", 4208, 1402], ["boundary_june_occurrence", 12345, 3345]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT p.transaction_id AS original_transaction_id, p.amount_minor AS original_purchase_amount, SUM(r.amount_minor) AS total_refunded_in_july\nFROM transactions p\nJOIN transactions r ON r.related_transaction_id = p.transaction_id\nWHERE p.event_kind = 'purchase'\n AND p.currency = 'CAD'\n AND r.event_kind = 'refund'\n AND r.status = 'posted'\n AND r.posted_at >= '2026-07-01' AND r.posted_at < '2026-08-01'\nGROUP BY p.transaction_id, p.amount_minor"}Executed rows
[["txn_00007", 1520, 506], ["txn_00015", 7754, 2584], ["txn_00030", 8191, 2730], ["txn_00041", 9577, 3192], ["txn_00049", 1443, 481], ["txn_00057", 4566, 1522], ["txn_00065", 546, 182], ["txn_00118", 5858, 1952], ["txn_00126", 3656, 1218], ["txn_00134", 6827, 2275], ["txn_00145", 5990, 1996], ["txn_00153", 3000, 1000], ["txn_00212", 7369, 2456], ["txn_00220", 6360, 2120], ["txn_00228", 9820, 3273], ["txn_00236", 5333, 1777], ["txn_00247", 6544, 2181], ["txn_00262", 5017, 1672], ["txn_00270", 6727, 2242], ["txn_00316", 960, 320], ["txn_00324", 7040, 2346], ["txn_00332", 3863, 1287], ["txn_00340", 3740, 1246], ["txn_00351", 5838, 1946], ["txn_00359", 1907, 635], ["txn_00367", 2869, 956], ["txn_00418", 5596, 1865], ["txn_00426", 5476, 1825], ["txn_00434", 5894, 1964], ["txn_00442", 4394, 1464], ["txn_00453", 8460, 2820], ["txn_00461", 7231, 2410], ["txn_00469", 1055, 351], ["txn_00477", 2564, 854], ["txn_00522", 3059, 1019], ["txn_00530", 3720, 1240], ["txn_00538", 4208, 1402], ["boundary_june_occurrence", 12345, 3345]]
Expected result
[["txn_00007", 1520, 506], ["txn_00015", 7754, 2584], ["txn_00030", 8191, 2730], ["txn_00041", 9577, 3192], ["txn_00049", 1443, 481], ["txn_00057", 4566, 1522], ["txn_00065", 546, 182], ["txn_00118", 5858, 1952], ["txn_00126", 3656, 1218], ["txn_00134", 6827, 2275], ["txn_00145", 5990, 1996], ["txn_00153", 3000, 1000], ["txn_00212", 7369, 2456], ["txn_00220", 6360, 2120], ["txn_00228", 9820, 3273], ["txn_00236", 5333, 1777], ["txn_00247", 6544, 2181], ["txn_00262", 5017, 1672], ["txn_00270", 6727, 2242], ["txn_00316", 960, 320], ["txn_00324", 7040, 2346], ["txn_00332", 3863, 1287], ["txn_00340", 3740, 1246], ["txn_00351", 5838, 1946], ["txn_00359", 1907, 635], ["txn_00367", 2869, 956], ["txn_00418", 5596, 1865], ["txn_00426", 5476, 1825], ["txn_00434", 5894, 1964], ["txn_00442", 4394, 1464], ["txn_00453", 8460, 2820], ["txn_00461", 7231, 2410], ["txn_00469", 1055, 351], ["txn_00477", 2564, 854], ["txn_00522", 3059, 1019], ["txn_00530", 3720, 1240], ["txn_00538", 4208, 1402], ["boundary_june_occurrence", 12345, 3345]]
Gold SQL
SELECT p.transaction_id,p.amount_minor,SUM(r.amount_minor) FROM transactions r JOIN transactions p ON r.related_transaction_id=p.transaction_id WHERE r.event_kind='refund' AND r.status='posted' AND r.currency='CAD' AND r.posted_at >= TIMESTAMP '2026-07-01' AND r.posted_at < TIMESTAMP '2026-08-01' GROUP BY p.transaction_id,p.amount_minor
B19How many distinct CAD purchases received at least one refund during July 2026? Return one count, including purchases from earlier periods.Bare: wrong answer · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: purchase_count. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COUNT(DISTINCT t1.transaction_id) AS count_distinct_cad_purchases_with_refund FROM transactions t1 JOIN transactions t2 ON t1.transaction_id = t2.related_transaction_id WHERE t1.direction = 'credit' AND t1.currency = 'CAD' AND t1.status = 'posted' AND t2.direction = 'debit' AND t2.status = 'posted' AND t2.occurred_at >= '2026-07-01' AND t2.occurred_at < '2026-08-01'"}Executed rows
[[0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(DISTINCT t1.transaction_id) AS count_distinct_purchases_with_refund\nFROM transactions t1\nJOIN transactions t2 ON t1.transaction_id = t2.related_transaction_id\nWHERE t1.currency = 'CAD'\n AND t1.event_kind = 'purchase'\n AND t1.status = 'posted'\n AND t2.event_kind = 'refund'\n AND t2.status = 'posted'\n AND EXTRACT(YEAR FROM t2.occurred_at) = 2026\n AND EXTRACT(MONTH FROM t2.occurred_at) = 7"}Executed rows
[[38]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(DISTINCT t1.transaction_id) AS count_refunded_purchases\nFROM transactions t1\nJOIN transactions t2 ON t2.related_transaction_id = t1.transaction_id\nWHERE t1.currency = 'CAD'\n AND t1.event_kind = 'purchase'\n AND t1.status = 'posted'\n AND t2.event_kind = 'refund'\n AND t2.status = 'posted'\n AND t2.posted_at >= '2026-07-01' AND t2.posted_at < '2026-08-01'"}Executed rows
[[38]]
Expected result
[[38]]
Gold SQL
SELECT COUNT(DISTINCT related_transaction_id) FROM transactions WHERE event_kind='refund' AND status='posted' AND currency='CAD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B20What was the deposit amount in CAD during July 2026? Return one amount in minor units.Bare: wrong answer · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_minor FROM transactions WHERE direction = 'inbound' AND currency = 'CAD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND status = 'posted'"}Executed rows
[[0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_minor FROM transactions WHERE event_kind = 'deposit' AND currency = 'CAD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND status = 'posted'"}Executed rows
[[769596]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'deposit' AND status = 'posted' AND posted_at >= '2026-07-01' AND posted_at < '2026-08-01'"}Executed rows
[[769596]]
Expected result
[[769596]]
Gold SQL
SELECT COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='deposit' AND status='posted' AND currency='CAD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B21What was the deposit amount in USD during July 2026? Return one amount in minor units.Bare: wrong answer · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_minor FROM transactions WHERE direction = 'inbound' AND status = 'posted' AND currency = 'USD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01'"}Executed rows
[[0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_usd_july_2026 FROM transactions WHERE event_kind = 'deposit' AND currency = 'USD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND status = 'posted'"}Executed rows
[[320665]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS deposit_amount_usd FROM transactions WHERE currency = 'USD' AND event_kind = 'deposit' AND status = 'posted' AND posted_at >= '2026-07-01' AND posted_at < '2026-08-01'"}Executed rows
[[320665]]
Expected result
[[320665]]
Gold SQL
SELECT COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='deposit' AND status='posted' AND currency='USD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B22What was the withdrawal amount in CAD during July 2026? Return one amount in minor units.Bare: wrong answer · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS withdrawal_amount_minor FROM transactions WHERE currency = 'CAD' AND direction = 'outbound' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01'"}Executed rows
[[0]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS withdrawal_amount_minor FROM transactions WHERE event_kind = 'withdrawal' AND currency = 'CAD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND status = 'posted'"}Executed rows
[[12084]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(amount_minor), 0) AS withdrawal_amount_minor FROM transactions WHERE event_kind = 'withdrawal' AND currency = 'CAD' AND status = 'posted' AND posted_at >= '2026-07-01' AND posted_at < '2026-08-01'"}Executed rows
[[12084]]
Expected result
[[12084]]
Gold SQL
SELECT COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='withdrawal' AND status='posted' AND currency='CAD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B23What was net external cash flow in CAD during July 2026? Return one signed amount in minor units.Bare: wrong answer · Allowed values: wrong answer · Documentation: SQL error
Rubric: analytical SQL. Expected columns: net_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(CASE WHEN direction = 'inbound' THEN amount_minor ELSE -amount_minor END), 0) AS net_external_cash_flow_minor FROM transactions WHERE currency = 'CAD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND event_kind = 'external' AND status = 'posted'"}Executed rows
[[0]]
Schema plus allowed values
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END), 0) AS net_external_cash_flow_minor FROM transactions WHERE currency = 'CAD' AND status = 'posted' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND event_kind IN ('deposit', 'withdrawal', 'transfer')"}Executed rows
[[757512]]
Full documentation
SQL error
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END), 0) AS net_external_cash_flow_cad_minor\nFROM transactions\nJOIN accounts USING (account_id)\nWHERE currency = 'CAD'\n AND status = 'posted'\n AND event_kind != 'transfer'\n AND posted_at >= '2026-07-01 00:00:00'\n AND posted_at < '2026-08-01 00:00:00'"}Executed rows
null
Expected result
[[-520480]]
Gold SQL
SELECT COALESCE(SUM(CASE WHEN direction='credit' THEN amount_minor ELSE -amount_minor END),0) FROM transactions WHERE event_kind <> 'transfer' AND status='posted' AND currency='CAD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B24What was net external cash flow in USD during July 2026? Return one signed amount in minor units.Bare: wrong answer · Allowed values: wrong answer · Documentation: correct
Rubric: analytical SQL. Expected columns: net_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(CASE WHEN direction = 'inbound' THEN amount_minor ELSE -amount_minor END), 0) AS net_external_cash_flow_usd_minor FROM transactions WHERE currency = 'USD' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND event_kind = 'external' AND status = 'posted'"}Executed rows
[[0]]
Schema plus allowed values
wrong answer
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END), 0) AS net_external_cash_flow_usd_july_2026 FROM transactions WHERE currency = 'USD' AND status = 'posted' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01' AND event_kind IN ('deposit', 'withdrawal')"}Executed rows
[[315630]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(CASE WHEN direction = 'credit' THEN amount_minor ELSE -amount_minor END) AS net_external_cash_flow_usd\nFROM transactions\nWHERE currency = 'USD'\n AND status = 'posted'\n AND event_kind != 'transfer'\n AND posted_at >= '2026-07-01 00:00:00'\n AND posted_at < '2026-08-01 00:00:00'"}Executed rows
[[-238702]]
Expected result
[[-238702]]
Gold SQL
SELECT COALESCE(SUM(CASE WHEN direction='credit' THEN amount_minor ELSE -amount_minor END),0) FROM transactions WHERE event_kind <> 'transfer' AND status='posted' AND currency='USD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B25What was internal transfer volume in CAD during July 2026? Count each transfer once. Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: SQL error
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(ABS(amount_minor)) AS internal_transfer_volume_minor FROM (SELECT DISTINCT transfer_group_id, amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'transfer' AND status = 'posted' AND DATE_TRUNC('month', occurred_at) = DATE '2026-07-01' AND transfer_group_id IS NOT NULL)"}Executed rows
[[13910]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(ABS(amount_minor)), 0) AS internal_transfer_volume_cad_july_2026 FROM (SELECT DISTINCT transfer_group_id, amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'transfer' AND status = 'posted' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7 AND transfer_group_id IS NOT NULL)"}Executed rows
[[13910]]
Full documentation
SQL error
Original model response
{"kind":"sql","sql":"SELECT SUM(t.amount_minor) AS internal_transfer_volume_cad_july_2026\nFROM (\n SELECT DISTINCT tg.transfer_group_id, t.amount_minor\n FROM transactions t\n JOIN transactions t2 ON t.transfer_group_id = t2.transfer_group_id\n WHERE t.currency = 'CAD'\n AND t.event_kind = 'transfer'\n AND t.status = 'posted'\n AND t.posted_at >= '2026-07-01' AND t.posted_at < '2026-08-01'\n AND t.direction = 'debit'\n) tg"}Executed rows
null
Expected result
[[13910]]
Gold SQL
SELECT COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='transfer' AND status='posted' AND direction='debit' AND currency='CAD' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01'
B26How many internal transfers completed during July 2026, by currency? Count each transfer once. Return currency and transfer count.Bare: wrong answer · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: currency, transfer_count. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT currency, COUNT(DISTINCT transfer_group_id) AS transfer_count FROM transactions WHERE event_kind = 'transfer' AND status = 'completed' AND DATE(posted_at) >= DATE '2026-07-01' AND DATE(posted_at) < DATE '2026-08-01' GROUP BY currency"}Executed rows
[]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT currency, COUNT(DISTINCT transfer_group_id) AS transfer_count\nFROM transactions\nWHERE event_kind = 'transfer'\n AND status = 'posted'\n AND EXTRACT(YEAR FROM occurred_at) = 2026\n AND EXTRACT(MONTH FROM occurred_at) = 7\nGROUP BY currency"}Executed rows
[["CAD", 4], ["USD", 4]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT currency, COUNT(DISTINCT transfer_group_id) AS transfer_count\nFROM transactions\nWHERE event_kind = 'transfer'\n AND status = 'posted'\n AND posted_at >= '2026-07-01' AND posted_at < '2026-08-01'\nGROUP BY currency"}Executed rows
[["CAD", 4], ["USD", 4]]
Expected result
[["CAD", 4], ["USD", 4]]
Gold SQL
SELECT currency,COUNT(DISTINCT transfer_group_id) FROM transactions WHERE event_kind='transfer' AND status='posted' AND posted_at >= TIMESTAMP '2026-07-01' AND posted_at < TIMESTAMP '2026-08-01' GROUP BY currency
B27What was the combined closing ledger balance of all CAD accounts on 2026-07-31? Return one signed amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: balance_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[125676]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[125676]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[125676]]
Expected result
[[125676]]
Gold SQL
SELECT SUM(closing_minor) FROM account_daily WHERE currency='CAD' AND balance_date=DATE '2026-07-31'
B28What was the combined closing ledger balance of all USD accounts on 2026-07-31? Return one signed amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: balance_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'USD'"}Executed rows
[[20113]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'USD'"}Executed rows
[[20113]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'USD'"}Executed rows
[[20113]]
Expected result
[[20113]]
Gold SQL
SELECT SUM(closing_minor) FROM account_daily WHERE currency='USD' AND balance_date=DATE '2026-07-31'
B29What was the combined opening ledger balance of all CAD accounts on 2026-07-01? Return one signed amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: balance_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(opening_minor) FROM account_daily WHERE balance_date = DATE '2026-07-01' AND currency = 'CAD'"}Executed rows
[[646156]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(opening_balance_minor) FROM accounts WHERE currency = 'CAD' AND account_id IN (SELECT account_id FROM account_daily WHERE balance_date = DATE '2026-07-01')"}Executed rows
[[646156]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(opening_balance_minor) FROM accounts WHERE currency = 'CAD'"}Executed rows
[[646156]]
Expected result
[[646156]]
Gold SQL
SELECT SUM(opening_minor) FROM account_daily WHERE currency='CAD' AND balance_date=DATE '2026-07-01'
B30What was the combined closing ledger balance of all CAD accounts on 2026-07-15? Return one signed amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: balance_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-15' AND currency = 'CAD'"}Executed rows
[[308471]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-15' AND currency = 'CAD'"}Executed rows
[[308471]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-15' AND currency = 'CAD'"}Executed rows
[[308471]]
Expected result
[[308471]]
Gold SQL
SELECT SUM(closing_minor) FROM account_daily WHERE currency='CAD' AND balance_date=DATE '2026-07-15'
B31What was gross CAD card spend on 2026-07-01? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) FROM card_spend_daily WHERE spend_date = DATE '2026-07-01' AND currency = 'CAD'"}Executed rows
[[66420]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) FROM card_spend_daily WHERE spend_date = DATE '2026-07-01' AND currency = 'CAD'"}Executed rows
[[66420]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) AS gross_cad_card_spend FROM card_spend_daily WHERE spend_date = DATE '2026-07-01' AND currency = 'CAD'"}Executed rows
[[66420]]
Expected result
[[66420]]
Gold SQL
SELECT COALESCE(SUM(purchased_minor),0) FROM card_spend_daily WHERE currency='CAD' AND spend_date=DATE '2026-07-01'
B32What was gross CAD card spend on 2026-07-31? Return one amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) FROM card_spend_daily WHERE spend_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[38062]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) AS gross_cad_card_spend FROM card_spend_daily WHERE spend_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[38062]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COALESCE(SUM(purchased_minor), 0) AS gross_cad_card_spend_minor FROM card_spend_daily WHERE spend_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
[[38062]]
Expected result
[[38062]]
Gold SQL
SELECT COALESCE(SUM(purchased_minor),0) FROM card_spend_daily WHERE currency='CAD' AND spend_date=DATE '2026-07-31'
B33For CAD purchase events marked pending at the snapshot and initiated during July 2026, return the event count and total amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: event_count, amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND status = 'pending' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01'"}Executed rows
[[31, 146326]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND status = 'pending' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7"}Executed rows
[[31, 146326]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND status = 'pending' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01'"}Executed rows
[[31, 146326]]
Expected result
[[31, 146326]]
Gold SQL
SELECT COUNT(*),COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='purchase' AND status='pending' AND currency='CAD' AND occurred_at >= TIMESTAMP '2026-07-01' AND occurred_at < TIMESTAMP '2026-08-01'
B34For CAD purchase events marked void at the snapshot and initiated during July 2026, return the event count and total amount in minor units.Bare: correct · Allowed values: correct · Documentation: correct
Rubric: analytical SQL. Expected columns: event_count, amount_minor. Row order not scored.
Bare schema
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND status = 'void' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7"}Executed rows
[[25, 145470]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE event_kind = 'purchase' AND status = 'void' AND currency = 'CAD' AND EXTRACT(YEAR FROM occurred_at) = 2026 AND EXTRACT(MONTH FROM occurred_at) = 7"}Executed rows
[[25, 145470]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT COUNT(*) AS event_count, SUM(amount_minor) AS total_amount_minor FROM transactions WHERE currency = 'CAD' AND event_kind = 'purchase' AND status = 'void' AND occurred_at >= '2026-07-01' AND occurred_at < '2026-08-01'"}Executed rows
[[25, 145470]]
Expected result
[[25, 145470]]
Gold SQL
SELECT COUNT(*),COALESCE(SUM(amount_minor),0) FROM transactions WHERE event_kind='purchase' AND status='void' AND currency='CAD' AND occurred_at >= TIMESTAMP '2026-07-01' AND occurred_at < TIMESTAMP '2026-08-01'
B35Which CAD accounts had no purchases contributing to July 2026 card spend? Return account_id, including accounts with other ledger activity.Bare: correct · Allowed values: correct · Documentation: wrong answer
Rubric: analytical SQL. Expected columns: account_id. Row order not scored.
Bare schema
correct
Originally scored query denied; rescored after the validator correction.
Original model response
{"kind":"sql","sql":"SELECT a.account_id\nFROM accounts a\nWHERE a.currency = 'CAD'\nAND NOT EXISTS (\n SELECT 1\n FROM card_spend_daily csd\n WHERE csd.account_id = a.account_id\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\n AND csd.purchased_minor > 0\n)"}Executed rows
[["acct_019"], ["acct_017"]]
Schema plus allowed values
correct
Original model response
{"kind":"sql","sql":"SELECT a.account_id\nFROM accounts a\nWHERE a.currency = 'CAD'\nAND a.account_id NOT IN (\n SELECT DISTINCT csd.account_id\n FROM card_spend_daily csd\n WHERE csd.currency = 'CAD'\n AND csd.spend_date >= DATE '2026-07-01'\n AND csd.spend_date < DATE '2026-08-01'\n AND csd.purchased_minor > 0\n)"}Executed rows
[["acct_017"], ["acct_019"]]
Full documentation
wrong answer
Original model response
{"kind":"sql","sql":"SELECT a.account_id\nFROM accounts a\nLEFT JOIN cards c ON a.account_id = c.account_id\nLEFT JOIN card_spend_daily csd ON c.card_id = csd.card_id AND csd.spend_date >= DATE '2026-07-01' AND csd.spend_date < DATE '2026-08-01' AND csd.purchased_minor > 0\nWHERE a.currency = 'CAD' AND csd.card_id IS NULL"}Executed rows
[["acct_017"], ["acct_019"], ["acct_017"]]
Expected result
[["acct_019"], ["acct_017"]]
Gold SQL
SELECT a.account_id FROM accounts a LEFT JOIN card_spend_daily s ON a.account_id=s.account_id AND s.spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01' WHERE a.currency='CAD' GROUP BY a.account_id HAVING COALESCE(SUM(s.purchase_count),0)=0
B36Reconcile July 2026 ledger balances by currency. Return currency, opening balance at July 1, total signed posted movement during July, and closing balance at July 31. Use minor units and include all accounts.Bare: wrong answer · Allowed values: wrong answer · Documentation: correct
Rubric: analytical SQL. Expected columns: currency, opening_minor, movement_minor, closing_minor. Row order not scored.
Bare schema
wrong answer
Original model response
{"kind":"sql","sql":"SELECT currency, SUM(opening_minor) AS opening_balance, SUM(posted_credit_minor - posted_debit_minor) AS total_signed_posted_movement, SUM(closing_minor) AS closing_balance FROM account_daily WHERE balance_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31' GROUP BY currency ORDER BY currency"}Executed rows
[["CAD", 15227999, -520480, 14707519], ["USD", 5686561, -238702, 5447859]]
Schema plus allowed values
wrong answer
Original model response
{"kind":"sql","sql":"SELECT currency,\n MIN(opening_minor) AS opening_balance,\n SUM(posted_credit_minor - posted_debit_minor) AS total_signed_posted_movement,\n MAX(closing_minor) AS closing_balance\nFROM account_daily\nWHERE balance_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'\nGROUP BY currency"}Executed rows
[["CAD", -32784, -520480, 128248], ["USD", -5139, -238702, 72958]]
Full documentation
correct
Original model response
{"kind":"sql","sql":"SELECT currency,\n SUM(opening_balance_minor) AS opening_balance,\n SUM(COALESCE(posted_credit_minor, 0) - COALESCE(posted_debit_minor, 0)) AS total_signed_posted_movement,\n SUM(closing_minor) AS closing_balance\nFROM (\n SELECT account_id, currency, opening_balance_minor, 0 AS posted_credit_minor, 0 AS posted_debit_minor, 0 AS closing_minor\n FROM accounts\n UNION ALL\n SELECT account_id, currency, 0, posted_credit_minor, posted_debit_minor, 0\n FROM account_daily\n WHERE balance_date >= '2026-07-01' AND balance_date <= '2026-07-31'\n UNION ALL\n SELECT account_id, currency, 0, 0, 0, closing_minor\n FROM account_daily\n WHERE balance_date = '2026-07-31'\n) AS combined\nGROUP BY currency"}Executed rows
[["CAD", 646156, -520480, 125676], ["USD", 258815, -238702, 20113]]
Expected result
[["CAD", 646156, -520480, 125676], ["USD", 258815, -238702, 20113]]
Gold SQL
SELECT currency,SUM(CASE WHEN balance_date=DATE '2026-07-01' THEN opening_minor ELSE 0 END),SUM(posted_credit_minor-posted_debit_minor),SUM(CASE WHEN balance_date=DATE '2026-07-31' THEN closing_minor ELSE 0 END) FROM account_daily WHERE balance_date >= DATE '2026-07-01' AND balance_date < DATE '2026-08-01' GROUP BY currency
B37What was total net card spend during July 2026? Return one monetary total.Bare: answered instead of asking · Allowed values: request failed · Documentation: answered instead of asking
Rubric: clarification. Expected columns: clarification labels. Row order not scored.
Bare schema
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(net_spend_minor) AS total_net_card_spend FROM card_spend_daily WHERE spend_date >= '2026-07-01' AND spend_date < '2026-08-01'"}Executed rows
null
Schema plus allowed values
request failed
Original model response
No response content. Acquisition status: error (HTTP 503)
Executed rows
null
Full documentation
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(net_spend_minor) AS total_net_card_spend FROM card_spend_daily WHERE spend_date >= DATE '2026-07-01' AND spend_date < DATE '2026-08-01'"}Executed rows
null
Expected result
{"missing": ["currency"]}Gold SQL
No SQL: ask for the missing definition.
B38What was net card spend in CAD? Return one monetary total.Bare: answered instead of asking · Allowed values: answered instead of asking · Documentation: answered instead of asking
Rubric: clarification. Expected columns: clarification labels. Row order not scored.
Bare schema
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(net_spend_minor) AS net_card_spend_cad FROM card_spend_daily WHERE currency = 'CAD'"}Executed rows
null
Schema plus allowed values
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(net_spend_minor) FROM card_spend_daily WHERE currency = 'CAD'"}Executed rows
null
Full documentation
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(net_spend_minor) AS net_card_spend_cad FROM card_spend_daily WHERE currency = 'CAD'"}Executed rows
null
Expected result
{"missing": ["time_window"]}Gold SQL
No SQL: ask for the missing definition.
B39What was the combined balance of all CAD accounts on 2026-07-31? Return one monetary total.Bare: answered instead of asking · Allowed values: answered instead of asking · Documentation: answered instead of asking
Rubric: clarification. Expected columns: clarification labels. Row order not scored.
Bare schema
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) AS combined_balance FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
null
Schema plus allowed values
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
null
Full documentation
answered instead of asking
Original model response
{"kind":"sql","sql":"SELECT SUM(closing_minor) AS total_balance_minor FROM account_daily WHERE balance_date = DATE '2026-07-31' AND currency = 'CAD'"}Executed rows
null
Expected result
{"missing": ["balance_definition"]}Gold SQL
No SQL: ask for the missing definition.
B40Which CAD accounts were high risk during July 2026? Return account_id.Bare: wrong clarification · Allowed values: wrong clarification · Documentation: invalid response
Rubric: clarification. Expected columns: clarification labels. Row order not scored.
Bare schema
wrong clarification
Original model response
{"kind":"clarification","missing":["balance_definition","metric_definition"]}Executed rows
null
Schema plus allowed values
wrong clarification
Original model response
{"kind":"clarification","missing":["time_window","metric_definition"]}Executed rows
null
Full documentation
invalid response
Original model response
{"kind":"clarification","missing":["risk threshold"]}Executed rows
null
Expected result
{"missing": ["metric_definition"]}Gold SQL
No SQL: ask for the missing definition.
Method and checks
Run settings and labels
- Model gpt-4.1-mini-2025-04-14, temperature 0, seed 7, an 800-token output ceiling and a JSON-object response format in every arm. A fixed seed does not prove deterministic provider behaviour; each question and arm has one retained outcome.
- Run 1 (bare and documented): banking-docs-c9aef3140970, frozen and collected 2026-10-08. Arm order alternated by question.
- Rescored run 1: banking-docs-c9aef3140970-exists-corrected. Allowed-values arm: banking-values-a651d652e76a, protocol addendum frozen 2026-10-11 03:44 UTC before any request (sha256 dcdc1663991b460346d47a7f7b941fa20611c87db037a51b692b19989a8311ae), collected 2026-10-11.
- Synthetic data seed 20261008. Bootstrap: paired percentile over questions, 10,000 resamples, seed 7. The twin-clustered version resamples 32 clusters: Questions whose text is identical after replacing CAD and USD with one placeholder form one cluster; every other question is its own cluster.
How answers were scored
Each analytical question has gold SQL that agrees with a separate Python calculation over the source records. Answers run in an isolated sandbox with read-only data, no network and no credentials, and must match the expected rows exactly in integer minor units. Column names do not matter; row order matters only when the question asks for it. Errors, denied queries, invalid responses and failed requests stay in the denominator. A clarification answer must name exactly the missing definitions, with no SQL.
The validator correction
The unchanged evaluator archive 0.1.0 denies any parsed SQL function outside its list, except AND, OR and NOT. Its parser represents EXISTS as a function, so NOT EXISTS (SELECT ...) was denied even though the prompt allows ordinary subqueries. The correction, in banking/correction.py, applies the same checks and treats EXISTS like the other Boolean operators; tables, functions and recursion inside the subquery are still checked. A test confirms that it agrees with the original validator on every gold query and saved answer without EXISTS. Founder ruling D12 recorded it as a declared correction. Only one of the 80 run 1 answers had been denied.
How the allowed-values arm was added
Founder ruling D23 added it after run 1 was scored. Its protocol addendum (values-arm-protocol-addendum.json) fixed the value list, its sha256, the 40 cache identities, the settings, the US$0.50 spend cap and this analysis before any request. Questions, prompts, model, settings and scoring match the other arms; the bare and documented answers were not collected again. One request failed with HTTP 503 and was kept as an incorrect outcome, with no retry.
How much the intervals can tell you
The 40 questions are purposive and related, and every arm comes from one model run. The intervals describe resampling this question set; they do not establish how documentation would perform on other questions, data or models, or across model reruns.
Cost and acquisition
Known model cost was about US$0.08 for all 120 requests: US$0.06 for run 1 and US$0.02 for the allowed-values arm. One request has an unknown cost because it failed at the provider. Exact values:
| Requests | Known cost, US$ | Unknown-cost requests | Reserved, US$ | Input tokens | Output tokens |
|---|---|---|---|---|---|
| Run 1, bare and documented (80 requests) | 0.0592552 | 0 | 0.3652752 | 123258 | 6220 |
| Allowed-values arm (40 requests) | 0.0220276 | 1 | 0.1443176 | 42801 | 3067 |
Synthetic data
A fictional ledger: accounts, cards, categories and transaction events in CAD and USD, with partial refunds, paired transfers, uncategorized events, pending and void activity, zero-activity accounts and postings that cross a month. All timestamps are UTC. Daily account balances and daily card spend are built from these records and reconciled against an independent calculation. They describe ledger balances, not available funds, fraud risk or a production banking system.
| File | Rows |
|---|---|
| accounts.csv | 19 |
| cards.csv | 34 |
| categories.csv | 4 |
| transactions.csv | 625 |
Replay without an API key
All saved responses replay from the included caches. With Linux namespaces, bubblewrap and Python 3.12:
make check make reproduce
Prerequisites are in the README. There is no unsafe execution fallback.
READMERescored run 1Three-arm resultsOriginal run 1 resultsAll 120 outcomes as CSVRun 1 manifestAllowed-values protocolRun 1 protocol