LAG and LEAD compare a row with a previous or following row in an ordered window.
LAG and LEAD compare a row with a previous or following row in an ordered window. Read the query line by line and identify the input table, the transformation, and the shape of the result before running it.
SELECT order_id, order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS previous_amount FROM orders ORDER BY order_date;
Before running the example, predict the output columns and whether the query returns one row, many rows, or possibly no rows.
In your own words, explain the main SQL feature in today’s lesson and why it answers the question.
Recall prompt: Use LAG(amount) OVER (ORDER BY order_date). The first previous value is NULL. Close this page section mentally and explain the query from memory before proceeding.
Show each order's amount and the previous order's amount by date.
Use LAG(amount) OVER (ORDER BY order_date). The first previous value is NULL.