Orders by Fulfillment Stage

Classify each order: 'delivered' if delivered_at is set, else 'shipped' if shipped_at is set, else 'pending'. Return the stage and number of orders, ordered by stage.

Table schema

Orders(id INT PRIMARY KEY, ordered_at DATE, shipped_at DATE, delivered_at DATE)

Sample data

id | ordered_at | shipped_at | delivered_at
1 | 2026-05-01 | 2026-05-02 | 2026-05-05
2 | 2026-05-02 | 2026-05-06 | 2026-05-12
3 | 2026-05-03 | 2026-05-04 | NULL
4 | 2026-05-04 | NULL | NULL
5 | 2026-05-08 | NULL | NULL