Window Functions: Intermediate

Compare each row to the one before or after

LAG and LEAD let you look backward and forward across rows without leaving your query. Every dataset has a first row with nothing before it, and that boundary is where period-over-period comparisons can quietly break.

LAG and LEAD are window functions that reach backward or forward to access values from other rows without a self-join. They are essential for comparing a row to the row before or after it.

Think of your result set as a spreadsheet. When you are on row 5, LAG lets you peek at row 4 (or row 3, or any row above), and LEAD lets you peek at row 6 (or row 7, or any row below). The database does not need to join the table to itself or use a correlated subquery. It simply remembers what it already processed and knows what comes next.

LAG accepts up to three arguments: the column to read, an optional offset (defaults to 1), and an optional default value for when there is no previous row. LEAD works identically but looks forward instead of backward.

The most common use of LAG is period-over-period comparison. By subtracting the previous period value from the current one, you get the change between periods in a single pass.

The first row has NULL for prev_month because there is no row before January. The arithmetic with NULL also produces NULL for mom_change. You can handle this with COALESCE or by providing a default value in the LAG call itself.

Beyond simple comparisons, LAG and LEAD unlock powerful patterns for forecasting, change detection, and anomaly identification.

While LAG is used to look backward, LEAD is equally valuable for forward-looking analysis. A common use case is detecting upcoming changes. For example, if you have a table of subscription plan changes, LEAD can show what plan a customer will switch to next, letting you identify potential downgrades before they happen.

Customer C1 is about to downgrade from Premium to Basic. Customer C2 upgraded from Basic to Premium. The NULL values in next_plan indicate the most recent plan, the one they are still on. This kind of forward-looking analysis powers churn prediction models and retention campaigns.

LAG is also powerful for detecting gaps or anomalies in sequences. If you expect events to occur daily, you can flag days where the gap between consecutive events is larger than expected. This is critical in monitoring pipelines where a missing day of data could indicate a broken ETL job or a sensor failure.

The row with days_since_last = 3 reveals a gap: sensor S1 skipped March 3rd and 4th. You could wrap this in a WHERE clause (via a subquery) to filter only the rows where the gap exceeds your threshold. In a production pipeline, this pattern feeds automated alerting systems that notify engineers when data stops flowing.

The offset parameter lets you look further back or ahead. LAG(revenue, 12) looks 12 rows back, useful for year-over-year comparison with monthly data:

The first 12 rows will have NULL for same_month_last_year because there are no rows 12 positions back yet. Once you have a full year of data, every row gets a comparison. Year-over-year analysis is one of the most requested metrics in business intelligence dashboards, and LAG with offset 12 is by far the cleanest way to compute it.

SELECT
  metric_name,
  metric_value,
  ___() OVER (
    ORDER BY metric_value ___
  ) AS ranking
FROM employee_metrics

DENSE_RANK is the correct choice whenever you want to count how many distinct performance levels exist above a given row, because it assigns consecutive integers with no gaps even when multiple rows share the same value.

LAG and LEAD are complementary to ranking functions because once you have ranked rows you can use LAG to compare each row to the one immediately above or below it in the ranking, which enables tier-boundary analysis without additional joins.

Pull specific values from a window

FIRST_VALUE, LAST_VALUE, and NTH_VALUE let you pull specific positional values from a window. They answer questions like "what was the first order amount in each category?" or "what was the third highest salary in each department?"

These functions are different from LAG and LEAD in an important way. LAG and LEAD access rows relative to the current row (1 row back, 2 rows forward). Positional functions access rows relative to the window frame itself (the first row in the frame, the last row, or the Nth row). This distinction matters because the frame can be the entire partition or just a sliding subset of it.

FIRST_VALUE returns the value from the first row in the window frame. This is commonly used to compare every row against the starting point. Unlike LAST_VALUE, FIRST_VALUE works correctly with the default frame because the first row of the frame is always included.

LAST_VALUE has a subtle but critical gotcha that catches nearly everyone the first time. The default window frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This means the "last value" in the frame is always the current row itself.

To understand why this happens, picture the frame as a sliding window that starts at the first row of the partition and ends at the current row. When the database processes row 1, the frame contains only row 1, so the last value is row 1. When it processes row 2, the frame contains rows 1 and 2, so the last value is row 2. The frame always ends at "where you are now," so LAST_VALUE just echoes the current row.

Notice how default_last just echoes each row's own salary? That is because the frame ends at CURRENT ROW, so the "last" value is always the current row. To get the actual last value in the entire partition, you must explicitly expand the frame:

By specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the frame spans the entire partition. Now LAST_VALUE correctly returns the last value (the lowest salary when ordered descending).

Whenever you use LAST_VALUE or NTH_VALUE, always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING unless you specifically want the frame to end at the current row. This is one of the most frequently tested window function concepts in interviews.

NTH_VALUE returns the value from the Nth row in the window frame. It is useful when you need a specific position, such as the second highest or third most recent.

Notice the full frame specification again. Without it, NTH_VALUE suffers from the same trap as LAST_VALUE: the first row in each partition would return NULL for second_highest because the frame at that point only contains one row.

SELECT
  department,
  metric_name,
  metric_value,
  ___(___) OVER (
    PARTITION BY department
    ORDER BY metric_value DESC
  ) AS top_value
FROM employee_metrics

FIRST_VALUE with ORDER BY DESC gives the maximum value, while LAST_VALUE needs an explicit frame clause to see the entire partition.

Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using LAST_VALUE or NTH_VALUE to avoid unexpected NULLs.

NTH_VALUE is useful for specific positions like second highest or third most recent, but also requires an explicit full-partition frame.

Define exactly which rows a window covers

The frame clause follows ORDER BY inside the OVER clause. The syntax is ROWS BETWEEN followed by a start bound and an end bound:

UNBOUNDED PRECEDING

Start from the very first row in the partition.

N PRECEDING

Start N rows before the current row.

CURRENT ROW

The current row being processed.

N FOLLOWING

End N rows after the current row.

UNBOUNDED FOLLOWING

Extend to the very last row in the partition.

SQL offers two frame types: ROWS and RANGE. They behave differently when your ORDER BY column contains duplicate values, which is common in real data.

ROWS counts physical rows. "3 PRECEDING" means exactly 3 rows above the current one, regardless of the values in those rows. RANGE, on the other hand, groups by value. "3 PRECEDING" with RANGE means all rows whose ORDER BY value is within 3 units of the current row's value. If multiple rows share the same value, RANGE treats them as a single logical group.

In practice, ROWS is used far more often than RANGE. ROWS is simpler to reason about, more predictable, and more widely supported across database engines. Use RANGE only when you specifically need value-based grouping, such as computing a sum of all transactions within a dollar amount range of the current row.

The first row only has 2 values in its frame (itself and the next row) because there is no preceding row. The last row similarly only has 2 values. The AVG function automatically handles partial frames by averaging whatever rows are present. This edge behavior is important to understand: moving averages at the boundaries of your data are based on fewer data points and may be less reliable.

The frame ROWS BETWEEN 6 PRECEDING AND CURRENT ROW creates a trailing 7-day window (6 previous rows plus the current one). Early rows where fewer than 7 days of data exist will average whatever is available. Note that the trailing average stabilizes after the first full week of data, making subsequent values more reliable for trend analysis.

Bounded frames (like 6 PRECEDING AND CURRENT ROW) limit the number of rows the function examines at each step. Unbounded frames (like UNBOUNDED PRECEDING AND CURRENT ROW) grow as the partition progresses. This distinction has practical implications.

With a bounded frame, the database only needs to maintain a fixed-size window in memory. Adding one row to the front and dropping one from the back is efficient. With an unbounded frame, the running calculation must account for every row seen so far. For SUM and COUNT, the database can maintain a running total incrementally. But functions that require sorting or comparing all values in the frame cannot take this shortcut, making unbounded frames expensive on very large partitions.

SELECT
  bill_date,
  amount,
  ___(amount, ___) OVER (
    ORDER BY bill_date
  ) AS prev_amount
FROM cloud_costs

LAG with an offset of 1 is the standard tool for day-over-day or period-over-period comparisons because it reads the value from the preceding row in a single pass without requiring the table to be joined to itself.

Providing a default value as the third argument to LAG replaces the NULL that appears on the first row of each partition, which prevents NULL propagation when you subsequently subtract the lagged value from the current value.

Frame specifications and LAG serve different purposes: frames control which rows an aggregate function sees at each step, while LAG always jumps to a fixed offset regardless of any frame clause you specify.

Split rows into equal-sized groups

NTILE divides the ordered rows in a partition into a specified number of roughly equal groups and assigns each row a bucket number from 1 to N. It is the go-to function for creating quartiles, deciles, and custom percentile buckets.

Percentile bucketing is fundamental to data engineering and analytics. Nearly every organization segments its users, customers, or products into tiers based on some metric. The top 25 percent of customers by spending might receive premium support. The bottom 10 percent of products by revenue might be candidates for discontinuation. NTILE automates this segmentation cleanly.

The NTILE function follows a predictable pattern for dividing rows into equal groups, making it straightforward to apply once you understand its behavior.

When the row count does not divide evenly, NTILE distributes the extra rows to the earlier buckets. With 10 rows and 4 buckets, the first two buckets get 3 rows each and the last two get 2 rows each.

Use NTILE(3) for tertiles (3 groups), NTILE(10) for deciles (10 groups), or NTILE(100) for percentiles. Deciles are popular for salary analysis and customer segmentation. Percentiles give the finest granularity.

Understanding when and how to use NTILE effectively requires comparing it to alternatives and knowing how to combine it with other window clauses.

Before NTILE, analysts created buckets using manual CASE statements with hardcoded thresholds. This approach is fragile because the thresholds must be updated whenever the data distribution changes. NTILE adapts automatically.

Use manual CASE when your bucket boundaries have business meaning (such as "under 50K is junior, 50-100K is mid, over 100K is senior"). Use NTILE when you want equal-sized groups based on relative position, such as "top 25 percent of performers."

Combine NTILE with PARTITION to create buckets within groups. This is essential when the same absolute value means different things in different contexts.

Note that NTILE does not consider the actual values when creating buckets. It only considers row position. Two employees with identical salaries could end up in different buckets if they fall on a boundary. If you need value-aware bucketing, use PERCENT_RANK or CUME_DIST instead.

SELECT
  customer_name,
  total_spent,
  ___(___) OVER (
    ORDER BY total_spent DESC
  ) AS tier
FROM customers

NTILE distributes rows as evenly as possible. If 10 rows are split into 4 buckets, two buckets get 3 rows and two get 2.

NTILE assigns buckets based on row position, not actual values. Two customers with identical spending could end up in different tiers at a boundary.

For value-aware bucketing where identical values should always share the same tier, use PERCENT_RANK or CUME_DIST instead.

Run different calculations in one query

Rank within category

RANK with PARTITION BY category

Percentage of total revenue

SUM with UNBOUNDED frame

Month-over-month growth

LAG for period comparison

Quartile placement

NTILE for percentile bucketing

There is no named WINDOW clause. You must write the full OVER specification for each window function. This means you will see multiple OVER clauses in a single SELECT, each potentially with different partitioning, ordering, and framing.

The first RANK partitions by category so each category has its own ranking. The second RANK has no partition, ranking all products together. Winter Jacket is ranked 1st in Clothing but 2nd overall.

This single query produces three different analytical perspectives: a department-level running total, month-over-month comparison via LAG, and a company-wide revenue tier via NTILE. Each OVER clause is tailored to its specific purpose.

When multiple window functions share the same OVER clause (same PARTITION BY, same ORDER BY, same frame), the query optimizer can often compute them in a single pass over the data. The database sorts the data once and evaluates all matching window functions together.

When window functions have different OVER clauses, the database may need to sort the data multiple times, once for each distinct window specification. On large datasets, each additional sort is expensive. This is why grouping window functions by their OVER clause is good practice.

SELECT
  bill_date,
  amount,
  ___(___, 1) OVER (
    ___ bill_date
  ) AS prev_amount,
  amount - ___(amount, 1) OVER (
    ORDER BY bill_date
  ) AS mom_change
FROM cloud_costs

Combining LAG for period comparison with NTILE for segmentation in the same query is a common analytics pattern because it simultaneously shows how a row changed from the previous period and where it ranks relative to all other rows.

Each window function in a SELECT list is evaluated independently against its own OVER clause, which means the database may sort the data multiple times if the specifications differ, so aligning OVER clauses where possible improves query performance on large datasets.