8/21/25, 6:33 PM LEAD LAG Functions in SQL
teach me everyting about lead lag and alll realated in sql
LEAD and LAG Functions in SQL
Overview
The LEAD and LAG window functions are used to access rows before or after the current
row in a dataset. They are often used for comparing values across rows without
using JOIN operations.
Syntax
-- LAG LAG(column_name, offset, default_value) OVER (PARTITION BY
partition_column ORDER BY order_column) -- LEAD LEAD(column_name, offset,
default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
column_name : The column from which to fetch the value.
offset : Number of rows to look backward (LAG) or forward (LEAD). Defaults to 1 if
omitted.
default_value : Value to return if the offset is out of bounds. Defaults to NULL .
PARTITION BY : Divides the dataset into partitions for separate computations.
ORDER BY : Defines the order of rows for the function.
Key Differences
LAG : Fetches data from a previous row.
LEAD : Fetches data from a next row.
Use Cases
1. Calculating differences between rows:
Compare values in the current row with the previous or next row.
2. Trend analysis:
Analyze patterns in stock prices, sales, etc.
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 1/6
8/21/25, 6:33 PM LEAD LAG Functions in SQL
3. Event tracking:
Identify time gaps between consecutive events.
Examples
Basic Example
SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS
prev_salary, LEAD(salary) OVER (ORDER BY hire_date) AS next_salary FROM
employees;
LAG(salary) : Retrieves the salary of the previous employee.
LEAD(salary) : Retrieves the salary of the next employee.
With PARTITION BY
SELECT department_id, employee_id, salary, LAG(salary) OVER (PARTITION BY
department_id ORDER BY hire_date) AS prev_salary, LEAD(salary) OVER (PARTITION
BY department_id ORDER BY hire_date) AS next_salary FROM employees;
Adds a partition for department_id , so computations are scoped within each
department.
Calculating Differences
SELECT employee_id, salary, LAG(salary) OVER (ORDER BY hire_date) AS
prev_salary, salary - LAG(salary) OVER (ORDER BY hire_date) AS
salary_difference FROM employees;
Handling Out of Bounds
SELECT employee_id, salary, LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS
prev_salary_with_default, LEAD(salary, 1, 0) OVER (ORDER BY hire_date) AS
next_salary_with_default FROM employees;
If there’s no previous or next row, 0 will be returned instead of NULL .
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 2/6
8/21/25, 6:33 PM LEAD LAG Functions in SQL
Common Scenarios
Detecting Gaps in Dates
SELECT order_id, order_date, LAG(order_date) OVER (ORDER BY order_date) AS
prev_order_date, DATEDIFF(order_date, LAG(order_date) OVER (ORDER BY
order_date)) AS days_between_orders FROM orders;
Ranking Changes Over Time
SELECT player_id, match_date, score, score - LAG(score) OVER (PARTITION BY
player_id ORDER BY match_date) AS score_change FROM player_scores;
Finding Start and End of Events
SELECT user_id, login_time, LEAD(login_time) OVER (PARTITION BY user_id ORDER
BY login_time) AS next_login, TIMESTAMPDIFF(MINUTE, login_time,
LEAD(login_time) OVER (PARTITION BY user_id ORDER BY login_time)) AS
time_to_next_login FROM logins;
Related Window Functions
FIRST_VALUE: Fetches the first value in a window.
LAST_VALUE: Fetches the last value in a window.
ROW_NUMBER: Assigns a unique number to each row in a partition.
RANK and DENSE_RANK: Provides ranking of rows.
Performance Tips
Use proper indexing for columns in ORDER BY for better performance.
Avoid large offsets if unnecessary; accessing many rows can be computationally
expensive.
Be cautious with PARTITION BY when dealing with large datasets to avoid memory
overhead.
Let me know if you'd like deeper exploration with practical examples!
and where is preceding used
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 3/6
8/21/25, 6:33 PM LEAD LAG Functions in SQL
PRECEDING in SQL
Overview
The keyword PRECEDING is used in windowing (or analytic) functions in SQL, specifically
within the ROWS or RANGE clause of a window definition. It defines the number of rows or
range of values that should be considered before the current row in the computation.
Syntax
<function> OVER ( PARTITION BY partition_column ORDER BY order_column ROWS
BETWEEN X PRECEDING AND Y FOLLOWING )
Key Concepts
PRECEDING : Refers to rows before the current row.
CURRENT ROW : Refers to the current row being processed.
FOLLOWING : Refers to rows after the current row.
Use Cases of PRECEDING
1. Cumulative Sums
To calculate a running total up to the current row:
SELECT employee_id, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN
UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM employees;
UNBOUNDED PRECEDING : Includes all rows from the start of the partition up to the
current row.
2. Moving Averages
To calculate a moving average over the last 3 rows:
SELECT employee_id, salary, AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN
2 PRECEDING AND CURRENT ROW) AS moving_avg FROM employees;
2 PRECEDING : Includes the current row and the 2 rows immediately before it.
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 4/6
8/21/25, 6:33 PM LEAD LAG Functions in SQL
3. Custom Window Frames
To calculate aggregates over a fixed number of rows before and after the current row:
SELECT order_id, order_date, SUM(order_amount) OVER (ORDER BY order_date ROWS
BETWEEN 3 PRECEDING AND 3 FOLLOWING) AS sum_window FROM orders;
3 PRECEDING AND 3 FOLLOWING : Includes 3 rows before, the current row, and 3 rows
after.
4. Lag-Like Behavior Using Window Frames
To mimic LAG using a custom frame:
SELECT employee_id, salary, SUM(salary) OVER (ORDER BY hire_date ROWS BETWEEN
1 PRECEDING AND 1 PRECEDING) AS lag_salary FROM employees;
1 PRECEDING AND 1 PRECEDING : Captures only the row immediately before the
current one.
Difference Between PRECEDING in ROWS and RANGE
ROWS:
Counts rows physically in the dataset.
Example:
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Includes the immediate previous and next rows.
RANGE:
Operates over a range of values in the ORDER BY column.
Example:
RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING
Includes all rows within a range of 100 units (e.g., days, amounts).
Practical Examples
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 5/6
8/21/25, 6:33 PM LEAD LAG Functions in SQL
Rolling Sales Totals Over Time
SELECT sale_date, SUM(sales) OVER (ORDER BY sale_date ROWS BETWEEN 7 PRECEDING
AND CURRENT ROW) AS weekly_sales FROM sales;
Aggregates sales for the last 7 days.
Tracking Stock Price Fluctuations
SELECT stock_id, trade_date, AVG(price) OVER (PARTITION BY stock_id ORDER BY
trade_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS moving_avg_price FROM
stocks;
Averages prices from the previous 2 days, the current day, and the next 2 days.
Performance Tips
Use ROWS for fixed row-based computations and RANGE for value-based
computations.
When using large frames (e.g., UNBOUNDED PRECEDING ), ensure appropriate indexing
on the ORDER BY column to avoid performance issues.
https://chatgpt.com/share/6741e9f9-1404-8012-807f-ee6600f765d0 6/6
