SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…