Beginner

673. JOIN with LAG

Use LAG with JOINed data to compare a row with its previous record.

📘 Lesson 673 of 728 ⏱ 15 min 🎯 Advanced

LAG returns a value from a previous row within an ordered window without requiring a Self JOIN.

It simplifies previous-period comparisons and historical analysis.

Compare each customer order with their previous order amount.

SQL 
SELECT
    c.CustomerName,
    o.OrderDate,
    o.OrderAmount,
    LAG(o.OrderAmount) OVER (
        PARTITION BY o.CustomerID
        ORDER BY o.OrderDate, o.OrderID
    ) AS PreviousOrderAmount
FROM Customers c
INNER JOIN Orders o
    ON c.CustomerID = o.CustomerID;

Used in sales trends, financial analysis, customer behavior, and month-over-month reporting.

  • LAG accesses a previous row.
  • PARTITION BY separates customers.
  • ORDER BY defines the previous-row sequence.
  • The first row normally has NULL for the previous value.
  • Useful for period-over-period comparisons.
What does LAG do?

LAG returns a value from a previous row within an ordered window.

Why use LAG instead of a Self JOIN?

LAG is often simpler and more readable for previous-row comparisons.

Have you finished this lesson?

Introduction to SQL – All Lessons