LAG and LEAD for row comparison in PostgreSQL - Deep Dive

Overview - LAG and LEAD for row comparison

What is it?

LAG and LEAD are SQL functions used to access data from previous or next rows in a result set without using complex self-joins. They help compare values between rows easily by looking backward or forward in the data. This is useful for analyzing trends, changes, or differences between rows in a table.

Why it matters

Without LAG and LEAD, comparing rows requires complicated and slow queries with self-joins or subqueries. These functions simplify and speed up such comparisons, making it easier to find patterns like increases, decreases, or gaps in data. This helps businesses make better decisions by quickly spotting changes over time or between related records.

Where it fits

Before learning LAG and LEAD, you should understand basic SQL SELECT queries, ORDER BY clauses, and window functions. After mastering these, you can explore advanced window functions, running totals, and complex analytics queries that build on row comparisons.

Mental Model

Core Idea

LAG and LEAD let you peek at the previous or next row’s data in a sorted list to compare values easily within the same query.

Think of it like...

Imagine reading a book and wanting to compare the current page’s content with the previous or next page without flipping back and forth manually. LAG and LEAD are like bookmarks that let you see those pages instantly.

┌───────────────┐
│   Row 1       │
│   Value: 100  │
│   LAG: NULL   │ ← No previous row
│   LEAD: 110   │ ← Next row value
└───────────────┘
       ↓
┌───────────────┐
│   Row 2       │
│   Value: 110  │
│   LAG: 100    │ ← Previous row value
│   LEAD: 105   │ ← Next row value
└───────────────┘
       ↓
┌───────────────┐
│   Row 3       │
│   Value: 105  │
│   LAG: 110    │
│   LEAD: NULL  │ ← No next row

Build-Up - 6 Steps

1

Foundation

Understanding row order importance

Concept: LAG and LEAD depend on the order of rows to know which row is previous or next.

In SQL, rows have no inherent order unless you specify it using ORDER BY. LAG and LEAD look at rows based on this order. For example, ordering sales by date lets you compare today’s sales with yesterday’s or tomorrow’s.

Result

You learn that without ORDER BY, LAG and LEAD results are unpredictable.

Knowing that row order controls which rows LAG and LEAD access prevents confusion and errors in comparisons.

2

Foundation

Basic syntax of LAG and LEAD

Concept: LAG and LEAD functions take a column and return the value from a previous or next row respectively.

Syntax: LAG(column, offset, default) OVER (ORDER BY column) LEAD(column, offset, default) OVER (ORDER BY column) - column: the value to fetch from another row - offset: how many rows back or forward (default 1) - default: value if no row exists Example: SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales, LEAD(sales) OVER (ORDER BY date) AS next_sales FROM sales_data;

Result

Each row shows its sales, previous day’s sales, and next day’s sales.

Understanding the parameters lets you customize how far back or forward you look and handle missing rows gracefully.

3

Intermediate

Using LAG and LEAD for difference calculation

🤔Before reading on: do you think you can calculate the change between rows using LAG or LEAD? Commit to your answer.

Concept: You can subtract the previous or next row’s value from the current to find differences or changes.

Example to find daily sales change: SELECT date, sales, sales - LAG(sales) OVER (ORDER BY date) AS change_from_prev FROM sales_data; This shows how much sales increased or decreased compared to the previous day.

Result

A new column with positive or negative numbers indicating sales change.

Knowing you can do arithmetic with LAG/LEAD results unlocks powerful trend analysis in SQL.

4

Intermediate

Handling partitions with LAG and LEAD

🤔Before reading on: do you think LAG and LEAD can compare rows across different groups or only the whole table? Commit to your answer.

Concept: You can use PARTITION BY to reset LAG and LEAD calculations within groups, like per customer or category.

Example: SELECT customer_id, order_date, amount, LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount FROM orders; This compares each customer’s orders only within their own history.

Result

Previous order amounts per customer, ignoring other customers’ data.

Understanding partitions lets you compare rows meaningfully within groups, avoiding mixing unrelated data.

5

Advanced

Using offsets and default values

🤔Before reading on: do you think LAG and LEAD can look more than one row away? Commit to your answer.

Concept: You can specify how many rows back or forward to look and provide a default if no such row exists.

Example: SELECT date, sales, LAG(sales, 2, 0) OVER (ORDER BY date) AS sales_two_days_ago FROM sales_data; This fetches sales from two days before, or 0 if not available.

Result

A column showing sales from two rows earlier or zero when missing.

Knowing offsets and defaults increases flexibility for complex comparisons and avoids NULL surprises.

6

Expert

Performance and indexing considerations

🤔Before reading on: do you think LAG and LEAD always run fast regardless of table size? Commit to your answer.

Concept: LAG and LEAD are window functions that scan ordered data; performance depends on indexing and query design.

If the ORDER BY column is indexed, the database can quickly access rows in order. Without indexes, large tables may cause slow queries. Also, avoid unnecessary partitions or complex expressions inside LAG/LEAD to keep performance good.

Result

Well-indexed queries with LAG/LEAD run efficiently even on big data.

Understanding performance helps write scalable queries and avoid slowdowns in production.

Under the Hood

LAG and LEAD are window functions that operate after the FROM and WHERE clauses but before the final SELECT output. They create a window frame over the ordered rows and fetch values from relative positions within that frame. Internally, the database engine sorts the data as specified, then accesses the target row’s value by offset without joining tables. This avoids repeated scans and complex joins.

Why designed this way?

These functions were designed to simplify common row-to-row comparisons that were previously done with self-joins, which are costly and complex. By integrating them as window functions, SQL allows efficient, readable, and expressive queries for analytics. The design balances flexibility (offsets, partitions) with performance by leveraging internal sorting and indexing.

┌───────────────┐
│ Input Rows    │
│ (unsorted)   │
└──────┬────────┘
       │ ORDER BY
       ▼
┌───────────────┐
│ Sorted Rows   │
│ (window frame)│
└──────┬────────┘
       │ LAG/LEAD
       ▼
┌───────────────┐
│ Access offset │
│ row value     │
└──────┬────────┘
       │ Output
       ▼
┌───────────────┐
│ Result Set    │
│ with LAG/LEAD │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does LAG always return the previous row regardless of ORDER BY? Commit to yes or no.

Common Belief: LAG simply returns the previous row in the table as stored.

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

Why it matters: Ignoring ORDER BY leads to wrong comparisons and confusing results.

Quick: Can LAG and LEAD access rows across different partitions? Commit to yes or no.

Common Belief: LAG and LEAD ignore partitions and look across the entire dataset.

Reality: Partitions isolate groups; LAG and LEAD only look within the same partition.

Why it matters: Misunderstanding partitions causes mixing unrelated data and incorrect analysis.

Quick: Does LAG always return a value or NULL if no previous row? Commit to yes or no.

Common Belief: LAG returns NULL if there is no previous row and cannot be changed.

Reality: You can specify a default value to return instead of NULL when no previous row exists.

Why it matters: Not using defaults can cause NULL handling bugs in calculations.

Quick: Are LAG and LEAD always faster than self-joins? Commit to yes or no.

Common Belief: LAG and LEAD are always faster than self-joins.

Reality: While usually faster, performance depends on indexing and query design; poorly indexed queries can still be slow.

Why it matters: Assuming automatic speed can lead to ignoring query optimization and slow production systems.

Expert Zone

1

LAG and LEAD can be combined with RANGE or ROWS window frames to control exactly which rows are visible, affecting results subtly.

2

Using LAG/LEAD with complex expressions or joins inside the window can cause unexpected NULLs or performance hits.

3

Default values in LAG/LEAD help avoid NULL propagation in calculations but must be chosen carefully to not skew results.

When NOT to use

Avoid LAG and LEAD when comparing rows across unrelated groups without partitions or when needing complex multi-row comparisons better handled by self-joins or recursive queries. For very large datasets without proper indexing, consider pre-aggregating data or using materialized views.

Production Patterns

Common patterns include calculating running differences in time series, detecting gaps or changes in event logs, comparing current and previous customer orders, and generating lead/lag indicators in financial reports. Experts often combine LAG/LEAD with CASE statements for conditional logic and use partitions to segment data cleanly.

Connections

Time Series Analysis

LAG and LEAD provide the SQL tools to perform time-based comparisons essential in time series.

Understanding LAG and LEAD helps grasp how time series data is analyzed by comparing past and future points within the same dataset.

Version Control Systems

Both track changes between states over time, similar to how LAG and LEAD compare rows to find differences.

Recognizing this connection clarifies how data evolves and how to detect changes efficiently.

Memory Paging in Operating Systems

Just as LAG and LEAD access nearby rows, paging accesses nearby memory pages to optimize performance.

This analogy reveals how locality and ordered access patterns improve efficiency in both databases and OS memory management.

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 how to order rows, so LAG picks arbitrary previous rows.

#2 Forgetting PARTITION BY when comparing grouped data mixes unrelated rows.

Wrong approach:

SELECT customer_id, order_date, amount, LAG(amount) OVER (ORDER BY order_date) AS prev_amount FROM orders;

Correct approach:

SELECT customer_id, order_date, amount, LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_amount FROM orders;

Root cause:

Without PARTITION BY, LAG looks across all customers, not just within one.

#3 Not handling NULLs from LAG causes errors in calculations.

Wrong approach:

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

Correct approach:

SELECT date, sales, sales - COALESCE(LAG(sales) OVER (ORDER BY date), 0) AS diff FROM sales_data;

Root cause:

LAG returns NULL for first row; subtracting NULL results in NULL, breaking calculations.

Key Takeaways

LAG and LEAD let you compare values from previous or next rows easily within the same query by defining a clear order.

Always use ORDER BY to control which rows are considered previous or next; otherwise, results are unpredictable.

PARTITION BY lets you compare rows within groups, avoiding mixing unrelated data.

You can customize how far back or forward to look and provide default values to handle missing rows gracefully.

Understanding performance implications and indexing helps write efficient queries using LAG and LEAD in real-world systems.

Practice

1. What is the main purpose of the LAG function in PostgreSQL?

easy

A. To sort rows in ascending order

B. To access data from the next row in the same result set

C. To delete duplicate rows

D. To access data from a previous row in the same result set

Solution

1. Step 1: Understand LAG function behavior

   The LAG function returns the value from a previous row based on the current row's position.

2. Step 2: Compare with other options

   LEAD accesses the next row, sorting and deleting duplicates are unrelated to LAG.

3. Final Answer:

   To access data from a previous row in the same result set -> Option D

4. Quick Check:

   LAG = previous row data access [OK]

Hint: LAG looks backward, LEAD looks forward in rows [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Thinking LAG sorts rows
• Assuming LAG deletes duplicates

2. Which of the following is the correct syntax to use LEAD to get the next row's salary ordered by employee_id?

easy

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

B. LEAD(salary ORDER BY employee_id)

C. LEAD(salary) ORDER BY employee_id

D. LEAD(ORDER BY salary) OVER (employee_id)

Solution

1. Step 1: Recall correct LEAD syntax

   The correct syntax uses LEAD(column) OVER (ORDER BY column) to specify the column and ordering.

2. Step 2: Check each option

   LEAD(salary) OVER (ORDER BY employee_id) matches the correct syntax; others misuse parentheses or clause order.

3. Final Answer:

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

4. Quick Check:

   LEAD(column) OVER (ORDER BY column) [OK]

Hint: LEAD/LAG need OVER with ORDER BY inside parentheses [OK]

Common Mistakes:

• Placing ORDER BY outside OVER clause
• Missing parentheses around ORDER BY
• Putting ORDER BY inside function parameters

3. Given the table sales with columns month and revenue, what will this query return?

SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_revenue FROM sales;

medium

A. Each row shows only current month revenue

B. Each row shows current month revenue and next month's revenue

C. Each row shows current month revenue and previous month's revenue

D. Query will cause a syntax error

Solution

1. Step 1: Understand LAG with ORDER BY month

   The LAG(revenue) returns the revenue from the previous month ordered by month.

2. Step 2: Interpret query output

   Each row shows current month revenue and previous month's revenue in prev_revenue. The first row's prev_revenue will be NULL.

3. Final Answer:

   Each row shows current month revenue and previous month's revenue -> Option C

4. Quick Check:

   LAG = previous row value [OK]

Hint: LAG returns previous row's value based on ORDER BY [OK]

Common Mistakes:

• Confusing LAG with LEAD
• Expecting next month's revenue instead
• Thinking query causes error

4. Identify the error in this query:

SELECT employee_id, salary, LEAD(salary) ORDER BY employee_id FROM employees;

medium

A. ORDER BY should be inside LEAD() function

B. Missing OVER() clause for LEAD function

C. LEAD cannot be used with salary column

D. employee_id must be in GROUP BY clause

Solution

1. Step 1: Check LEAD function syntax

   LEAD requires an OVER() clause to specify ordering and partitioning.

2. Step 2: Analyze query structure

   The query uses LEAD(salary) without OVER(), causing a syntax error.

3. Final Answer:

   Missing OVER() clause for LEAD function -> Option B

4. Quick Check:

   Window functions need OVER() [OK]

Hint: Always use OVER() with LEAD/LAG [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Confusing window functions with aggregates

5. You want to find all employees whose salary increased compared to the previous employee ordered by hire_date. Which query correctly uses LAG to achieve this?

hard

A. SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary;

B. SELECT employee_id, salary FROM employees WHERE salary > (SELECT LAG(salary) FROM employees ORDER BY hire_date);

C. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees WHERE salary > prev_salary;

D. SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees HAVING salary > prev_salary;

Solution

1. Step 1: Understand LAG usage for filtering

   Window functions like LAG cannot be used directly in WHERE clause; use subquery or CTE to compute then filter.

2. Step 2: Analyze options

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; correctly uses subquery. The second option's subquery LAG lacks OVER clause (syntax error). The third uses alias in WHERE (invalid). The fourth uses HAVING without GROUP BY (invalid).

3. Final Answer:

   SELECT employee_id, salary FROM (SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS prev_salary FROM employees) t WHERE salary > prev_salary; -> Option A

4. Quick Check:

   Subquery to filter on window functions [OK]

Hint: Use subquery to filter on window function results [OK]

Common Mistakes:

• Using alias in WHERE clause
• Misusing HAVING without GROUP BY
• Incorrect subquery for LAG