Menu
CoddyTech

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:

idcustomer_idamountordered_onstatusmeta
111202025-01-02paid{"channel":"web","items":2,"shipping":{"city":"Haifa"}}
121302025-01-04paid{"channel":"app","items":3,"shipping":{"city":"Haifa"}}
132302025-02-01pending{"channel":"web","items":1}
143502025-02-03paid{"channel":"app","items":5,"shipping":{"city":"Eilat"}}
152102025-02-03paid{"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 icon

Challenge

Medium

Return 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.
quiz iconTest yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Connected Data

Practice on your own: PostgreSQL playground