Pitfall

Integer division

7 / 93 is 0 when both sides are integers.

Write 100.0 * a / b, or CAST(a AS NUMERIC) / b. Postgres, Redshift, SQL Server and SQLite divide integers as integers (7 / 2 is 3); DuckDB, Snowflake, BigQuery and MySQL return 3.5. A query that is right on one can be wrong on the other: make the decimal explicit, and it is right everywhere. Rates and shares need decimals.

SELECT 7 / 2 AS on_duckdb,
       7 * 1.0 / 2 AS everywhere,
       100.0 * 149 / 2500 AS cancel_pct;

on_duckdb	everywhere	cancel_pct
3.5	3.5	5.96

on_duckdb

everywhere

cancel_pct

3.5

5.96

Try it in Randori

Practise:The cancellation rate