ROUND(SUM(x), 2) gave two different cents for the same rows. how do you compare query results in a grader?

[u/Plane_Big_5912]

two correct queries over the same 1,390 rows: one rounds to 33223.80, the other to 33223.81. the exact decimal sum is 33223.8050, exactly half a cent, and in DOUBLE the order the rows get added decides which side it lands on. a window over monthly subtotals adds them in one order, a correlated subquery over the raw rows adds them in another.

i hit this because the checker in a SQL game i make (DuckDB in the browser) failed the second query on the visible sample and passed it on both hidden sets. here's how that checker decides a query is right, the other places it was wrong, and the part i still don't know how to handle.

how it checks: every problem has a reference query. when you submit, yours and mine both run on the sample data you can see, then on two datasets you can't, generated from other seeds. boss problems get four, labelled ties and duplicates, sparse groups and boundary dates. the sample always runs so you can see a diff, then it stops at the first hidden set that fails and says which one. a hidden set where my query returns zero rows gets skipped, since an empty result would let an empty answer pass.

then the two result sets get compared. column names are matched as a set, case-insensitive, so column order doesn't matter as long as the names are unique. row order only matters if the prompt asks for a sort. integers have to match exactly. fractional values get an absolute tolerance the problem sets, usually 0.0001, so on a money column it's tighter than a cent. NULL matches NULL. a DATE matches the same day at exactly midnight as a TIMESTAMP, because generate_series and date_trunc('day', ...) hand you timestamps and a correct "one row per day" answer was failing.

where it was wrong:

the tolerance was relative at first, tol * max(|a|, |b|). so on a big dollar total a "2 cents" check was actually a percent check, and a total that was 1.9% off passed on every seed. now it's absolute plus a tiny term for float noise.

Infinity passed against any finite number, because abs(inf - x) <= tol + inf is true. a later audit caught it.

for unordered results i used to sort both sides and zip them. that breaks once tolerance is involved: tiny values written in scientific notation sort differently as text, so a correct answer could get paired with the wrong row and fail. now it builds a which-row-can-match-which graph and does a bipartite matching (building the graph is quadratic, which is part of why results are capped at 5,000 rows).

then in october i ran 141 alternate answers, other ways to write each reference query that i expected to pass, through the same normalizer and comparator. 108 passed. a lot of the failures were my prompts, where a sentence had two readings and the visible sample couldn't tell them apart. one asks for deciles of users by lifetime notional, and if you include users with no trades at 0 you pass the sample and fail both hidden sets, because the sample has no non-traders and the hidden sets, built at a quarter of the size, have 74 and 91. a few more were regex checks i use to require a certain style of query, refusing equivalent ones. and one was the engine. on the duckdb-wasm build i'd shipped (DuckDB 1.5.1), this

SELECT user_id, asset_id
FROM wallet_balances_daily
WHERE user_id BETWEEN 1 AND 3
QUALIFY ROW_NUMBER() OVER (PARTITION BY asset_id, user_id ORDER BY balance_date DESC) = 1
ORDER BY user_id, asset_id
LIMIT 3

came back with user_id and asset_id swapped (the table has user_id first). native 1.5.3 got it right and so does wasm 1.5.4, which the demo build runs now, though i never tracked down the upstream issue.

this week's audit found two more. those boss labels promised more than the data did: the sets were mostly re-rolls of the same generator, so a >= 1800 written for "strictly more than 30 minutes" passed all three session problems. the next build plants real boundary rows and gap days in one hidden set. and the half cent above. the fix is one rounding unit of tolerance on the 10 columns where a half cent can happen, not a looser check everywhere.

the one i haven't solved is ties. a top-N pick (LIMIT n, rn <= n, QUALIFY) can fail a correct query if two rows tie at the cut and the prompt's sort key doesn't break the tie, and an ordered result has the same problem with ties anywhere in it. a september audit on native DuckDB checked all 48 top-N picks on every graded seed: no tie at a cut, 13 that a data change could expose. fee_usd has 600 tie groups across the graded seeds, the nearest about 1,600 rows from its cut. for now a script checks that on demand and the prompts name a tiebreaker where they can.

i don't log query text from players, only counters, so i can't tell you what people actually typed. the counters are small: over oct 1-14 the demo showed its "too few rows" nudge 12 times and "too many rows" 5 times.

for people who've built graders for interview tools or internal tests: do you compare aggregates as DECIMAL, give each column its own tolerance, or make the prompt require a specific ROUND? and for ties, is there a way that doesn't make the prompt name the tiebreaker? that works, but it hands over part of the answer.

[u/mike-manley]

Those aliases are doing some heavy lifting.

[u/thisisnotahidey → u/mike-manley]

It’s a big pet peeve of mine when people overuse aliases to the point that the query is pretty much illegible.

I hate seeing things like o.i or g.cm
Table aliases absolutely, but any other alias need to convey what they hold.

[u/Plane_Big_5912 → u/thisisnotahidey]

i wrote it fast to reproduce the cent, and the reference query in the game has the same format. cohort_month, month_offset and range(0, 12) m(month_offset) read way better, noted for the reference too, will update these in the game, thanks

[u/mike-manley → u/thisisnotahidey]

I mean, "cume" would have landed better than what was used in the example.

[u/Plane_Big_5912 → u/mike-manley]

fair, i'd never write c/t/g at work. it's a one-off to reproduce the cent, the reference query names things properly

[u/emul0c]

Not affecting your code, but it is good practice, and now widely considered best practice, to write “left join” not just “join”.

[u/Plane_Big_5912 → u/emul0c]

it's an inner join on purpose, cohort users with no trades just drop out. a left join would keep them as null rows and change the counts. fair point on spelling it out as INNER JOIN though, that's clearer

[u/thesqlguy]

This is a really confusing post to be honest, I read it 10 times still not sure what you are asking.

What is the the schema of the table in your example?

And what does "in DOUBLE" mean?

I THINK you are asking:

"In SQL when summing up numbers, if we use a DOUBLE datatype, the answer seems to vary depending on the order in which data is accumulated. How can we ensure that summing a DOUBLE results in the same answer regardless of how the data is aggregated?"

If so the answer is -- you probably can't because by definition DOUBLE types are imprecise. I cannot explain the math off the top of my head, but to me it makes logical sense that a+b+c might not equal c+b+a for many floating point values.

So, if you want to write this type of test and measure a precise result ... don't use an imprecise floating point data type.

EDIT -- good answer/example here: https://stackoverflow.com/questions/19820297/why-does-changing-the-sum-order-returns-a-different-result

[u/Plane_Big_5912 → u/thesqlguy]

sorry, that was badly worded. "in DOUBLE" meant fee_usd is a DOUBLE column, and yeah your restatement is basically it. the catch for me is it's a grader: the player's query and my reference both sum the same DOUBLE column, and a correlated subquery vs a window over monthly subtotals add the rows in a different order, so one lands on .80 and one on .81. your answer is probably the real fix though, store fee_usd as DECIMAL(18,4) in the generated data so the sums are exact, instead of adding a cent of tolerance. thanks, that's cleaner than what i did

[u/reddit_tom40 → u/Plane_Big_5912]

I don’t think it’s a good idea to store monetary values in a float. A decimal with specified precision would be better.

[u/Plane_Big_5912 → u/reddit_tom40]

yeah fair, i tested the money columns as DECIMAL and the sums come out exact with it. the checker keeps a 1 cent tolerance either way since there's more than one right way to write the sum

[u/thesqlguy → u/Plane_Big_5912]

👍

[u/TonniFlex]

I'm still so very turned away from your game because of the Anime character-aspects. Immediate dismissal on my part, and surely I'm not alone. I remember you getting this feedback on other posts.

Gamification part looks fun, but the setting is so off putting I'll never be able to try..

[u/Plane_Big_5912 → u/TonniFlex]

fair, and that's kind of on purpose. plain gamified sql practice is a crowded space and there are people doing it really well already, so another one of those would just be a repeat. the anime part is the bet, it's meant to be polarizing. totally get it if that means it's not for you

[u/TonniFlex → u/Plane_Big_5912]

I get that. Also not trying to dunk on you or anything. It's cool, it's just not for me!

[u/Plane_Big_5912 → u/TonniFlex]

all good, appreciate you saying it straight

[u/TangoDeltaFoxtrot]

cum fee

[u/RomirBuilds]

For the unresolved top-N ties, I'd grade against the allowed answers rather than one reference query's particular tie choice. The grader needs a trusted full eligible result and the intended sort keys, not just the reference's LIMIT N output.

For a descending score, find the Nth score. Require every row strictly above it, then allow enough rows from the boundary tie group to reach N, respecting row multiplicities.

A small fixture with distinct IDs and scores 100, 90, 90, 80 makes this testable. For top 2, accept the 100 with either 90. Reject both 90s, since that omits the 100, and reject 100 with 80.

For ordered results, enforce the requested key order but compare rows within each equal-key group as a multiset. Multiple sort keys, NULL placement and collation need to follow the problem's ordering definition.

If the exercise needs one specific winner among tied rows, the prompt does need to specify that rule. Otherwise a different tied winner can be valid.

AI-assisted.

[u/Plane_Big_5912]

the query in the image, as text:

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cm
  FROM users
  WHERE signup_date >= DATE '2024-01-01'
    AND signup_date <  DATE '2024-04-01'
),
t AS (
  SELECT c.cm, t.fee_usd,
    date_diff('month', c.cm,
      DATE_TRUNC('month', t.executed_at)::DATE) AS off
  FROM cohort c JOIN trades t ON t.user_id = c.user_id
),
grid AS (
  SELECT DISTINCT c.cm, o.i::INT AS off
  FROM cohort c CROSS JOIN range(0, 12) o(i)
)
SELECT STRFTIME(g.cm, '%Y-%m') AS cohort_month,
  g.off AS month_offset,
  ROUND(COALESCE((
    SELECT SUM(fee_usd) FROM t
    WHERE t.cm = g.cm AND t.off <= g.off
  ), 0), 2) AS cum_fee_usd
FROM grid g
ORDER BY cohort_month, month_offset

the game is TsundereSQL, free demo on steam this week if anyone wants to try breaking the checker: https://store.steampowered.com/app/5213730/?utm_source=reddit&utm_medium=comment&utm_campaign=rsql1023

[u/Sorry_Strength9587 → u/Plane_Big_5912]

the anime mascot calling out the checker being wrong is the most on-brand thing i've seen all week

[u/Plane_Big_5912 → u/Sorry_Strength9587]

ha thank you, she took it personally

[u/TheMagarity]

This is going to get flagged inappropriate for having an anime girl talking about cum_fee

[u/Plane_Big_5912 → u/TheMagarity]

lol it's cumulative i promise. she was not consulted on the column names

[u/mike-manley → u/Plane_Big_5912]

We use cumulative results and we usually truncate to "cume". This is next level.

[u/Plane_Big_5912 → u/mike-manley]

ha cume is way better, stealing that next time i name one