Database Management · Intermediate level

Intermediate level · Module 2: Advanced SQL Queries · Lesson 3 of 3

Window Functions

Rank rows, calculate running totals and compare rows with their group without collapsing them.

In this lesson you will

  • Use ROW_NUMBER, RANK and aggregate window functions
  • Use PARTITION BY and ORDER BY within windows

Key concepts

window functionOVER (PARTITION BY … ORDER BY …)ROW_NUMBER and RANKrunning total

The lesson

Window functions calculate across related rows while keeping every row, unlike GROUP BY which collapses them. SUM(amount) OVER (PARTITION BY account ORDER BY tx_date) gives each transaction a running balance for its own account.

ROW_NUMBER() numbers rows within a partition; RANK() gives equal values the same rank. Combined with a CTE, they answer 'top three per group' questions: rank within each county, then keep ranks 1 to 3.

LAG and LEAD read the previous or next row, which is useful for comparing each month with the one before.

Step by step

  1. Calculate a running balance per account.
  2. Rank villages by households reached within each county.
  3. Keep the top three per county with a CTE.
  4. Compare each month with the previous one using LAG.
Code example

Running wallet balances with a window function

Each transaction keeps its own row while SUM … OVER adds up a running balance per account.

SQLrunning_balance.sql
CREATE TABLE wallet_transactions (  id INTEGER PRIMARY KEY,  account TEXT NOT NULL,  tx_date TEXT NOT NULL,  amount INTEGER NOT NULL); INSERT INTO wallet_transactions (account, tx_date, amount) VALUES  ('Akol', '2026-04-01', 50000), ('Akol', '2026-04-03', -12000),  ('Akol', '2026-04-07', -8000), ('Mary', '2026-04-02', 20000),  ('Mary', '2026-04-05', 15000), ('Mary', '2026-04-06', -30000); -- Running balance per account, in date orderSELECT account,       tx_date,       amount,       SUM(amount) OVER (         PARTITION BY account         ORDER BY tx_date       ) AS balanceFROM wallet_transactionsORDER BY account, tx_date;

Output

account  tx_date     amount  balance
-------  ----------  ------  -------
Akol     2026-04-01   50000    50000
Akol     2026-04-03  -12000    38000
Akol     2026-04-07   -8000    30000
Mary     2026-04-02   20000    20000
Mary     2026-04-05   15000    35000
Mary     2026-04-06  -30000     5000

How it works

SUM(amount) OVER (PARTITION BY account ORDER BY tx_date) adds up amounts in date order and restarts for each account, so Mary's balance starts again at 20,000. Unlike GROUP BY, the window function keeps every transaction row. The same query works in PostgreSQL. Output produced by running the script in SQLite; data is invented.

Key concepts

window functionPARTITION BYORDER BY inside OVERrunning total

Practice activity

Answer the top-three-villages and running-total questions from the practical activity.

Expected result: Correct rankings per county and running totals that match manual calculations.

Check your understanding

  1. How does a window function differ from GROUP BY?

    Show answer

    It calculates across related rows but keeps every row.

  2. What does PARTITION BY do in a window?

    Show answer

    Restarts the calculation for each group.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.