LEAD() and LAG()
FIRST_VALUE(), LAST_VALUE()
Instruction
Perfect! Of course, there's also a function that shows a previous value, and its name is LAG(x):
SELECT OpenDate, Name, LAG(Name) OVER(ORDER BY OpenDate ASC) AS Lag FROM Website;
Now, instead of showing the next opening date, we show the previous opening date. Take a look:
Note that you can always sort the rows in reverse order with DESC and use LEAD(...) instead of LAG(...), or the other way around. In other words:
LEAD(...) OVER(ORDER BY ...)
is the same as
LAG(...) OVER(ORDER BY ... DESC)
and
LEAD(...) OVER(ORDER BY ... DESC)
is the same as
LAG(...) OVER (ORDER BY ...)
Exercise
For the website with Id = 3, show the Day, the number of clicks that day, and the number of clicks on the previous day (PreviousDayClicks).
Note that there won't be any previous value for the first row.
Stuck? Here's a hint!
Use LAG(Clicks) and the right ORDER BY clause.
