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

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.

Console

Code editor

Result

TableConsole