Interview Drills · Conditional aggregation · FILTER
36. Refund rate per customer
Concept: Conditional aggregation · FILTERshow
Conditional aggregation counts only some rows inside a group: COUNT(*) FILTER (WHERE status = 'x') in Postgres, or the portable SUM(CASE WHEN status = 'x' THEN 1 ELSE 0 END). Watch out for integer division: COUNT returns an integer, so 1 / 3 is 0. Multiply by 100.0 first.
Example (different data):
SELECT store_id, ROUND(100.0 * COUNT(*) FILTER (WHERE late) / COUNT(*), 2) AS late_pct FROM deliveries GROUP BY store_id;
Your task
Classic interview question: the risk team wants each customer's refund rate to spot serial refunders.
For every customer who has placed an order, return customer_id, orders (all their orders, any status), refunds (orders with status 'refunded') and refund_rate (refunds as a percentage of orders, rounded to 2 decimals). Sort by refund_rate (highest first), then customer_id.
Starting Postgres in your browser…
Checking your access…