Interview Question Bank · NULLIF · divide-by-zero · conditional SUM
5. Return rate by category
MediumE-commerceQuestion #89
Concept: NULLIF · divide-by-zero · conditional SUMshow
Any ratio can hit a group whose denominator is 0, and Postgres stops the whole query with 'division by zero'. Wrap the divisor in NULLIF(divisor, 0): it turns 0 into NULL, and x / NULL is NULL, which is the honest answer for 'no rate'. Use SUM over a quantity column for unit rates; COUNT(*) counts rows, not units.
Example (different data):
SELECT region, ROUND(100.0 * SUM(refunded_amount) / NULLIF(SUM(paid_amount), 0), 1) AS refund_pct FROM payments GROUP BY region;
Your task
Quillmere's quality team compares how often each category's units come back. One category has never had a completed sale, and the report must not crash on it.
For every category that appears on an order line (any status), return category, units_sold (sum of quantity on completed orders, 0 if none), units_returned (sum of returned_qty on completed orders, 0 if none) and return_rate_pct (units_returned as a percentage of units_sold, rounded to 1 decimal; NULL when units_sold is 0). Order lines without a product are ignored. Sort by category.
Starting Postgres in your browser…
Checking your access…