Looking for a structured path? Browse all SQL lessons.

Maintained by
Learning Platform content team
Reviewed by
Learning Platform source and executable-example contract

SQL window functions: ranking and running totals

GROUP BY collapses many input rows into one result row per group. A window function calculates across related rows while keeping each detail row visible.

Rank rows inside each group

WITH scores(team, player, points) AS (
  VALUES
    ('red', 'Ada', 12),
    ('red', 'Linus', 9),
    ('blue', 'Grace', 15),
    ('blue', 'Ken', 11)
)
SELECT
  team,
  player,
  points,
  ROW_NUMBER() OVER (
    PARTITION BY team
    ORDER BY points DESC
  ) AS team_rank
FROM scores
ORDER BY team, team_rank;

Expected rows include Grace | 15 | 1, Ken | 11 | 2, Ada | 12 | 1, and Linus | 9 | 2 within their teams.

PARTITION BY team restarts the calculation for each team. The window's ORDER BY controls ranking; the query's final ORDER BY controls display.

Running total with an explicit frame

WITH sales(day, amount) AS (
  VALUES ('2026-08-01', 10), ('2026-08-02', 7), ('2026-08-03', 5)
)
SELECT
  day,
  amount,
  SUM(amount) OVER (
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Writing the ROWS frame explicitly avoids surprising peer-row behavior when multiple records share the same ordering value.

Choose the ranking function deliberately

  • ROW_NUMBER() always produces unique positions.
  • RANK() gives ties the same rank and leaves gaps.
  • DENSE_RANK() gives ties the same rank without gaps.

Failure modes

A missing ORDER BY makes row numbering nondeterministic. Filtering a window result in the same query level is not portable; calculate it in a CTE or subquery, then filter the alias outside.

Run the queries in the SQL playground, then work through Window Functions and Aggregation.

Official references