Courier scorecard with conditional counts

A logistics lead wants a one-row-per-courier scorecard instead of a long list of parcel statuses.

From parcel_events, return courier, delivered, returned, in_transit (the count of parcels in each status) and delivery_rate_pct (delivered parcels as a percentage of all the courier's parcels, 1 decimal). Order by delivery_rate_pct descending, then courier.

1. Step 1: Conditional aggregation: a `CASE` inside `SUM` counts only the rows that match, turning statuses into columns.
2. Step 2: Multiply by `100.0` (not `100`) before dividing, otherwise PostgreSQL performs integer division and truncates the rate.
3. Step 3: `COUNT(*)` per courier is the denominator for the delivery rate.

SELECT courier,
       SUM(CASE WHEN status = 'DELIVERED'  THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'RETURNED'   THEN 1 ELSE 0 END) AS returned,
       SUM(CASE WHEN status = 'IN_TRANSIT' THEN 1 ELSE 0 END) AS in_transit,
       ROUND(100.0 * SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END)
             / COUNT(*), 1)                                   AS delivery_rate_pct
FROM parcel_events
GROUP BY courier
ORDER BY delivery_rate_pct DESC, courier;