Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions in PostgreSQL

Introduction
LEAD and LAG
11. Function LAG(x, y)
FIRST_VALUE, LAST_VALUE, NTH_VALUE
Summary

Instruction

Nice! And, of course, there's also an analogous version of LAG(x,y) with two arguments:

SELECT
  name,
  opened,
  LAG(opened,2) OVER(ORDER BY opened)
FROM website;

Now, instead of the previous row, we show the value '2 rows before'.

Some people have difficulties remembering the difference between LEAD(...) and LAG(...). You may think about it in the following way: let's imagine that your ordered rows are people hiking in the mountains. The people (rows) that are FURTHER ahead are currently LEADing, so you get them with LEAD. The people (rows) BEHIND you are currently LAGging (making progress slower), so you call them with LAG.

Exercise

Show the statistics for the website with id = 3: day, revenue and the revenue 3 days before.

Stuck? Here's a hint!

Use LAG(revenue,3) and the right ORDER BY clause.