LAG accesses previous rows, LEAD accesses following rows. Perfect for comparisons and trend analysis.

LAG Function

SELECT

date,

revenue,

LAG(revenue) OVER (ORDER BY date) as prev_revenue

FROM daily_sales;

LEAD Function

SELECT

date,

revenue,

LEAD(revenue) OVER (ORDER BY date) as next_revenue

FROM daily_sales;

Calculate Change

SELECT

date,

revenue,

revenue - LAG(revenue) OVER (ORDER BY date) as daily_change,

ROUND((revenue - LAG(revenue) OVER (ORDER BY date)) / LAG(revenue) OVER (ORDER BY date) * 100, 2) as pct_change

FROM daily_sales;

LAG with Offset

SELECT

date,

revenue,

LAG(revenue, 7) OVER (ORDER BY date) as week_ago_revenue

FROM daily_sales;

LAG with Default Value

SELECT

date,

revenue,

LAG(revenue, 1, 0) OVER (ORDER BY date) as prev_revenue

FROM daily_sales;

Year-over-Year Comparison

SELECT

month,

year,

revenue,

LAG(revenue) OVER (PARTITION BY month ORDER BY year) as prev_year_revenue

FROM monthly_sales;

Generate LAG/LEAD Queries

AI2sql creates time-based comparisons easily.