SQL Query: Latest Order State in Last 1 Hour (Excluding Delivered)

Problem: Given an order-state-transitions table with columns (order_id, state, timestamp) where state moves through Created -> Confirmed -> Reached Merchant -> Reached Customer -> Delivered, write a query to fetch all order IDs with their latest state within the last 1 hour, excluding orders that are already Delivered.

Constraints: Each order can have multiple state-transition rows; you need only the most recent row per order within the time window.

Example schema: order_state_transitions(order_id, state, updated_at).

Approach:

SELECT t.order_id, t.state
FROM order_state_transitions t
JOIN (
  SELECT order_id, MAX(updated_at) AS max_ts
  FROM order_state_transitions
  WHERE updated_at >= NOW() - INTERVAL '1 hour'
  GROUP BY order_id
) latest ON t.order_id = latest.order_id AND t.updated_at = latest.max_ts
WHERE t.state != 'Delivered';

Alternatively use a window function: ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) filtered to rn=1, then exclude Delivered. At scale, this query needs an index on (order_id, updated_at) at minimum, and ideally a covering/composite index including state; a naive full scan with GROUP BY over all historical transitions degrades badly as the table grows, so partitioning the table by time range or maintaining a separate 'current_order_state' materialized table updated on each transition (avoiding a self-join over full history) is the production-grade answer for behavior at scale.