Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Lead-Lag Navigation

Lead-Lag Navigation

Learn how to use SQL’s Lead-lag navigation pattern to compare values across rows and analyze trends over time.

We'll cover the following...

To improve customer satisfaction and inventory planning, let us say we want to analyze how each customer's purchase amount has changed over time.

Did their spending increase from one order to the next? Did it drop? For this, we need to compare values across different rows, specifically, a current row with its previous or next row in a sequence. This is where the lead-lag navigation pattern becomes essential.

In this lesson, we’ll learn how to compare rows using the LEAD() and LAG() window functions. By the end, we’ll be able to calculate changes, detect trends, and navigate ordered data effectively.

We’ll learn how to:

  • Understand the use of LEAD() and LAG() in SQL.

  • Apply this pattern to analyze changes between consecutive records.

  • Use it to compare rows across time, transactions, or any ordered dimension.

  • Build insight-driving queries using the OnlineStore database.

Pattern overview

Category:

  • Sequencing & Hierarchical Patterns

Intent:

To access data from the previous or next row within a defined sequence.

Motivation:

Many analytical questions require comparing current values to prior or future ones, such as tracking changes in prices, purchase behavior, or shipping performance. Without LEAD() or LAG(), these comparisons often require complex self-joins, which are harder to read and maintain.

Also known as:

  • Row-to-row comparison

  • Temporal shift

  • Row navigation

Structure

SELECT column1,
       column2,
       LAG(column_to_compare) OVER (PARTITION BY group_column ORDER BY order_column) AS PreviousValue,
       LEAD(column_to_compare) OVER (PARTITION BY group_column ORDER BY order_column) AS NextValue
FROM   table_name;
Syntax for Lead-lag navigation pattern

Keywords

LAG, LEAD, OVER, PARTITION BY, ORDER BY, window functions

Problem structure

We use the Lead-Lag Navigation pattern when:

  • We need to compare values across rows within the same group or sequence.

  • We want to look ahead or behind to calculate differences or observe changes over time.

  • We answer questions like:

    • “What’s the difference between this and the previous transaction?”

    • “Did the customer spend more or less than before?”

    • “What’s the next item in a sequence?”

    • “How many days passed between consecutive purchases?”

  • Look for keywords like: “previous value,” “next value,” “difference between rows,” “consecutive events,” or “row-to-row comparison” to identify when this pattern applies.

Example use case

Given the following table structure of Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table, referring to the customer who placed the order. The table contains information about the orders placed. Let's compare how much each customer spent on each order and calculate the difference from their previous order. Write an SQL query to list each customer's orders along with the total amount of the previous order and the change in amount compared to that previous order, ordered by OrderDate.

Press + to interact
SELECT   CustomerID,
         OrderID,
         OrderDate,
         TotalAmount,
         LAG(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS PreviousAmount,
         TotalAmount - LAG(TotalAmount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS ChangeFromPrevious
FROM     Orders
ORDER BY CustomerID, OrderDate;

Explanation:

  • ...