Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
LEAD and LAG
8. Function LEAD(x, y)
FIRST_VALUE, LAST_VALUE, NTH_VALUE
Summary

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.