ReviseAlgo Logo

Window Functions

LAG, LEAD, FIRST_VALUE, and LAST_VALUE

Accessing adjacent row variables without self-joins.

Last Updated: June 12, 2026 • 14 min read

LAG and LEAD access values from previous or next rows without self-joins. FIRST_VALUE and LAST_VALUE grab the first or last value in a window frame. These offset functions unlock time-series analysis — month-over-month growth, gap detection, and session tracking — all in a single pass over the data.

1. Introduction

Offset window functions let you "peek" at neighboring rows relative to the current row. LAG looks backward (previous row), LEAD looks forward (next row), and you can specify an offset to look multiple rows ahead or behind. This replaces complex self-joins with clean, readable expressions — and executes much faster.

2. Why It Matters

  • Time-series analytics: Month-over-month revenue growth, day-over-day user change, and week-over-week comparison all use LAG to access the previous period's value.
  • Gap detection: Find missing dates, gaps in sequences, or consecutive events using LAG/LEAD difference checks.
  • Session analysis: Calculate time between user actions (session duration, time between clicks) using LAG on timestamp columns.
  • Data cleaning: Detect sudden value jumps, anomalies, or duplicate entries by comparing adjacent rows.

3. Real-World Analogy

Imagine reading a stock price chart. For each day, you want to know: "What was yesterday's price?" (LAG) and "What will tomorrow's price be?" (LEAD). The daily change = today's price − yesterday's price (LAG). This is the foundation of all time-series analysis: comparing the current value against a previous or next value.

4. How It Works

  • LAG(col, n, default): Returns the value of col from n rows before the current row. Default is 1. Returns NULL (or a specified default) if no such row exists.
  • LEAD(col, n, default): Same as LAG but looks forward to the next n rows.
  • FIRST_VALUE(col): Returns the first value in the window frame. Useful with ORDER BY to get the "earliest" or "highest" value.
  • LAST_VALUE(col): Returns the last value in the window frame. Gotcha: the default frame ends at the current row, not the partition end. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the true last value.

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Month-over-month revenue growth:

Detect gaps in daily orders:

8. Common Mistakes

LAST_VALUE returns the current row

The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE returns the current row's value. Fix: add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Missing PARTITION BY with multiple entities

When analyzing multiple customers/products, always add PARTITION BY customer_id. Without it, LAG crosses entity boundaries — customer A's last order appears as customer B's "previous" order.

NULL handling with LAG

The first row's LAG returns NULL. Use COALESCE(LAG(col), 0) or LAG(col, 1, 0) (third argument = default) to avoid NULL in calculations.

Interview Insight

"Find all consecutive days where a user logged in." Use LAG to get the previous login date per user, then filter where current_date - prev_date = 1. Group consecutive days using a difference-based island detection pattern.

9. Quick Quiz

Q1: What does LAG(salary, 2, 0) return for the first row?

Answer: 0 (the default value). The first row has no "2 rows back", so LAG returns the third argument. Without a default, it returns NULL.

Q2: How do you compute the difference between the current and previous row?

Answer: value - LAG(value) OVER (ORDER BY date). For percentage change: 100.0 * (value - LAG(value) OVER (...)) / NULLIF(LAG(value) OVER (...), 0).

10. Scenario-Based Challenge

Challenge: User Session Analysis

Given a user_events(user_id, event_type, created_at) table, analyze user sessions:

  1. Use LAG to compute the time between consecutive events per user.
  2. Define a new session as any gap > 30 minutes from the previous event.
  3. Use a running sum of "new session" flags to assign session IDs.
  4. Compute each session's duration (first event to last event).

11. Debugging Exercise

Find the bug in this query:

Issues:

  1. Default frame: Without explicit frame bounds, LAST_VALUE returns the current row's value (frame ends at current row). Fix: add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
  2. Alternative: Use FIRST_VALUE(total) OVER (PARTITION BY customer_id ORDER BY order_date DESC) instead — simpler and avoids the frame issue.

12. Interview Questions

Q1: How do you calculate month-over-month growth rate?

A: 100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / NULLIF(LAG(revenue) OVER (ORDER BY month), 0). Use NULLIF to avoid division by zero when the previous month had no revenue.

Q2: What's the difference between LAG and a self-join for accessing previous rows?

A: Functionally equivalent, but LAG is simpler to write and typically faster — it accesses the previous row in a single pass without joining the table to itself. Self-joins require a unique column to match rows and are harder to read.

Q3: How do you find consecutive events (islands) using LAG?

A: Compute date - ROW_NUMBER() OVER (ORDER BY date) as a group key. Consecutive dates produce the same group key. Then GROUP BY this key to find each island's start, end, and length.

13. Production Considerations

  • Always specify PARTITION BY for multi-entity data: Without it, LAG/LEAD cross partition boundaries and produce incorrect results.
  • Use explicit frame bounds with LAST_VALUE: The default frame silently returns wrong results. Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
  • Handle NULLs at boundaries: The first row's LAG and last row's LEAD return NULL. Use COALESCE or the third argument (default value) to handle these gracefully.
  • Index sort columns: An index on (partition_column, order_column) avoids an expensive sort step during window function evaluation.
  • Use NULLIF for growth calculations: value / NULLIF(prev_value, 0) prevents division-by-zero errors when the previous period had no activity.