Caso Abierto Español

Field manual · SQL traps

A rate needs a denominator

A raw count alone rewards whoever shows up most; a rate with no minimum sample rewards whoever shows up least. You need both at once.

Where it shows up

Any ranking of "the best", "the most suspicious" or "the worst store": if the result is a proportion, the first question is what it's being divided by.

Example

Wrong: a count presented as if it were a rate

SELECT store, count(*) AS refunds
FROM refunds
GROUP BY store
ORDER BY refunds DESC;
-- the store with the most sales looks the worst, even if it refunds less than anyone

Right: refunds over that store's total orders

SELECT o.store,
       count(r.order_id) * 1.0 / count(o.order_id) AS refund_rate,
       count(o.order_id) AS orders
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.order_id
GROUP BY o.store
HAVING count(o.order_id) >= 30   -- without this, a store with 2 orders and
                                  -- 1 refund "wins" at 50%
ORDER BY refund_rate DESC;

The count points at whoever sells the most; a rate with no minimum points at whoever sells the least, because with few orders any extreme proportion is easy to hit. A HAVING count(*) >= N throws out samples too small for the rate to mean anything, and it's worth always comparing against the overall rate: if 5% of all orders get refunded, 12 out of 80 (15%) stands out; 1 out of 2 says nothing, even though it's "50%".