Value Functions: LAG, LEAD, FIRST_VALUE

Need to compare this month's sales to last month? Show the difference from a row's value to the first in its group? Value functions let you access data from other rows without self-joins.

LAG: Look Backward

LAG accesses data from a previous row.

LAG(column, offset, default) OVER (ORDER BY ...)
-- offset: how many rows back (default: 1)
-- default: value if no previous row (default: NULL)

Month-Over-Month Comparison

SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) as prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) as change
FROM monthly_sales;

Year-Over-Year (Looking Back 12 Rows)

SELECT
  month,
  revenue,
  LAG(revenue, 12) OVER (ORDER BY month) as same_month_last_year,
  ROUND(
    100.0 * (revenue - LAG(revenue, 12) OVER (ORDER BY month))
    / LAG(revenue, 12) OVER (ORDER BY month),
    1
  ) as yoy_growth_pct
FROM monthly_sales;

LEAD: Look Forward

LEAD accesses data from a future row - the opposite of LAG.

LEAD(column, offset, default) OVER (ORDER BY ...)

Time Until Next Event

SELECT
  customer_id,
  order_date,
  LEAD(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) as next_order,
  LEAD(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) - order_date as days_until_next
FROM orders;

Using Default Values

-- Use 0 instead of NULL when no previous value exists
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month) as prev_revenue
FROM monthly_sales;

FIRST_VALUE and LAST_VALUE

Access the first or last value in a partition.

Compare to First Value

SELECT
  employee,
  department,
  hire_date,
  salary,
  FIRST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) as first_hire_salary,
  salary - FIRST_VALUE(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) as salary_vs_first
FROM employees;

LAST_VALUE Gotcha

-- Wrong: returns current row's value (default frame)
LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY hire_date)

-- Correct: sees all rows in partition
LAST_VALUE(salary) OVER (
  PARTITION BY dept
  ORDER BY hire_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Comparing to Min/Max

SELECT
  product,
  category,
  price,
  -- Lowest price in category (first when sorted by price)
  FIRST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price
  ) as min_price,
  -- Highest price (last in price order, need full frame)
  LAST_VALUE(price) OVER (
    PARTITION BY category
    ORDER BY price
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as max_price,
  -- How this product compares
  ROUND(100.0 * price / FIRST_VALUE(price) OVER (
    PARTITION BY category ORDER BY price
  ), 0) as pct_of_min
FROM products;

Real-World: Session Analysis

-- Calculate time spent on each page
SELECT
  user_id,
  page,
  timestamp,
  LEAD(timestamp) OVER (
    PARTITION BY user_id
    ORDER BY timestamp
  ) - timestamp as time_on_page
FROM page_views;

Real-World: Gap Analysis

-- Find days with no orders
SELECT
  order_date,
  LEAD(order_date) OVER (ORDER BY order_date) as next_order_date,
  LEAD(order_date) OVER (ORDER BY order_date) - order_date - 1 as gap_days
FROM (
  SELECT DISTINCT order_date FROM orders
) dates
WHERE LEAD(order_date) OVER (ORDER BY order_date) - order_date > 1;

Key Takeaways

Next up: Window Frames - Fine-tune exactly which rows your window function sees.