Save up to $499! Grab all Python courses for $49 or all online courses we’ve ever launched for only $169. Only Feb 11-16. Happy Valentine's!
LEAD() and LAG()
7. Deltas


Good job! LEAD can be extremely useful when we want to calculate deltas, i.e. differences between two values. A typical example may look like this:

  LEAD(Clicks) OVER(ORDER BY Day ASC) AS Lead,
  Clicks - LEAD(Clicks) OVER(ORDER BY Day ASC) AS Difference
FROM Statistic
WHERE WebsiteId = 2;

The above query calculates day-to-day deltas: the last column shows the difference in clicks between the current day and the next day. From a business point of view, this could easily tell us a lot about the website: if the deltas for many rows are positive, and possibly increasing, then the website is expanding. If, in turn, the deltas are mostly negative, we can start to worry about the performance of the website.


For WebsiteId = 1, show each statistics row: Day, Revenue, Revenue on the next day (NextDayRevenue) and the Difference between these two values.

Stuck? Here's a hint!

Use LEAD(Revenue) and the ORDER BY clause twice.