LAG, LEAD and window frames

Previous, next and in-frame values.

UnderstandStudy an examplePractise

Understand the concept

LAG and LEAD retrieve previous or next values within a partition ordered by OVER. Add a key such as OrderId when timestamps tie. When a neighbour does not exist, the default result is NULL.

FIRST_VALUE and LAST_VALUE depend on frame boundaries. A default frame can make LAST_VALUE return the current frame’s end rather than the partition’s final row. For the latter, state ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Window ordering does not guarantee display ordering.

WHEN TO USE IT

Use it for previous orders and changes over time.

COMMON PITFALL

Assuming LAST_VALUE always means the partition’s last row.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT OrderId, OrderDate, LAG(OrderDate) OVER (ORDER BY OrderDate, OrderId) AS PreviousDate FROM lab.SalesOrder;

The first row has a NULL previous date.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Calculate days between each customer’s consecutive orders.

Show the solution and explanation

Worked solution

WITH P AS (SELECT CustomerId, OrderId, OrderDate, LAG(OrderDate) OVER (PARTITION BY CustomerId ORDER BY OrderDate, OrderId) AS PreviousDate FROM lab.SalesOrder) SELECT *, DATEDIFF(day, PreviousDate, OrderDate) AS DaysBetween FROM P;

The first order for each customer has a NULL gap. Zero would confuse it with a same-day repeat order.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.