lesson 39 Window functions · advanced

Compare rows with LAG

Compare each row with an earlier row.

the idea

LAG reads a value from a previous row within a window. The first row has no predecessor, so its result is NULL unless you supply a default.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  id,
  customer_id,
  LAG(quantity) OVER( PARTITION BY customer_id ORDER BY ordered_at,id ) AS previous_quantity
FROM orders;

Watch for: LAG uses the window ordering; it is not the previous row in storage.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Line up a customer's quantities. LAG looks at the previous row in the same partition. Sorting by time makes previous mean the prior order.

   step-1.sql Run

   sql›

   SELECT id, customer_id, quantity, ordered_at
   FROM orders
   WHERE customer_id = 1
   ORDER BY ordered_at, id;

2. 2

   Read the previous quantity. LAG(quantity) returns the prior order's quantity for that customer. The first order has NULL for previous.

   step-2.sql Run

   sql›

   SELECT
     id,
     customer_id,
     LAG(quantity) OVER( PARTITION BY customer_id ORDER BY ordered_at, id ) AS previous_qty
   FROM orders;