LAG and LEAD replace a self join on the same table

Comparing a row with the previous one — time between orders, change since the last reading — was a self join on a correlated subquery finding the preceding key, which reads the table twice and is slow.

SELECT
  placed_at,
  total,
  LAG(total)     OVER w AS previous_total,
  total - LAG(total, 1, 0) OVER w AS delta,
  LEAD(placed_at) OVER w AS next_order_at
FROM orders
WHERE customer_id = 4471
WINDOW w AS (ORDER BY placed_at);

The third argument is the default for when there is no previous row, which saves a COALESCE wrapping the whole expression. Both take an offset, so LAG(total, 7) compares with a week ago in a daily series without any date arithmetic. This is the window function that most often replaces application code rather than SQL — the loop in PHP that kept the previous row in a variable disappears entirely.