Case Study 1 — The Conversion Rate That Was Always Zero

One of the most common SQL bugs in the world, and one of the easiest to miss: integer division. A dashboard metric sat at 0% for months, and nobody questioned it — because 0% looked like a real, if disappointing, number.

Background

A growth team tracked "checkout conversion rate" — the fraction of carts that became completed orders. The data team materialized two integer columns per day: completed_carts and total_carts. The dashboard query computed the rate:

SELECT day,
       completed_carts / total_carts AS conversion_rate    -- ❌ both integers
FROM daily_cart_stats
ORDER BY day;

Every single day showed a conversion rate of 0. The team spent two weeks investigating their checkout funnel, convinced they had a catastrophic product problem — broken payment buttons, a checkout outage, something. There was nothing wrong with the product. The bug was in the query.

What went wrong

In SQL, integer / integer produces an integer, discarding any fractional part. A realistic day might have 1,200 completed carts out of 5,000 total — a 24% conversion rate. But:

   1200 / 5000   →   0     (integer division: 0.24 truncates to 0)

Because completed carts are always fewer than total carts, the ratio is always between 0 and 1, which integer division always truncates to 0. The metric wasn't measuring a bad funnel; it was structurally incapable of returning anything but zero. The "0% conversion" was an artifact of the data type, not the business.

The fix is to make at least one operand non-integer before dividing:

SELECT day,
       round(completed_carts::numeric / total_carts, 4) AS conversion_rate
FROM daily_cart_stats
ORDER BY day;

Casting completed_carts to numeric forces floating/decimal division, so 1200::numeric / 5000 = 0.24. (Equivalently: completed_carts * 1.0 / total_carts, or 100.0 * completed_carts / total_carts to get a percentage directly.) And because total_carts could in principle be zero on a quiet day, the robust version guards it:

SELECT day,
       round(100.0 * completed_carts / NULLIF(total_carts, 0), 2) AS conversion_pct
FROM daily_cart_stats
ORDER BY day;

NULLIF(total_carts, 0) turns a zero denominator into NULL (yielding a NULL rate for empty days) instead of a division-by-zero error.

The analysis

  1. Integer division silently truncates. a / b with two integers throws away the remainder. For any ratio of a smaller integer over a larger one — conversion rates, success rates, percentages, "share of total" — the result is always 0. This is perhaps the single most common numeric bug in SQL.

  2. A wrong-but-plausible number is the worst kind. 0% conversion looked like a real metric, so instead of suspecting the query, the team suspected the product and burned two weeks. If the bug had thrown an error, they'd have fixed it in minutes. Silent corruption is expensive precisely because it doesn't announce itself.

  3. Force decimal division explicitly. Cast one operand (::numeric), multiply by 1.0, or multiply by 100.0 for a percentage. Make the intent visible so a reviewer can see you meant fractional division.

  4. Guard denominators with NULLIF. Any real-world rate can have a zero denominator eventually (a day with no carts, a category with no products). x / NULLIF(y, 0) is the standard safe-division idiom.

  5. Sanity-check a metric against a hand calculation. Pick one day, compute the rate by hand (1200/5000 = 24%), and compare to the dashboard. A single spot-check would have exposed "0% every day" instantly. Never ship a metric you haven't reconciled against one manual calculation.

Discussion questions

  1. Explain precisely why every day's conversion rate came out as 0.
  2. Give three equivalent ways to force decimal division, and say which you find clearest.
  3. Why add NULLIF(total_carts, 0)? What happens without it on a zero-cart day?
  4. The team blamed the product for two weeks. What review habit would have caught a data-type bug before it reached the dashboard?
  5. ⭐ List three other common metrics (besides conversion rate) that would silently read 0 due to integer division.