LAG() in SQL

⏎ī¸ LAG() is a SQL window function that returns the value from a previous row within the same result set. It allows you to compare the current row with one or more preceding rows without using self-joins or complex subqueries.

📖 What is LAG()?

LAG() accesses data from a row that comes before the current row, based on the ordering defined in the OVER() clause. This makes it ideal for identifying changes, calculating differences, and performing time-series analysis.

Information

LAG() is commonly used for year-over-year comparisons, month-over-month analysis, tracking trends, and detecting changes between consecutive records.

đŸŽ¯ Why Use LAG()?

LAG() makes it easy to compare current values with previous values without writing complex SQL.

  • 📌 Compare current and previous rows.
  • 📌 Calculate growth or decline.
  • 📌 Detect changes over time.
  • 📌 Analyze trends and historical data.
  • 📌 Eliminate the need for self-joins.

📋 Sample Table

MonthSales
January10000
February12000
March11000
April15000

📝 Basic Syntax

LAG() Syntax

LAG(expression [, offset [, default_value]])
OVER (
    [PARTITION BY column_name]
    ORDER BY column_name
)
ArgumentDescription
expressionThe value to retrieve from a previous row.
offsetNumber of rows to look back. Default is 1.
default_valueReturned when no previous row exists.

💡 Example: Previous Month's Sales

Basic LAG() Example

SELECT
    Month,
    Sales,
    LAG(Sales) OVER (
        ORDER BY Month
    ) AS PreviousSales
FROM MonthlySales;

📊 Example Output

MonthSalesPreviousSales
January10000NULL
February1200010000
March1100012000
April1500011000

Remember

The first row has no previous row, so LAG() returns NULL by default.

💡 Example: Calculate Monthly Sales Difference

Compare Current and Previous Sales

SELECT
    Month,
    Sales,
    Sales - LAG(Sales) OVER (
        ORDER BY Month
    ) AS SalesDifference
FROM MonthlySales;

📊 Example Output

MonthSalesSalesDifference
January10000NULL
February120002000
March11000-1000
April150004000

💡 Example: Use a Custom Offset

Retrieve the value from two rows earlier.

LAG() with Offset

SELECT
    Month,
    Sales,
    LAG(Sales, 2) OVER (
        ORDER BY Month
    ) AS SalesTwoMonthsAgo
FROM MonthlySales;

💡 Example: Use a Default Value

Instead of returning NULL, return 0 when no previous row exists.

LAG() with Default Value

SELECT
    Month,
    Sales,
    LAG(Sales, 1, 0) OVER (
        ORDER BY Month
    ) AS PreviousSales
FROM MonthlySales;

💡 Example: Partition Data

Restart previous-row calculations for each department.

LAG() with PARTITION BY

SELECT
    Department,
    EmployeeName,
    Salary,
    LAG(Salary) OVER (
        PARTITION BY Department
        ORDER BY Salary
    ) AS PreviousSalary
FROM Employees;

⚖ī¸ LAG() vs LEAD()

FunctionAccesses
LAG()Previous row(s).
LEAD()Next row(s).

đŸ’ŧ Real-World Applications

  • 📈 Compare monthly or yearly sales.
  • 💹 Analyze stock price movements.
  • đŸĻ Compare account balances over time.
  • 🌡ī¸ Measure changes in weather data.
  • 📊 Build trend and growth reports.
  • đŸ“Ļ Compare inventory levels between reporting periods.

🗄ī¸ Database Compatibility

Database SystemLAG() Support
MySQL✅ Supported in MySQL 8.0 and later.
PostgreSQL✅ Fully supported.
SQL Server✅ Supported (SQL Server 2012 and later).
Oracle✅ Fully supported.
SQLite✅ Supported in SQLite 3.25.0 and later.

⚠ī¸ Common Mistakes

  • ❌ Omitting the ORDER BY clause inside OVER().
  • ❌ Assuming the first row always has a previous value.
  • ❌ Using the wrong offset.
  • ❌ Forgetting PARTITION BY when calculations should restart for each group.

Warning

The accuracy of LAG() depends on the ordering defined in the ORDER BY clause. If the ordering is incomplete or non-deterministic, the "previous" row may not be the one you expect.

⚠ī¸ Best Practices

Best Practice

Always use a deterministic ORDER BY, provide a default value when appropriate, use PARTITION BY to separate independent groups, and combine LAG() with arithmetic expressions to analyze trends and changes efficiently.

🚀 Key Points to Remember

  • 📌 LAG() retrieves values from previous rows.
  • 📌 It is a SQL window function.
  • 📌 The default offset is 1.
  • 📌 You can specify a custom offset and default value.
  • 📌 PARTITION BY restarts calculations within groups.
  • 📌 It is ideal for trend analysis, comparisons, and time-series reporting.
>>"LAG() lets you look back in your data without looking back at your SQL."

Summary

✅ LAG() is a powerful SQL window function that retrieves values from previous rows in a result set. It simplifies comparisons between consecutive records, supports trend analysis, and eliminates the need for complex self-joins, making it an essential tool for analytical SQL queries.