Window Functions — LAG and LEAD

SELECT month, revenue,
       LAG(revenue)  OVER (ORDER BY month) AS prev_month,
       LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_sales;

LAG gets the value from the previous row. LEAD gets the value from the next row. Both take an optional offset (default 1) and a default value for when there is no previous/next row.

SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change,
       ROUND(
         100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
         / LAG(revenue) OVER (ORDER BY month), 2
       ) AS pct_change
FROM monthly_sales;

MoM analysis is one of the most common real-world SQL tasks. The percentage change formula: (current - previous) / previous * 100. Watch for divide-by-zero when previous is 0.

WITH mom AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev_rev
  FROM monthly_sales
)
SELECT month, revenue, prev_rev
FROM mom
WHERE revenue < prev_rev;

Wrapping in a CTE lets you filter on the LAG result without repeating the window function. You cannot directly use window functions in WHERE — always use a CTE or subquery.

SELECT name, salary,
       LEAD(salary) OVER (ORDER BY salary DESC) AS next_lower,
       salary - LEAD(salary) OVER (ORDER BY salary DESC) AS gap
FROM employees;

LEAD with ORDER BY salary DESC gets the next lower salary. The gap shows how much more each person earns than the next person below them in the salary ladder.

SELECT name, department, salary, hire_date,
       LAG(salary) OVER (
         PARTITION BY department
         ORDER BY hire_date
       ) AS prev_hire_salary
FROM employees;

PARTITION BY department means LAG looks at the previous row within the same department only, ordered by hire date. Cross-department rows are ignored.