LAG and LEAD

SQL · Unit 13: Window functions

Full plan
SQL
9 exercises

Is Luke buying less each time? His orders were for 5, 2, and 1 units. To see that in a query you need, next to each order, the one before.

LAG pulls a value from the previous row. The column you want goes in its parentheses, and the window's ORDER BY says what "previous" means. With placed, id, it's the order that came in right before.

The first order has nothing before it, and LAG leaves a NULL there.

SELECT id, quantity,
  LAG(quantity) OVER (
    ORDER BY placed, id
  ) AS previous
FROM orders
WHERE customer_id = 2
ORDER BY placed, id;

Returns

id	quantity	previous
3	5	NULL
6	2	5
12	1	2

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Predict the output
Ann, customer 1, has placed orders 1, 2, and 7, one after the other, for 2, 1, and 1 units. What does this query return?
2. Complete the code
The warehouse wants to know whether each order that comes in is bigger or smaller than the one that came in right before. Fill in the subtraction for the first five orders, with the most recent on top.
3. Put the lines in order
LEAD isn't only for dates. When someone is looking at a product under 100, the store wants to suggest the next one up in price. Put in order the query that gives each of those products with its price and the name of the next most expensive one, in alphabetical order. Two lines don't belong.
4. Complete the code
When was each customer's previous purchase? Fill in the window for Valerie and Dylan, customers 5 and 6. "Previous" means the same person's previous purchase.
5. Predict the output
Orders 2, 3, and 4 belong to three different customers, Ann, Luke, and Mason, and are for 1, 5, and 10 units. Ann had already placed order 1, for 2 units. What does this query return?
6. Find the bug
Now the orders that grew, bigger than the same customer's previous one. There's only one, Valerie's order 8, which went from 1 unit to 3. The query returns seven. Which line has the problem?
7. Predict the output
Last lesson's summary by day, up to January 10. There were orders on the 3rd, 4th, 9th, and 10th, for 2, 6, 10, and 1 units. Someone adds a column and calls it more_than_yesterday. What does this query return?
8. Put the lines in order
The stock clerk arranges each shelf by price. For the products under 100, which are the Accessories and Audio ones, put in order the query that gives each one with its section and, next to it, the price of the one right below it in that same section, in alphabetical order. Two lines don't belong.
9. Write the code
Each order's change from the same customer's previous one. Write a query with four columns: first the customer_id, then the order's number, then its quantity, and last how many more units it had than that customer's previous order, negative if fewer. The previous one is the one with the earlier date and, within the same day, the lower number. Sort by customer and, within each one, from the most recent order to the oldest.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →