LAG function for previous row access in SQL - Deep Dive

Overview - LAG function for previous row access

What is it?

The LAG function in SQL lets you look at data from a previous row in the same result set without needing to join the table to itself. It helps you compare current row values with earlier rows easily. This function is often used in reports and analytics to find differences or trends over time. It works by moving backward a specified number of rows within a group or ordered set.

Why it matters

Without the LAG function, comparing a row to its previous row would require complex self-joins or subqueries, which are harder to write and slower to run. LAG simplifies these comparisons, making queries cleaner and faster. This helps businesses quickly spot changes, trends, or anomalies in data like sales, stock prices, or user activity. Without it, analyzing sequences or time series data would be much more difficult and error-prone.

Where it fits

Before learning LAG, you should understand basic SQL SELECT queries, ORDER BY clauses, and window functions. After mastering LAG, you can explore other window functions like LEAD, FIRST_VALUE, and aggregate window functions. This knowledge fits into the broader topic of advanced SQL analytics and reporting.

Mental Model

Core Idea

LAG lets you peek at the value from a previous row in your ordered data without changing the table structure.

Think of it like...

Imagine reading a book and wanting to remember what happened in the previous chapter without flipping back manually. LAG is like a bookmark that automatically shows you the last chapter's key point while you read the current one.

┌───────────────┐
│ Current Row   │
│ Value: 100   │
│ Previous Row │
│ Value: 90    │  <-- LAG function fetches this
└───────────────┘

Data ordered by date:
Date       | Value
-----------|-------
2024-01-01 | 90
2024-01-02 | 100  <-- Current row

LAG(Value) over (ORDER BY Date) = 90

Build-Up - 7 Steps

1

Foundation

Understanding row order in SQL

Concept: SQL tables are unordered by default, but you can order rows using ORDER BY to create a sequence.

When you run a SELECT query, the rows can come in any order unless you specify ORDER BY. For example, SELECT * FROM sales ORDER BY date ASC will list sales from earliest to latest. This order is important because LAG works based on the order you define.

Result

Rows are arranged in a clear sequence, which LAG can then use to look backward.

Knowing that SQL rows have no natural order without ORDER BY is key to using LAG correctly, as it depends on a defined sequence.

2

Foundation

Introduction to window functions

Concept: Window functions perform calculations across a set of rows related to the current row without collapsing the result into fewer rows.

Unlike aggregate functions that group rows, window functions like ROW_NUMBER(), RANK(), and LAG() let you calculate values across rows while keeping each row visible. You define a window using OVER() with optional PARTITION BY and ORDER BY clauses.

Result

You can compute running totals, ranks, or previous values while still seeing all rows.

Understanding window functions is essential because LAG is one of them, enabling row-by-row comparisons within a group.

3

Intermediate

Basic usage of LAG function

🤔Before reading on: do you think LAG returns the previous row's value by default or do you need to specify how many rows back?

Concept: LAG returns the value from a previous row based on the order you specify, defaulting to one row back.

Syntax: LAG(column, offset, default) OVER (ORDER BY column) - column: the value to fetch from previous row - offset: how many rows back (default 1) - default: value if no previous row exists (optional) Example: SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales FROM sales_data; This shows sales and the previous day's sales side by side.

Result

Each row shows its own sales and the sales from the previous date, or NULL if none.

Knowing the default offset is 1 helps you quickly compare each row to the immediate previous one without extra parameters.

4

Intermediate

Using LAG with PARTITION BY

🤔Before reading on: do you think LAG looks at previous rows across the entire table or only within partitions?

Concept: PARTITION BY divides data into groups, and LAG works within each group separately.

Example: SELECT department, date, sales, LAG(sales) OVER (PARTITION BY department ORDER BY date) AS prev_sales FROM sales_data; This compares sales to the previous date's sales only within the same department, not across departments.

Result

Previous sales values are shown per department, resetting at each new department.

Understanding partitioning prevents mixing unrelated rows and ensures meaningful comparisons within groups.

5

Intermediate

Handling NULLs and defaults in LAG

Concept: You can specify a default value to return when there is no previous row, avoiding NULL results.

Syntax example: LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_sales This returns 0 instead of NULL for the first row where no previous row exists. This is useful for calculations that can't handle NULLs easily.

Result

No NULLs appear; first row shows 0 as previous sales.

Providing defaults makes your queries more robust and easier to use in further calculations.

6

Advanced

Combining LAG with other window functions

🤔Before reading on: do you think you can use LAG together with aggregates like SUM or ROW_NUMBER in one query?

Concept: LAG can be combined with other window functions to create complex analytics in a single query.

Example: SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales, SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM sales_data; This shows previous sales and a running total up to the current row.

Result

Each row shows sales, previous sales, and cumulative sales so far.

Combining window functions unlocks powerful insights without multiple queries or joins.

7

Expert

Performance considerations and indexing

🤔Before reading on: do you think LAG queries always run fast regardless of table size and indexing?

Concept: LAG performance depends on how the database orders and partitions data; proper indexing can speed up these operations.

When using LAG with ORDER BY and PARTITION BY, the database sorts data internally. If the columns used for ordering and partitioning are indexed, the query runs faster. Without indexes, large tables may cause slow queries due to sorting overhead. Example: Index on (department, date) helps LAG with PARTITION BY department ORDER BY date. Also, some databases optimize window functions differently; understanding your DBMS helps tune performance.

Result

Queries with proper indexes run efficiently even on large datasets.

Knowing how LAG interacts with indexing helps write scalable queries for production systems.

Under the Hood

LAG is a window function that accesses the value from a previous row within a defined window frame. Internally, the database engine orders the rows according to the ORDER BY clause and partitions them if specified. It then keeps track of the current row's position and fetches the value from the row at the specified offset behind it. This is done without joining tables, by using internal row pointers or offsets in the sorted data. If no previous row exists, it returns NULL or a default value.

Why designed this way?

LAG was designed to simplify comparisons between rows in ordered data without expensive self-joins or subqueries. Before window functions, comparing rows required complex queries that were hard to write and slow to execute. Window functions like LAG provide a declarative, efficient way to access related rows in the same query result, improving readability and performance.

┌───────────────────────────────┐
│ Input Table (unordered rows)  │
└──────────────┬────────────────┘
               │ ORDER BY
               ▼
┌───────────────────────────────┐
│ Ordered Rows by date           │
│ Row 1: sales=90               │
│ Row 2: sales=100              │
│ Row 3: sales=110              │
└──────────────┬────────────────┘
               │ LAG(sales,1)
               ▼
┌───────────────────────────────┐
│ Output with LAG column         │
│ Row 1: sales=90, lag=NULL     │
│ Row 2: sales=100, lag=90      │
│ Row 3: sales=110, lag=100     │
└───────────────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LAG return the previous row's value regardless of ordering? Commit to yes or no.

Common Belief: LAG always returns the previous row's value in the table as stored.

Reality: LAG returns the previous row based on the ORDER BY clause inside the OVER() window, not the physical table order.

Why it matters: Without specifying ORDER BY, LAG results are unpredictable, leading to wrong comparisons and analysis.

Quick: If you don't specify a default value in LAG, does it return zero or NULL for the first row? Commit to your answer.

Common Belief: LAG returns zero or some default number automatically for missing previous rows.

Reality: LAG returns NULL by default when there is no previous row unless you specify a default value explicitly.

Why it matters: Assuming zero instead of NULL can cause incorrect calculations or misinterpretations in reports.

Quick: Does LAG work across partitions even if PARTITION BY is used? Commit to yes or no.

Common Belief: LAG ignores PARTITION BY and looks at previous rows across the entire dataset.

Reality: LAG respects PARTITION BY and only looks at previous rows within the same partition group.

Why it matters: Ignoring partitioning can cause mixing unrelated data, leading to invalid comparisons.

Quick: Can LAG be used to look forward to the next row? Commit to yes or no.

Common Belief: LAG can be used to look at any row, including future rows.

Reality: LAG only looks backward; to look forward, you use the LEAD function.

Why it matters: Confusing LAG and LEAD leads to wrong query logic and results.

Expert Zone

1

LAG's behavior can differ subtly between SQL dialects, especially in how default values and offsets are handled, requiring careful testing.

2

When used with large partitions, LAG can cause memory pressure because the database must keep track of many rows in the window frame.

3

Combining LAG with ROWS or RANGE framing clauses can change which rows are considered previous, affecting results in subtle ways.

When NOT to use

Avoid LAG when you need to compare rows across unrelated groups or when the data is not naturally ordered. In such cases, self-joins or correlated subqueries might be more appropriate. Also, if your database does not support window functions, you must use older methods like self-joins.

Production Patterns

In production, LAG is often used for time series analysis like calculating daily changes, detecting anomalies, or computing moving differences. It is combined with PARTITION BY to analyze data per user, region, or category. Also, it is used in financial systems to compare current and previous balances or prices efficiently.

Connections

Time Series Analysis

LAG provides a way to compare sequential data points over time, a core operation in time series analysis.

Understanding LAG helps grasp how analysts detect trends and changes in time-based data without complex joins.

Functional Programming - Immutable Data

LAG conceptually resembles accessing previous states in a sequence without modifying data, similar to immutable data structures in functional programming.

Knowing this connection clarifies how LAG provides read-only access to past values, preserving original data integrity.

Version Control Systems

LAG is like viewing the previous commit or version in a sequence, enabling comparison between current and past states.

This analogy helps understand how LAG supports change detection by referencing prior snapshots in a sequence.

Common Pitfalls

#1 Using LAG without ORDER BY causes unpredictable results.

Wrong approach:

SELECT date, sales, LAG(sales) OVER () AS prev_sales FROM sales_data;

Correct approach:

SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales FROM sales_data;

Root cause:

Not specifying ORDER BY means the database does not know the row sequence, so LAG cannot reliably find the previous row.

#2 Assuming LAG returns zero instead of NULL for the first row.

Wrong approach:

SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales FROM sales_data; -- expecting 0 for first row

Correct approach:

SELECT date, sales, LAG(sales, 1, 0) OVER (ORDER BY date) AS prev_sales FROM sales_data;

Root cause:

By default, LAG returns NULL when no previous row exists; a default value must be explicitly set.

#3 Using LAG without PARTITION BY when data has groups.

Wrong approach:

SELECT department, date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales FROM sales_data;

Correct approach:

SELECT department, date, sales, LAG(sales) OVER (PARTITION BY department ORDER BY date) AS prev_sales FROM sales_data;

Root cause:

Without partitioning, LAG compares rows across different groups, mixing unrelated data.

Key Takeaways

LAG is a window function that lets you access data from previous rows in a defined order without joins.

Ordering rows with ORDER BY is essential for LAG to work predictably and meaningfully.

PARTITION BY limits LAG's look-back to within groups, preventing mixing unrelated rows.

By default, LAG returns NULL when no previous row exists, but you can specify a default value.

Proper indexing on partition and order columns improves LAG query performance on large datasets.

Practice

1. What does the SQL LAG function do?

easy

A. Returns the value from the next row in the result set

B. Calculates the sum of all previous rows

C. Returns the value from the previous row in the result set

D. Filters rows based on a condition

Solution

1. Step 1: Understand the purpose of LAG

   The LAG function is used to access data from a previous row relative to the current row in a query result.

2. Step 2: Compare options with definition

   Returns the value from the previous row in the result set correctly states that LAG returns the previous row's value. Other options describe different functions or behaviors.

3. Final Answer:

   Returns the value from the previous row in the result set -> Option C

4. Quick Check:

   LAG = previous row value [OK]

Hint: LAG looks backward one row by default [OK]

Common Mistakes:

• Confusing LAG with LEAD (next row)
• Thinking LAG sums values
• Assuming LAG filters rows

2. Which of the following is the correct syntax to use LAG to get the previous salary ordered by employee ID?

easy

A. LAG(salary) OVER (ORDER BY employee_id)

B. LAG(salary) BY employee_id

C. LAG(salary) ORDER employee_id

D. LAG(salary) PARTITION BY employee_id

Solution

1. Step 1: Recall LAG syntax

   LAG requires an OVER clause with ORDER BY to define the row order for previous row access.

2. Step 2: Match syntax with options

   LAG(salary) OVER (ORDER BY employee_id) correctly uses OVER (ORDER BY employee_id). The other options misuse syntax or keywords.

3. Final Answer:

   LAG(salary) OVER (ORDER BY employee_id) -> Option A

4. Quick Check:

   LAG syntax = LAG() OVER (ORDER BY ...) [OK]

Hint: Always use OVER with ORDER BY for LAG [OK]

Common Mistakes:

• Omitting OVER clause
• Using PARTITION BY without ORDER BY
• Incorrect keyword order

3. Given the table Employees with columns id and salary, what will this query return?

SELECT id, salary, LAG(salary) OVER (ORDER BY id) AS prev_salary FROM Employees;

Assuming data:
id | salary
1 | 5000
2 | 6000
3 | 5500

medium

A. [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:5000}, {id:3, salary:5500, prev_salary:6000}]

B. [{id:1, salary:5000, prev_salary:5000}, {id:2, salary:6000, prev_salary:6000}, {id:3, salary:5500, prev_salary:5500}]

C. [{id:1, salary:5000, prev_salary:6000}, {id:2, salary:6000, prev_salary:5500}, {id:3, salary:5500, prev_salary:NULL}]

D. [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:NULL}, {id:3, salary:5500, prev_salary:NULL}]

Solution

1. Step 1: Understand LAG with ORDER BY id

   LAG(salary) returns the salary from the previous row ordered by id. For id=1, no previous row exists, so NULL is returned.

2. Step 2: Apply LAG to each row

   Row 1: prev_salary = NULL
   Row 2: prev_salary = salary of id=1 = 5000
   Row 3: prev_salary = salary of id=2 = 6000

3. Final Answer:

   [{id:1, salary:5000, prev_salary:NULL}, {id:2, salary:6000, prev_salary:5000}, {id:3, salary:5500, prev_salary:6000}] -> Option A

4. Quick Check:

   LAG returns previous row's value or NULL if none [OK]

Hint: First row prev value is always NULL [OK]

Common Mistakes:

• Assuming first row has previous value
• Mixing up current and previous rows
• Ignoring ORDER BY effect

4. Identify the error in this query:

SELECT id, salary, LAG(salary) ORDER BY id AS prev_salary FROM Employees;

medium

A. AS alias must come before LAG

B. ORDER BY cannot be used inside LAG

C. LAG cannot be used with salary column

D. Missing OVER() clause for LAG function

Solution

1. Step 1: Check LAG function syntax

   LAG requires an OVER() clause to specify ordering. The query uses ORDER BY without OVER(), which is invalid.

2. Step 2: Confirm correct syntax

   The correct syntax is LAG(salary) OVER (ORDER BY id). The query misses OVER(), causing a syntax error.

3. Final Answer:

   Missing OVER() clause for LAG function -> Option D

4. Quick Check:

   LAG needs OVER() with ORDER BY [OK]

Hint: LAG always needs OVER() clause [OK]

Common Mistakes:

• Using ORDER BY without OVER()
• Placing alias before function
• Misunderstanding LAG usage

5. You want to find the difference between each employee's salary and the previous employee's salary ordered by id. Which query correctly calculates this difference?

hard

A. SELECT id, salary, LAG(salary) - salary OVER (ORDER BY id) AS diff FROM Employees;

B. SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees;

C. SELECT id, salary, salary - LAG(salary) FROM Employees ORDER BY id;

D. SELECT id, salary, salary - LAG(salary) PARTITION BY id AS diff FROM Employees;

Solution

1. Step 1: Understand the goal

   We want to subtract the previous salary from the current salary, ordered by id.

2. Step 2: Check each option's correctness

   SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees; correctly uses LAG(salary) OVER (ORDER BY id) and subtracts it from salary.
   SELECT id, salary, LAG(salary) - salary OVER (ORDER BY id) AS diff FROM Employees; reverses subtraction order.
   SELECT id, salary, salary - LAG(salary) FROM Employees ORDER BY id; misses OVER clause.
   SELECT id, salary, salary - LAG(salary) PARTITION BY id AS diff FROM Employees; misuses PARTITION BY without OVER.

3. Final Answer:

   SELECT id, salary, salary - LAG(salary) OVER (ORDER BY id) AS diff FROM Employees; -> Option B

4. Quick Check:

   Difference = current - previous salary using LAG [OK]

Hint: Use LAG with OVER and subtract current minus previous [OK]

Common Mistakes:

• Forgetting OVER() clause
• Subtracting in wrong order
• Misusing PARTITION BY without OVER