Latest Order State in the Last Hour

Problem Given an order-state-transitions table where an order moves through Created → Confirmed → Reached Merchant → Reached Customer → Delivered, write a query that fetches every order ID with its latest state within the last 1 hour, excluding orders that are already Delivered.

Schema

• order_state_transitions(order_id, state, updated_at) — append-only; each order accumulates one row per transition, so an order has many rows

Required output

• One row per order: order_id and its most recent state, restricted to orders with activity in the last hour, with Delivered orders filtered out

Example

• Order 7: Created 10:02, Confirmed 10:05, Reached Merchant 10:20 → included, state = Reached Merchant
• Order 9: Created 10:01, …, Delivered 10:30 → excluded, since its latest state is Delivered