Help improve SovranCode?

Google Analytics can measure anonymous usage after you choose Allow. Essential account and progress features work either way.

SovranCode
SQL: Query, Model, and Analyze Data Window Functions
This device
Course contentsWindow Functions · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
SQL: Query, Model, and Analyze Data12 complete · 20 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsPlannedSelf joins and many-to-many dataPlannedSubqueries and correlated subqueriesPlannedUNION, INTERSECT, and EXCEPTPlanned

Designing reliable schemas

CREATE TABLE and schema designPlannedConstraints and data integrityPlannedNormalization and intentional denormalizationPlannedViews and materialized viewsPlanned

Writing and protecting data

INSERT, UPDATE, and DELETEPlannedUpserts and conflict handlingPlannedTransactions and savepointsPlannedIsolation, locks, and concurrencyPlanned

Performance and administration

Indexes and access pathsPlannedEXPLAIN and query plansPlannedQuery tuning patternsPlannedBackups, restores, and migrationsPlanned

Security and production workflow

Users, roles, and least privilegePlannedPreventing SQL injectionPlannedStored procedures, functions, and triggersPlannedProduction data projectPlanned
PREVIOUS LESSONGROUP BY and HAVING
NEXT LESSONCommon Table Expressions
Reports, aggregation, and analytics · Lesson 11 135 min

Window Functions

A grouped aggregate collapses many source rows into one result per group. A window function computes across related rows while keeping each source row visible. That makes it useful for ranks, running totals, and comparisons with neighboring rows.

What you will leave with

You will read OVER, PARTITION BY, and window ORDER BY; distinguish ranking functions; specify running and moving frames; compare a row with its predecessor; and filter ranked results safely.

A window adds context without collapsing rows

Rows remainSee each sale alongside its calculated context.
PartitionChoose which rows share a calculation.
Order and frameDefine sequence and a calculation boundary.
AuditCheck ties, edges, and result order.
SOURCE8 sales4 North · 4 South

Every sale has an ID, date, region, and amount.

GROUP BY2 rowsone per region

Individual sale IDs disappear.

WINDOW8 rowsone per sale

Each row can show its region total.

GROUP BY versus OVER

The grouped query returns two regional totals: North has 5,500 cents and South has 8,500. The window version repeats the relevant regional total beside each of the eight sales. OVER (PARTITION BY region) defines a window for each region, but it does not merge rows.

SQL BROWSER RUNNER

Review a grouped baseline

See how GROUP BY collapses sales into two region rows.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT region, SUM(amount_cents) AS region_cents
FROM sales GROUP BY region ORDER BY region;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Predict the number of output rows and both region totals.

SQL BROWSER RUNNER

Keep each sale beside its region total

Use SUM OVER with a region partition.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, amount_cents,
       SUM(amount_cents) OVER (PARTITION BY region) AS region_cents
FROM sales ORDER BY sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Remove PARTITION BY region. What total appears beside every sale?

ROW_NUMBER gives a unique position

ROW_NUMBER() numbers rows inside each partition. The number restarts at one for South. We order by amount descending and then sale ID, so equal 2,000-cent North sales receive deterministic but different positions. A window ORDER BY defines the calculation order; the final query ORDER BY controls display order.

SQL BROWSER RUNNER

Number sales within each region

Rank the largest sale first and break ties by sale ID.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, amount_cents,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount_cents DESC, sale_id) AS row_number
FROM sales ORDER BY region, row_number;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find both 2,000-cent North rows and compare their positions.

RANK and DENSE_RANK preserve ties differently

For North, the two 2,000-cent sales tie for rank one. RANK() then skips to three; DENSE_RANK() goes to two. Both use amount alone so equal amounts remain peers. Add sale ID to the ranking order only if you intend to remove those ties.

SQL BROWSER RUNNER

Compare tied ranks

See how RANK and DENSE_RANK treat equal amounts.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, amount_cents,
       RANK() OVER (PARTITION BY region ORDER BY amount_cents DESC) AS rank_with_gaps,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount_cents DESC) AS dense_rank
FROM sales ORDER BY region, amount_cents DESC, sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Inspect the two South sales worth 1,500 cents. What are their ranks?

An explicit frame makes a running total

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW sums from the first regional sale through the current sale. The North running totals are 1,000, 3,000, 5,000, and 5,500 cents. A unique tie-breaker in window ordering makes row-by-row results deterministic. Explicit ROWS avoids default-frame surprises when order values tie.

SQL BROWSER RUNNER

Calculate a running regional total

Use an explicit ROWS frame ordered by date and sale ID.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, sold_on, amount_cents,
       SUM(amount_cents) OVER (
         PARTITION BY region ORDER BY sold_on, sale_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM sales ORDER BY region, sold_on, sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify the last running total equals each region's full total.

A bounded frame creates a moving average

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW considers at most two sales in the same region: this sale and its predecessor. At a partition start, only one row exists, so the first moving average equals the first amount. This is a two-sale average, not a two-day calendar average.

SQL BROWSER RUNNER

Average the current and previous sale

Calculate a two-row moving average in each region.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, amount_cents,
       ROUND(AVG(amount_cents) OVER (
         PARTITION BY region ORDER BY sold_on, sale_id
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ), 2) AS two_sale_average_cents
FROM sales ORDER BY region, sold_on, sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change 1 PRECEDING to 2 PRECEDING and identify where the average changes.

LAG reads a previous row without merging it

LAG(amount_cents) reads the prior sale in the same region according to the window order. The first row of each region has no predecessor, so its previous amount and difference are NULL. A missing predecessor is not a zero-dollar sale.

SQL BROWSER RUNNER

Compare each sale with its predecessor

Use LAG and subtract the previous amount.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, amount_cents,
       LAG(amount_cents) OVER (PARTITION BY region ORDER BY sold_on, sale_id) AS previous_cents,
       amount_cents - LAG(amount_cents) OVER (PARTITION BY region ORDER BY sold_on, sale_id) AS change_cents
FROM sales ORDER BY region, sold_on, sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Find the North sale whose change is negative 1,500 cents.

LAST_VALUE needs the intended frame

LAST_VALUE reads the last row of its window frame, which may end at the current row by default. To show the final sale amount for every row in a region, extend the frame through UNBOUNDED FOLLOWING. North then shows 500 cents and South 2,500 on each of their rows.

SQL BROWSER RUNNER

Read the final sale in each partition

Use a full-partition frame with LAST_VALUE.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

SELECT sale_id, region, sold_on,
       LAST_VALUE(amount_cents) OVER (
         PARTITION BY region ORDER BY sold_on, sale_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS final_region_sale_cents
FROM sales ORDER BY region, sold_on, sale_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Change UNBOUNDED FOLLOWING to CURRENT ROW and compare the result.

Common window-function mistakes

OVER ()

Forgotten partition

Without PARTITION BY, every row belongs to one whole-result window.

ORDER BY amount only

Unstable row position

Add a stable tie-breaker when ROW_NUMBER or ROWS needs a precise sequence.

WHERE ROW_NUMBER() = 1

Wrong query stage

Calculate the window value in a subquery or CTE, then filter its output.

LAST_VALUE without frame

Unexpected endpoint

Write the frame that actually reaches the final partition row.

Independent lab: top sale per region

Return one highest sale per region with its sale ID, amount, and region total. The starter ranks rows in a CTE, then filters position = 1 outside it. North returns sale 2 at 2,000 cents with a 5,500-cent region total; South returns sale 5 at 3,000 cents with an 8,500-cent total. North has a tied top amount, so sale ID deliberately chooses one winner.

SQL BROWSER RUNNER

Audit a regional top-sale report

Combine ROW_NUMBER and a partition total, then filter the computed rank.

CREATE TABLE sales (
  sale_id INTEGER PRIMARY KEY,
  region TEXT NOT NULL,
  amount_cents INTEGER NOT NULL,
  sold_on TEXT NOT NULL
);

INSERT INTO sales (sale_id, region, amount_cents, sold_on) VALUES
  (1, 'North', 1000, '2026-09-01'),
  (2, 'North', 2000, '2026-09-02'),
  (3, 'South', 1500, '2026-09-02'),
  (4, 'North', 2000, '2026-09-03'),
  (5, 'South', 3000, '2026-09-04'),
  (6, 'North', 500,  '2026-09-05'),
  (7, 'South', 1500, '2026-09-06'),
  (8, 'South', 2500, '2026-09-07');

-- Largest sale in each region, preserving the original sale row.
WITH ranked_sales AS (
  SELECT sale_id, region, amount_cents, sold_on,
         ROW_NUMBER() OVER (
           PARTITION BY region ORDER BY amount_cents DESC, sale_id
         ) AS position,
         SUM(amount_cents) OVER (PARTITION BY region) AS region_cents
  FROM sales
)
SELECT region, sale_id, amount_cents, region_cents
FROM ranked_sales
WHERE position = 1
ORDER BY region;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Verify both rows. Change ROW_NUMBER to RANK and explain how ties affect the result.

Lab review criteria

The ranking happens inside the CTE and the outer query filters its result. The partition total includes every regional sale, not just the winner. A deterministic tie-breaker controls which North sale represents the tied maximum.

Lesson review

Window functions preserve source rows while adding context. PARTITION BY defines independent groups, window ORDER BY defines sequence, and a frame defines which ordered rows feed a calculation. Ranking ties, partition edges, and final result order all deserve explicit decisions.

  • I can explain why an OVER result keeps the original row count.
  • I can compare ROW_NUMBER, RANK, and DENSE_RANK on ties.
  • I can write explicit running and moving ROWS frames.
  • I can interpret a NULL predecessor from LAG.
  • I can filter a computed window result in an outer query.
KNOWLEDGE CHECK

Check window-function reasoning

Answer all ten questions, then revisit any example whose tie or frame surprised you.

01How many rows does SUM(amount_cents) OVER (PARTITION BY region) return for eight sales?
02What does PARTITION BY region change?
03What does ROW_NUMBER do to equal-amount rows with a sale-ID tie-breaker?
04For tied first-place sales, what position follows under RANK and DENSE_RANK?
05Why include sale_id after sold_on in a running-total window order?
06What does ROWS BETWEEN 1 PRECEDING AND CURRENT ROW describe?
07What does LAG return for the first sale in a region?
08Why extend LAST_VALUE's frame to UNBOUNDED FOLLOWING?
09How should you keep only rows with ROW_NUMBER() = 1?
10Why does the lab use sale_id to break a North top-amount tie?
PREVIOUS LESSONGROUP BY and HAVING
NEXT LESSONCommon Table Expressions
ON THIS PAGEWindow FunctionsLesson mapGROUP BY vs OVERROW_NUMBERRanking tiesRunning totalsMoving averagesLAGFrame boundaryCommon mistakesIndependent labLesson reviewKnowledge check
Course contents