Lesson 34 / 37 · Windows

LAG reads the previous row

Within each customer, ordered by date, lag() fetches the previous row's amount. The first order of every customer has no previous row, so it gets NULL.

The query

SELECT id, customer_id, order_date, amount,
       lag(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_amount,
       amount - lag(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS change
FROM orders
WHERE amount IS NOT NULL
ORDER BY customer_id, order_date

Try lead(amount): it reads the next row instead, so the last order of each customer gets NULL.

Next: Ranking, and filtering on it with QUALIFY · All lessons