Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Window Functions

Introduction
LEAD and LAG
7. Deltas
FIRST_VALUE, LAST_VALUE, NTH_VALUE
Summary

Instruction

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:

SELECT
  day,
  clicks,
  LEAD(clicks) OVER(ORDER BY day),
  LEAD(clicks) OVER(ORDER BY day) - clicks AS difference
FROM statistics
WHERE website_id = 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.

Exercise

For website_id = 1, show each statistics row: day, revenue, revenue on the next day and the difference between these two values (as next day's minus that day's revenue).

Stuck? Here's a hint!

Use LEAD(revenue) and the right ORDER BY clause twice.