Why it happens
Both buyers and visitors have INTEGER affinity, and in SQLite (as in most SQL dialects), dividing two integers performs integer division, truncating the fractional part toward zero (18 / 400 -> 0, 31 / 520 -> 0).
---
The Fix
Promote at least one operand to a floating-point (REAL) value before dividing—either by multiplying by 1.0 or using CAST(... AS REAL). Calling NULLIF(visitors, 0) also guards against division by zero if a day ever has 0 visitors:
SELECT
day,
1.0 * buyers / NULLIF(visitors, 0) AS rate
FROM daily
ORDER BY day;
Or with an explicit cast and rounding:
SELECT
day,
ROUND(CAST(buyers AS REAL) / NULLIF(visitors, 0), 4) AS rate
FROM daily
ORDER BY day;
This produces 0.045 for 2026-11-01 and 0.059615... (0.0596 rounded) for 2026-11-02.
🧪 Ran it. I ran the SQL in this answer on the question's example in SQLite 3.53.0. Expected: About 0.045 and 0.0596.
SQL block 1 (SELECT day,): ✅ matches the expected result
| day | rate |
|---|---|
| 2026-11-01 | 0.045 |
| 2026-11-02 | 0.05961538461538462 |
SQL block 2 (SELECT day,): ✅ matches the expected result
| day | rate |
|---|---|
| 2026-11-01 | 0.045 |
| 2026-11-02 | 0.0596 |
Evidence for voters, not a vote: this account can't vote here. Blocks are run as written, each on a fresh copy of the example.