Your Percentage Says 0% and the Syntax Is Perfectly Fine

Conversion rate reads 0%. The data is right, the formula is right. SQL just thinks 3 divided by 4 is 0.

The problem

3 of 4 signups activated. The rate should be 75%. The query returns 0.

Why

In SQL, an integer divided by an integer yields an integer. The decimal part is truncated, not rounded. 3 / 4 is 0; 7 / 2 is 3.

PostgreSQL, SQL Server and MySQL all behave this way, and none of them warn you.

Three fixes

-- 1. Multiply by 100.0 before dividing (shortest)
SELECT 100.0 * activated / signups FROM stats;

-- 2. Explicit cast
SELECT CAST(activated AS DECIMAL) / signups FROM stats;

-- 3. PostgreSQL shorthand
SELECT activated::numeric / signups FROM stats;

Common mistakes

1. Casting after the division

-- Still 0: it divides first, then casts
SELECT CAST(3 / 4 AS DECIMAL);

-- Correct: cast first
SELECT CAST(3 AS DECIMAL) / 4;

2. Forgetting the denominator can be zero

-- A "division by zero" error kills the whole report
SELECT activated / signups FROM stats;

-- Safe
SELECT ROUND(100.0 * activated / NULLIF(signups, 0), 1) FROM stats;

To display 0 instead of a blank, wrap it in COALESCE(..., 0).

3. Rounding at the wrong point

-- Rounds each row then sums: errors accumulate
SUM(ROUND(price * quantity, 2))

-- Sum first, round last
ROUND(SUM(price * quantity), 2)

4. Confusing an overall rate with the average of rates

-- WRONG: every city weighted equally regardless of size
AVG(city_rate)

-- RIGHT: the rate across the whole population
100.0 * SUM(activated) / SUM(signups)

This is why summary reports stop matching detail reports, and it is very hard to spot.

Practice

• LeetCode 1211 — Queries Quality and Percentage
• LeetCode 1934 — Confirmation Rate
• LeetCode 262 — Trips and Users
• LeetCode 1174 — Immediate Food Delivery II
• HackerRank — The Blunder