SQL Window Functions: LEAD() and LAG()
LEAD() and LAG() are window functions in SQL that allow you to access data from subsequent or
previous rows without using self-joins or subqueries. These functions are particularly useful when
analyzing trends, changes over time, or comparing row-wise data.
Syntax
LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY
order_column)
LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY
order_column)
Parameters:
- column_name: The column you want to access from a different row.
- offset (optional): Number of rows ahead or behind. Default is 1.
- default_value (optional): Value to return if the LEAD or LAG row doesn’t exist.
- PARTITION BY: (Optional) Splits the data into groups.
- ORDER BY: Defines the order of rows.
LEAD(): Looking Ahead
Use Case: To compare the current row with the next row.
Example: Sales Forecasting
SELECT
employee_id,
sales_month,
revenue,
LEAD(revenue) OVER (PARTITION BY employee_id ORDER BY sales_month) AS next_month_revenue
FROM
monthly_sales;
Explanation: This query compares an employee’s revenue with their revenue in the next month.
Useful for identifying whether sales are increasing or decreasing.
LAG(): Looking Behind
Use Case: To compare the current row with the previous row.
Example: Revenue Change Detection
SELECT
employee_id,
sales_month,
revenue,
LAG(revenue) OVER (PARTITION BY employee_id ORDER BY sales_month) AS
previous_month_revenue,
Like, Comment, and Repost!
revenue - LAG(revenue) OVER (PARTITION BY employee_id ORDER BY sales_month) AS
revenue_change FROM
monthly_sales;
Explanation: This helps identify how much revenue has changed compared to the previous month.
Scenario-Based Examples
Scenario 1: Identify First-Time Purchase
SELECT
customer_id,
purchase_date,
LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS
previous_purchase,
CASE
WHEN LAG(purchase_date) OVER (PARTITION BY customer_id ORDER BY purchase_date) IS
NULL THEN 'First Purchase' ELSE 'Repeat Purchase' END AS purchase_type FROM
purchases;
Helps marketing teams target first-time vs repeat customers.
Scenario 2: Detect Price Drop
SELECT
product_id,
price_date,
price,
LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) AS previous_price,
CASE
WHEN price < LAG(price) OVER (PARTITION BY product_id ORDER BY price_date) THEN
'Price Dropped' ELSE 'No Drop' END AS price_trend FROM product_prices;
Useful for pricing analysts to monitor pricing strategies.
Scenario 3: Patient Readmission Check (Healthcare Use Case)
SELECT
patient_id,
admission_date,
discharge_date,
LEAD(admission_date) OVER (PARTITION BY patient_id ORDER BY admission_date) AS
next_admission,
CASE
WHEN DATEDIFF(
DAY, discharge_date,
LEAD(admission_date) OVER (PARTITION BY patient_id ORDER BY admission_date)
) <= 30 THEN 'Readmitted Within 30 Days' ELSE 'No Readmission' END AS
readmission_status FROM admissions;
Useful in healthcare analytics to track readmissions for quality control.
Scenario 4: Running Comparison Between Salespersons
Like, Comment, and Repost!
SELECT
salesperson_id,
sales_date,
total_sales,
LAG(total_sales) OVER (PARTITION BY salesperson_id ORDER BY sales_date) AS
previous_sales,
total_sales - LAG(total_sales) OVER (PARTITION BY salesperson_id ORDER BY sales_date) AS
sales_change FROM daily_sales;
Helps performance tracking and trend visualization.
Common Pitfalls
- Missing ORDER BY: Without it, the function can behave unpredictably.
- Nulls: Use the default_value to avoid nulls when no previous/next record exists.
- Offset Usage: Default is 1, but can be customized if comparing to 2nd or 3rd previous/next record.
Summary
Function Looks at Use Case
LEAD() Next row Forecasting, next status
LAG() Previous row Change tracking, historical
comparison
Pro Tips
- Combine with CASE WHEN for flag-based logic.
- Use with PARTITION BY to ensure correct grouping (e.g., by customer, product, or employee).
- Chain with DATEDIFF or arithmetic operators for powerful comparisons.
Like, Comment, and Repost!