SQL window functions: ranking, comparisons and running totals

Choose the partition, order and frame

• PARTITION BY: restart a calculation for each group, such as a department.
• ORDER BY inside OVER: define the sequence used by the calculation.
• ROWS frame: specify which row positions an aggregate includes.

A window calculation preserves the rows presented to it; an ordinary grouped aggregate combines them. Filters and grouping earlier in the query still affect the input. Use an outer query or CTE to filter a calculated rank. Use a final ORDER BY when output order matters.

1. Rank salaries and handle ties deliberately

Run this self-contained example. The two Data employees with $90,000 salaries should tie for salary rank, while employee ID gives ROW_NUMBER a stable tie-breaker.

WITH scores(employee_id, department, salary_usd) AS (
  VALUES (1, 'Data', 90000), (2, 'Data', 90000),
         (3, 'Data', 80000), (4, 'BI', 85000)
)
SELECT employee_id, department, salary_usd,
  ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY salary_usd DESC, employee_id
  ) AS row_num,
  RANK() OVER (
    PARTITION BY department ORDER BY salary_usd DESC
  ) AS salary_rank,
  DENSE_RANK() OVER (
    PARTITION BY department ORDER BY salary_usd DESC
  ) AS dense_salary_rank
FROM scores
ORDER BY department, row_num;

For exactly one employee per department, filter row_num = 1 in an outer query. For everyone tied at the highest salary, filter salary_rank = 1. Do not add employee ID to the salary ranking order if equal salaries must remain tied. This example has no null salaries; define how unknown salaries should be handled before ranking real data.

When should you use ROW_NUMBER, RANK or DENSE_RANK?

Use ROW_NUMBER for a fixed number of rows, RANK for competition-style positions with ties and gaps, and DENSE_RANK for distinct ordered levels without gaps. First decide whether your question asks for people, positions or salary levels. These produce different answers when values tie.

For the Data salaries above, the top two people are employees 1 and 2 under the employee-ID tie-break rule. The top two distinct salary levels include all three employees. A filter on RANK ≤ 2 returns only employees 1 and 2 because the next rank is 3.

The same distinction matters for customer history: “one latest record” needs an agreed tie-breaker, while “all events at the latest timestamp” may return several records. Work through the latest row per customer SQL example to test tied timestamps, missing dates and the difference between a current paid status and a historical paid event.

Does NTILE keep equal values in the same bucket?

No. NTILE balances row counts, so a boundary can split equal values. For six customers with spend of $100, $100, $100, $100, $50 and $10, NTILE(3) ordered by spend descending and unique customer ID assigns buckets 1, 1, 2, 2, 3, 3. All four $100 customers have equal spend, but they do not share one bucket.

A unique tie-breaker makes the assignment reproducible for the same input; it does not turn buckets into distinct value levels. Use an explicit threshold policy if equal spend must share a segment. DENSE_RANK preserves tied spend levels when ordered only by spend, but may produce more than three levels. Compare the tested US and UK customer-bucket example and the PostgreSQL window-function reference.

2. Compare adjacent months without dividing by zero

The sample contains one row per month, sorted by an ISO month-start value. LAG reads the preceding row and LEAD reads the following row; neither represents a forecast or target.

WITH monthly_revenue(month_start, revenue_usd) AS (
  VALUES ('2026-01-01', 1000.0), ('2026-02-01', 1200.0),
         ('2026-03-01', 0.0), ('2026-04-01', 500.0)
), compared AS (
  SELECT month_start, revenue_usd,
    LAG(revenue_usd) OVER (ORDER BY month_start) AS previous_revenue,
    LEAD(revenue_usd) OVER (ORDER BY month_start) AS next_revenue
  FROM monthly_revenue
)
SELECT *,
  ROUND(100.0 * (revenue_usd - previous_revenue)
    / NULLIF(previous_revenue, 0), 2) AS growth_pct
FROM compared
ORDER BY month_start;

Keep “no preceding observation” distinct from zero revenue. If a month is absent, the previous row may be more than a month earlier. Check that the previous period is the immediately preceding calendar month, or build a complete month series with an explicit missing-data rule. Do not turn an unreceived month into zero sales. For example, US revenue of $100 in January and $150 in March yields a 50% change between available rows, but February is unknown, so March month-over-month growth is unavailable. Run the tested LAG example with missing months and zero denominators to compare these cases. For separate products, include product in PARTITION BY. To show one year’s growth with January compared against December, calculate the window with December present and filter the display period afterwards.

3. Specify the running-total frame

This dataset has one non-null revenue value for every calendar day. The average appears only after seven observations are available.

WITH daily_sales(day, revenue_usd) AS (
  VALUES ('2026-09-01', 10.0), ('2026-09-02', 20.0),
         ('2026-09-03', 30.0), ('2026-09-04', 40.0),
         ('2026-09-05', 50.0), ('2026-09-06', 60.0),
         ('2026-09-07', 70.0), ('2026-09-08', 80.0)
)
SELECT day, revenue_usd,
  SUM(revenue_usd) OVER (
    ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_revenue,
  CASE WHEN COUNT(*) OVER (
    ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) = 7 THEN AVG(revenue_usd) OVER (
    ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) END AS full_week_average
FROM daily_sales
ORDER BY day;

Six preceding rows plus the current row is seven rows. It means seven calendar days here only because the dates are complete and unique. Aggregate multiple transactions into daily totals first. Add missing dates only after deciding whether absence means zero sales or unavailable data. AVG ignores null values, so a complete-row count alone does not establish seven known revenue values.

To test a missing calendar date, try the seven-day moving average with a complete calendar. The runnable SQLite example shows why seven available rows can span eight days and separates confirmed zero sales from an incomplete data feed.

An explicit ROWS frame also avoids the default ordered aggregate frame including other rows tied on the ordering value. If each transaction needs its own running total, put the timestamp and a stable, unique transaction ID in ORDER BY inside OVER, together with an explicit ROWS frame. A final ORDER BY only sorts the displayed result; it does not resolve ties inside the window.

Compare both results in the running-total exercise with tied timestamps: two orders at the same time produce peer totals of $300 and $300 under the default frame, versus $100 and $300 with a deterministic row-by-row order.

Practice: explain before you run