LEAD() and LAG()
FIRST_VALUE(), LAST_VALUE()
Instruction
Nice! And, of course, there's also an corresponding version of LAG(x, y) with two arguments:
SELECT Name, OpenDate, LAG(OpenDate,2) OVER(ORDER BY OpenDate ASC) AS Lag 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 can think about it this way: 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 (coming behind), so you call them with LAG().
Exercise
Show the following statistics for the website with Id = 3: Day, Revenue, and the Revenue 3 days before. Name the last column Lag.
Stuck? Here's a hint!
Use LAG(Revenue,3) and the right ORDER BY clause.
