Level 4 · Return rates that are all whole numbers

Six categories, six whole numbers. Real return rates are never that tidy. The query is fine everywhere except one character.

Integer division in different databases

Dividing two integers means different things depending on where the query runs:

• Postgres, SQL Server, Redshift, SQLite: / between two integers is integer division. 402 / 5824 is 0.
• DuckDB: / always returns a decimal (402 / 5824 is 0.069...), and // is the integer division operator.
• MySQL, BigQuery, Snowflake: / returns a decimal.

COUNT(*) is an integer everywhere. So a Postgres query written as COUNT(r.return_id) * 100 / COUNT(*) produced whole percents there, and a migration tool that faithfully kept that behavior in DuckDB wrote //. The bug was already in the original report.

What the starter returns

402 * 100 // 5824 is 6 for Textiles, and 103 * 100 // 1616 is also 6 for Furniture. The real rates are 6.90 and 6.37. Had the multiplication come after the division, 402 // 5824 * 100, every category would show 0.

The fix

Make one operand decimal before dividing:

ROUND(100.0 * COUNT(r.return_id) / COUNT(*), 2) AS return_rate_pct

100.0 is a decimal literal, so the whole expression is evaluated in decimals from the left. This version gives the same answer in every database above, which is why analysts write it this way even in DuckDB. CAST(COUNT(*) AS DOUBLE) works too, but is longer.

Rounding too early

The other way to lose precision is rounding before scaling: ROUND(402 / 5824, 2) * 100 turns 0.069 into 0.07 and then into 7, and Textiles reads 7% instead of 6.90%. Round once, at the end, to the precision the report shows.

Common mistakes

• COUNT(r.return_id) / COUNT(*) * 1.0: in Postgres or SQL Server the integer division already happened; multiplying afterward is too late.
• COUNT(*) for returned lines: after a LEFT JOIN it counts every line. Count a column from returns.
• An inner join to returns: it drops the lines that were never returned, so every rate becomes 100%.

In SQL Server

This is the version most likely to bite you there: / on two INTs truncates.

SELECT 402 / 5824;           -- 0
SELECT 100 * 402 / 5824;     -- 6
SELECT 100.0 * 402 / 5824;   -- 6.902472...

Write 100.0 * first, exactly as here.

On the job

Rates of exactly 0, 100 or whole numbers are a red flag in any report. Check the division before you check the data.