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

Instruction

Fantastic! There is also another version of LEAD. It takes two arguments: LEAD(x,y). x remains the same – it specifies the column to return. y, in turn, is a number which defines the number of rows forward from the current value. For instance:

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

This form of LEAD won't show the webpage with the opening date coming immediately after the current opening date. Instead, it will show the opening date 2 rows forward – the 1st row will show the 3rd date etc.

Exercise

Take the statistics for the website with id = 2 between 1 and 14 May 2016 and show the day, the number of users and the number of users 7 days later.

Note that the last 7 rows don't have a value in the last column, because no rows '7 days from now' can be found for them.

Stuck? Here's a hint!

Use LEAD(users,7) and the right ORDER BY clause.

Console

Code editor

Result

TableConsole