Positional Value Functions: LEAD and LAG0%

Positional Value Functions: LEAD and LAG

Intermediate12 min read•Updated: 2026-09-12

Positional Value Functions: LEAD and LAG

In relational databases, rows are theoretically unordered sets. However, real-world business intelligence often depends on inter-row comparisons:

What were our sales this month compared to last month (Month-over-Month growth)?
How much time elapsed between a user's current login and their previous login?
What was the price change between consecutive stock ticks?

Before window functions, answering these questions required costly self-joins with complex offset conditions. The LAG() and LEAD() positional value functions allow you to peer backward or forward across rows with zero self-joins.

Mechanics of LAG() and LEAD()

LAG(expr [, offset [, default]]): Accesses data from a preceding row at a specified physical offset prior to the current row within the partition.
LEAD(expr [, offset [, default]]): Accesses data from a succeeding row at a specified physical offset after the current row within the partition.

Both functions accept three arguments:

1
expr: The column or expression to retrieve.
2
offset (Optional, Default: 1): The number of rows back (for LAG) or forward (for LEAD) to inspect.
3
default (Optional, Default: NULL): The fallback value if the offset reaches outside the partition boundary.

Step-by-Step Example: Month-Over-Month (MoM) Growth

Suppose we have monthly revenue data:

1CREATE TABLE monthly_revenue (
2    month_id INT PRIMARY KEY,
3    revenue DECIMAL(10, 2)
4);
5 
6INSERT INTO monthly_revenue VALUES
7(1, 10000.00),
8(2, 12000.00),
9(3, 11500.00),
10(4, 15000.00);

Calculating prior month revenue, net difference, and percentage growth:

1SELECT 
  month_id,
  revenue,
  -- Fetch the revenue from the immediately preceding month
  LAG(revenue, 1, 0.00) OVER (ORDER BY month_id) AS prior_month_revenue,
  
  -- Absolute difference
  revenue - LAG(revenue, 1, revenue) OVER (ORDER BY month_id) AS mom_dollar_change,
  
  -- Percentage change
  ROUND(
      ((revenue - LAG(revenue, 1, NULL) OVER (ORDER BY month_id)) / 
      LAG(revenue, 1, NULL) OVER (ORDER BY month_id)) * 100, 
      2
  ) AS mom_growth_pct
16FROM monthly_revenue;

Query Results:

month_id	revenue	prior_month_revenue	mom_dollar_change	mom_growth_pct
1	10000.00	0.00	0.00	NULL
2	12000.00	10000.00	+2000.00	+20.00%
3	11500.00	12000.00	-500.00	-4.17%
4	15000.00	11500.00	+3500.00	+30.43%

Looking Ahead with LEAD()

LEAD() functions identically to LAG(), but looks forward:

1-- Find next appointment date for medical patients
2SELECT 
3    patient_id,
4    appointment_date,
5    LEAD(appointment_date, 1) OVER (
6        PARTITION BY patient_id 
7        ORDER BY appointment_date
8    ) AS next_scheduled_appointment
9FROM patient_appointments;

Additional Positional Functions: FIRST_VALUE & LAST_VALUE

MySQL 8.0 also provides:

FIRST_VALUE(expr): Returns the value from the first row of the window frame.
LAST_VALUE(expr): Returns the value from the last row of the window frame (requires careful frame specification like ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).

1-- Compare employee salary against the lowest salary in their department
2SELECT 
  emp_id,
  department,
  salary,
  FIRST_VALUE(salary) OVER (
      PARTITION BY department 
      ORDER BY salary ASC
  ) AS lowest_dept_salary
10FROM employees;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the LAG() window function accomplish?

Question 2

What value does LAG(salary, 1) return for the very first row in a partition if no default value is specified?

Question 3

Which function looks forward to fetch a value from a subsequent row in the partition?

Question 4

What is the second argument in LAG(amount, 2, 0)?

Question 5

Why are LAG and LEAD superior to traditional self-joins for trend analysis?

Next Lesson

ACID Properties in Relational Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK	ACID Properties in Relational Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

ACID Properties in Relational Databases

Positional Value Functions: LEAD and LAG

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Positional Value Functions: LEAD and LAG

In relational databases, rows are theoretically unordered sets. However, real-world business intelligence often depends on inter-row comparisons:

What were our sales this month compared to last month (Month-over-Month growth)?
How much time elapsed between a user's current login and their previous login?
What was the price change between consecutive stock ticks?

Mechanics of LAG() and LEAD()

LAG(expr [, offset [, default]]): Accesses data from a preceding row at a specified physical offset prior to the current row within the partition.
LEAD(expr [, offset [, default]]): Accesses data from a succeeding row at a specified physical offset after the current row within the partition.

Both functions accept three arguments:

1
expr: The column or expression to retrieve.
2
offset (Optional, Default: 1): The number of rows back (for LAG) or forward (for LEAD) to inspect.
3
default (Optional, Default: NULL): The fallback value if the offset reaches outside the partition boundary.

Step-by-Step Example: Month-Over-Month (MoM) Growth

Suppose we have monthly revenue data:

1CREATE TABLE monthly_revenue (
2    month_id INT PRIMARY KEY,
3    revenue DECIMAL(10, 2)
4);
5 
6INSERT INTO monthly_revenue VALUES
7(1, 10000.00),
8(2, 12000.00),
9(3, 11500.00),
10(4, 15000.00);

Calculating prior month revenue, net difference, and percentage growth:

1SELECT 
  month_id,
  revenue,
  -- Fetch the revenue from the immediately preceding month
  LAG(revenue, 1, 0.00) OVER (ORDER BY month_id) AS prior_month_revenue,
  
  -- Absolute difference
  revenue - LAG(revenue, 1, revenue) OVER (ORDER BY month_id) AS mom_dollar_change,
  
  -- Percentage change
  ROUND(
      ((revenue - LAG(revenue, 1, NULL) OVER (ORDER BY month_id)) / 
      LAG(revenue, 1, NULL) OVER (ORDER BY month_id)) * 100, 
      2
  ) AS mom_growth_pct
16FROM monthly_revenue;

Query Results:

month_id	revenue	prior_month_revenue	mom_dollar_change	mom_growth_pct
1	10000.00	0.00	0.00	NULL
2	12000.00	10000.00	+2000.00	+20.00%
3	11500.00	12000.00	-500.00	-4.17%
4	15000.00	11500.00	+3500.00	+30.43%

Looking Ahead with LEAD()

LEAD() functions identically to LAG(), but looks forward:

1-- Find next appointment date for medical patients
2SELECT 
3    patient_id,
4    appointment_date,
5    LEAD(appointment_date, 1) OVER (
6        PARTITION BY patient_id 
7        ORDER BY appointment_date
8    ) AS next_scheduled_appointment
9FROM patient_appointments;

Additional Positional Functions: FIRST_VALUE & LAST_VALUE

MySQL 8.0 also provides:

FIRST_VALUE(expr): Returns the value from the first row of the window frame.
LAST_VALUE(expr): Returns the value from the last row of the window frame (requires careful frame specification like ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING).

1-- Compare employee salary against the lowest salary in their department
2SELECT 
  emp_id,
  department,
  salary,
  FIRST_VALUE(salary) OVER (
      PARTITION BY department 
      ORDER BY salary ASC
  ) AS lowest_dept_salary
10FROM employees;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does the LAG() window function accomplish?

Question 2

What value does LAG(salary, 1) return for the very first row in a partition if no default value is specified?

Question 3

Which function looks forward to fetch a value from a subsequent row in the partition?

Question 4

What is the second argument in LAG(amount, 2, 0)?

Question 5

Why are LAG and LEAD superior to traditional self-joins for trend analysis?

Next Lesson

ACID Properties in Relational Databases

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK	ACID Properties in Relational Databases

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next