Compare with LAG
Part of the Connected Data section of Coddy's PostgreSQL journey. Lesson 14 of 25.
LAG(value) reads a value from the preceding row in the window order. With PARTITION BY it stays inside each group. When no preceding row exists, the default result is NULL.
Input table orders:
id | customer_id | amount | ordered_on | status | meta |
|---|---|---|---|---|---|
11 | 1 | 20 | 2025-01-02 | paid | {"channel":"web","items":2,"shipping":{"city":"Haifa"}} |
12 | 1 | 30 | 2025-01-04 | paid | {"channel":"app","items":3,"shipping":{"city":"Haifa"}} |
13 | 2 | 30 | 2025-02-01 | pending | {"channel":"web","items":1} |
14 | 3 | 50 | 2025-02-03 | paid | {"channel":"app","items":5,"shipping":{"city":"Eilat"}} |
15 | 2 | 10 | 2025-02-03 | paid | {"channel":"web","items":2} |
SELECT id, amount, LAG(amount) OVER (ORDER BY id) AS previous_amount FROM orders ORDER BY id;The first order has no previous amount; order 12 reads 20 from order 11. LAG means the previous row in the chosen ordering, not the previous calendar day. Missing days do not create rows.
Use LAG for a previous-row comparison, with partitioning and order that match the question.
Challenge
MediumReturn id, customer_id and amount_change from the previous order of that customer in id order. Keep NULL for the first order of each customer. Display by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in Connected Data
3Window Calculations
Number Rows in GroupsRanks and TiesRunning TotalsCompare with LAGLatest Order per CustomerPractice on your own: PostgreSQL playground