Window Functions

Aggregate functions like SUM and COUNT collapse multiple rows into one. But what if you want to calculate a running total, rank employees by salary within each department, or compare each row to the next one — all without losing any rows? That's exactly what window functions do. They perform calculations across a set of rows related to the current row, while keeping every row in the result.

What You'll Learn

By the end of this lesson you will write one query that gives every row in a sales table a rank inside its own region and a running total alongside it, and you will be able to say in advance how many rows come back — which is the whole point, because unlike a GROUP BY the answer is always "all of them". The second skill is sharper and it is what most of this lesson is really about: choosing between ROW_NUMBER, RANK and DENSE_RANK when two rows tie, because all three are correct for different questions and only one of them is correct for yours.

This is the milestone the warehouse's leaderboard stands on. The capstone reports each category's best-selling products and a month-by-month running total, and every number in both comes from a window function rather than a second query. You arrive here able to collapse rows into group totals with GROUP BY and to read the group cardinality off a query (Aggregation and Grouping); what is new is getting the same arithmetic without giving up the individual rows.

The OVER Clause

Every window function uses the OVER() clause to define which rows the function should consider. Without OVER, you get a regular aggregate. With OVER, you get a window function.

-- Create a sales table
CREATE TABLE sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    amount REAL,
    sale_date TEXT
);

INSERT INTO sales (id, salesperson, region, amount, sale_date)
VALUES
    (1, 'Alice', 'North', 5000, '2025-01-05'),
    (2, 'Bob', 'South', 4500, '2025-01-08'),
    (3, 'Alice', 'North', 3200, '2025-01-12'),
    (4, 'Carol', 'North', 2800, '2025-01-15'),
    (5, 'Bob', 'South', 6100, '2025-01-18'),
    (6, 'David', 'South', 7200, '2025-01-20'),
    (7, 'Carol', 'North', 3900, '2025-01-22'),
    (8, 'David', 'South', 4800, '2025-01-25');

-- Compare: regular aggregate vs window function
-- Regular aggregate: one row total
SELECT SUM(amount) AS total_sales FROM sales;

-- Window function: every row kept, total shown alongside each
SELECT
    salesperson,
    amount,
    sale_date,
    SUM(amount) OVER () AS total_all_sales,
    ROUND(amount * 100.0 / SUM(amount) OVER (), 1) AS pct_of_total
FROM sales
ORDER BY sale_date;

The OVER () with empty parentheses means "consider all rows." Each row retains its individual data while also showing the grand total.

PARTITION BY

PARTITION BY divides rows into groups (partitions) and applies the window function independently within each group. It's like GROUP BY, but without collapsing rows.

-- Create an employees table
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department TEXT NOT NULL,
    salary INTEGER,
    hire_date TEXT
);

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES
    (1, 'Alice', 'Engineering', 95000, '2020-03-15'),
    (2, 'Bob', 'Engineering', 88000, '2021-06-01'),
    (3, 'Grace', 'Engineering', 102000, '2018-07-14'),
    (4, 'Carol', 'Marketing', 72000, '2019-11-20'),
    (5, 'David', 'Marketing', 68000, '2022-01-10'),
    (6, 'Henry', 'Marketing', 78000, '2021-09-30'),
    (7, 'Eva', 'Sales', 65000, '2023-04-05'),
    (8, 'Frank', 'Sales', 71000, '2020-08-22');

-- Show each employee with their department's stats
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department), 0) AS diff_from_avg,
    COUNT(*) OVER (PARTITION BY department) AS dept_size
FROM employees
ORDER BY department, salary DESC;

Each partition (department) gets its own independent calculation. Alice's average is computed only across Engineering, Carol's only across Marketing, and so on.

ROW_NUMBER, RANK, and DENSE_RANK

These ranking functions assign a position to each row within a partition.

-- Create a scores table
CREATE TABLE exam_scores (
    id INTEGER PRIMARY KEY,
    student TEXT NOT NULL,
    subject TEXT NOT NULL,
    score INTEGER
);

INSERT INTO exam_scores (id, student, subject, score)
VALUES
    (1, 'Alice', 'Math', 95),
    (2, 'Bob', 'Math', 87),
    (3, 'Carol', 'Math', 95),
    (4, 'David', 'Math', 78),
    (5, 'Eva', 'Math', 87),
    (6, 'Alice', 'Science', 88),
    (7, 'Bob', 'Science', 92),
    (8, 'Carol', 'Science', 85),
    (9, 'David', 'Science', 92),
    (10, 'Eva', 'Science', 79);

-- Compare the three ranking functions
SELECT
    student,
    subject,
    score,
    ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC, student) AS row_num,
    RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS dense_rank
FROM exam_scores
ORDER BY subject, score DESC, student;

The differences, read off the Math partition above, whose five scores are 95, 95, 87, 87, 78:

• ROW_NUMBER() — always unique, so the two 95s get 1 and 2 even though nothing separates them. The Math column reads 1, 2, 3, 4, 5.
• RANK() — ties share a rank, and the next rank skips by however many rows tied. Math reads 1, 1, 3, 3, 5: two rows took positions 1 and 2, so the next distinct score starts at 3.
• DENSE_RANK() — ties share a rank and nothing is skipped. Math reads 1, 1, 2, 2, 3, which counts distinct scores rather than positions.

Note the , student added to ROW_NUMBER's ORDER BY and to the final ORDER BY. Without it the two tied 95s could be numbered either way round, and SQLite does not promise which — a report that renumbers itself between runs is not a report. Whenever you use ROW_NUMBER, order by something that cannot tie.

CREATE TABLE heats (swimmer TEXT NOT NULL, secs INTEGER NOT NULL);
INSERT INTO heats VALUES ('Ada',52),('Bo',55),('Cy',52),('Di',58),('Eve',55);
SELECT swimmer, secs,
RANK() OVER (ORDER BY secs) AS rank_place,
DENSE_RANK() OVER (ORDER BY secs) AS dense_place
FROM heats
ORDER BY secs, swimmer;

LAG and LEAD

LAG looks at previous rows; LEAD looks at following rows. These are invaluable for comparing consecutive records.

-- Create a monthly revenue table
CREATE TABLE monthly_revenue (
    id INTEGER PRIMARY KEY,
    month TEXT NOT NULL,
    revenue REAL
);

INSERT INTO monthly_revenue (id, month, revenue)
VALUES
    (1, '2024-07', 42000),
    (2, '2024-08', 45500),
    (3, '2024-09', 38000),
    (4, '2024-10', 51000),
    (5, '2024-11', 48500),
    (6, '2024-12', 62000),
    (7, '2025-01', 55000),
    (8, '2025-02', 58500);

-- Compare each month to the previous and next month
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_rev,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_rev,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS month_over_month,
    CASE
        WHEN LAG(revenue, 1) OVER (ORDER BY month) IS NULL THEN NULL
        WHEN revenue > LAG(revenue, 1) OVER (ORDER BY month) THEN 'Up'
        WHEN revenue < LAG(revenue, 1) OVER (ORDER BY month) THEN 'Down'
        ELSE 'Flat'
    END AS trend
FROM monthly_revenue
ORDER BY month;

LAG(column, n) looks back n rows (default 1). LEAD(column, n) looks ahead n rows. The first row has no LAG value and the last row has no LEAD value — both return NULL.

Running Totals and Moving Averages

Window functions can compute cumulative values using a frame specification with ROWS BETWEEN.

-- Create a daily sales table
CREATE TABLE daily_sales (
    id INTEGER PRIMARY KEY,
    sale_date TEXT NOT NULL,
    amount REAL
);

INSERT INTO daily_sales (id, sale_date, amount)
VALUES
    (1, '2025-01-01', 1200),
    (2, '2025-01-02', 800),
    (3, '2025-01-03', 1500),
    (4, '2025-01-04', 2100),
    (5, '2025-01-05', 900),
    (6, '2025-01-06', 1800),
    (7, '2025-01-07', 2400),
    (8, '2025-01-08', 1100),
    (9, '2025-01-09', 1600),
    (10, '2025-01-10', 2000);

-- Running total (cumulative sum)
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_sales
ORDER BY sale_date;

-- 3-day moving average
SELECT
    sale_date,
    amount,
    ROUND(AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0) AS moving_avg_3day
FROM daily_sales
ORDER BY sale_date;

Frame specifications control which rows the function considers relative to the current row:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — from the start up to now (running total)
• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — the current row and the 2 before it (3-day window)

A frame is a request rather than a guarantee, and the interesting question is what happens at the very start of a partition, where a window that reaches backwards has nothing to reach into. Work that out on the block below before reading on.

CREATE TABLE readings (day TEXT NOT NULL, mm INTEGER NOT NULL);
INSERT INTO readings VALUES ('2025-03-01',4),('2025-03-02',10),('2025-03-03',1),('2025-03-04',7);
SELECT day, mm,
SUM(mm) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_sum
FROM readings
ORDER BY day;

Now read the first two rows of the moving average in the fence above with that in mind. On 2025-01-01 there are no preceding rows at all, so the frame holds one row and the three-day average is that day's own figure, 1200.0; on 2025-01-02 the frame holds two rows and averages 1200 and 800 into 1000.0. Only from the third row on does the window contain the three days the column name promises. A frame never invents rows to fill itself.

Run the running-total query again with one change: replace ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW with ROWS BETWEEN 1 PRECEDING AND CURRENT ROW. Predict first — the column stops being cumulative and becomes a two-day total, so 2025-01-03 should fall from 3500 to 2300. Then make a second change: in the moving-average query, replace the whole frame with ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING so the window looks forward instead of back, and rerun. Predict that one too: the short rows move from the top of the result to the bottom, because now it is the last rows that have nothing left to reach for, and the final day reports its own 2000.0 as a three-day average. Note that you have to replace both ends of the frame — writing ROWS BETWEEN 2 FOLLOWING AND CURRENT ROW names a window that ends before it starts, and SQLite rejects it with 'Error: unsupported frame specification'.

Practical Example: Sales Leaderboard

Combining multiple window functions to build a comprehensive report.

-- Create a quarterly sales table
CREATE TABLE quarterly_sales (
    id INTEGER PRIMARY KEY,
    salesperson TEXT NOT NULL,
    region TEXT NOT NULL,
    quarter TEXT NOT NULL,
    revenue REAL
);

INSERT INTO quarterly_sales (id, salesperson, region, quarter, revenue)
VALUES
    (1, 'Alice', 'North', 'Q1', 85000),
    (2, 'Alice', 'North', 'Q2', 92000),
    (3, 'Bob', 'North', 'Q1', 78000),
    (4, 'Bob', 'North', 'Q2', 81000),
    (5, 'Carol', 'South', 'Q1', 95000),
    (6, 'Carol', 'South', 'Q2', 88000),
    (7, 'David', 'South', 'Q1', 72000),
    (8, 'David', 'South', 'Q2', 96000);

-- Build a comprehensive leaderboard
SELECT
    salesperson,
    region,
    quarter,
    revenue,
    RANK() OVER (PARTITION BY quarter ORDER BY revenue DESC) AS overall_rank,
    RANK() OVER (PARTITION BY region, quarter ORDER BY revenue DESC) AS region_rank,
    SUM(revenue) OVER (PARTITION BY salesperson ORDER BY quarter) AS cumulative_revenue,
    revenue - LAG(revenue) OVER (PARTITION BY salesperson ORDER BY quarter) AS quarter_change,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (PARTITION BY quarter), 1) AS pct_of_quarter
FROM quarterly_sales
ORDER BY quarter, overall_rank, salesperson;

Note the third sort key. overall_rank is a RANK, so two salespeople who tie on revenue within a quarter receive the same rank, and sorting by quarter and rank alone would leave their relative order unspecified — the same trap the ROW_NUMBER section warned about, arriving this time through the sort rather than through the ranking function. Nobody ties in this particular data, which is exactly why the missing key would have gone unnoticed until it mattered.

Putting a Ranking Query Together

A window function is not a clause of its own — it lives inside the SELECT list, and everything else about the query keeps the ordinary clause order. That has one consequence worth pinning down before you write your own: the window function runs after WHERE, so a row the WHERE clause removed never existed as far as the ranking is concerned.

CREATE TABLE runs (id INTEGER PRIMARY KEY, athlete TEXT NOT NULL, meters INTEGER NOT NULL);
INSERT INTO runs VALUES (1,'Ada',800),(2,'Bo',1200),(3,'Cy',400),(4,'Di',1200);

The four athletes above are already loaded. Cy ran 400 metres, and the query below filters to 800 and over.

A Ranking That Runs and Is Still Wrong

The last section made ranking look mechanical. It is not, because the three ranking functions all run, all exit cleanly, and all return plausible numbers — only one of them answers your question.

CREATE TABLE entries (id INTEGER PRIMARY KEY, player TEXT NOT NULL, points INTEGER NOT NULL);
INSERT INTO entries VALUES (1,'Ada',90),(2,'Bo',75),(3,'Cy',90),(4,'Di',60),(5,'Eve',75);
SELECT player, points,
  ROW_NUMBER() OVER (ORDER BY points DESC) AS place
FROM entries
ORDER BY place;

5 row(s) affected
player | points | place
-----------------------
Ada | 90 | 1
Cy | 90 | 1
Bo | 75 | 2
Eve | 75 | 2
Di | 60 | 3

Try It Yourself

The build below is the warehouse leaderboard in miniature: one query that keeps every order row while adding two window columns to it. The starter runs and prints a table of the right shape, but both of its computed columns are wrong on purpose — the total is not cumulative and the rank is a hard-coded zero. Work TODO 1 and TODO 2 until the output matches the spec.

CREATE TABLE orders (id INTEGER PRIMARY KEY, region TEXT NOT NULL, month TEXT NOT NULL, cents INTEGER NOT NULL);
INSERT INTO orders VALUES
(1,'north','2025-01',12000),(2,'north','2025-02',9000),(3,'north','2025-03',15000),
(4,'south','2025-01',8000),(5,'south','2025-02',20000),(6,'south','2025-03',8000);
-- TODO 1: make running_cents cumulative within each region, in month order.
--         North should read 12000, then 21000, then 36000.
-- TODO 2: make region_rank rank the months of each region by cents, largest first,
--         with tied months sharing a place. South's two 8000 months both rank 2.
--         Remember that a GROUP BY collapses rows and a window function does not.
SELECT region, month, cents,
  SUM(cents) AS running_cents,
  0 AS region_rank
FROM orders
GROUP BY region, month
ORDER BY region, month;

6 row(s) affected
region | month | cents | running_cents | region_rank
----------------------------------------------------
north | 2025-01 | 12000 | 12000 | 2
north | 2025-02 | 9000 | 21000 | 3
north | 2025-03 | 15000 | 36000 | 1
south | 2025-01 | 8000 | 8000 | 2
south | 2025-02 | 20000 | 28000 | 1
south | 2025-03 | 8000 | 36000 | 2

Once it passes, try two variations and predict each before running. First, change the running total's frame to ROWS BETWEEN 1 PRECEDING AND CURRENT ROW — north's last month should fall from 36000 to 24000, because it is now a two-month total rather than a cumulative one. Second, remove PARTITION BY region from the rank only: south's 20000 should become rank 1 across the whole table and north's 12000 should drop to 3, because the ranking is now competing against every region at once.

CREATE TABLE scores (player TEXT NOT NULL, pts INTEGER NOT NULL);
INSERT INTO scores VALUES ('Ada',90),('Bo',75),('Cy',90),('Di',60);

SELECT player, pts, RANK() OVER (ORDER BY pts DESC) AS place
FROM scores ORDER BY place, player;

SELECT s.player, s.pts,
(SELECT COUNT(*) FROM scores b WHERE b.pts > s.pts) + 1 AS place
FROM scores s ORDER BY place, s.player;

Key Takeaways

• Window functions perform calculations across related rows without collapsing them
• OVER() defines the window; PARTITION BY divides it into groups
• ROW_NUMBER, RANK, and DENSE_RANK assign positions within partitions
• LAG looks at previous rows; LEAD looks at subsequent rows
• Frame specifications (ROWS BETWEEN) control running totals and moving averages
• Window functions are ideal for rankings, comparisons, and cumulative calculations

Pro Tip: When you need the same window definition multiple times, SQLite supports the WINDOW clause to define it once: WINDOW w AS (PARTITION BY department ORDER BY salary DESC) and then use OVER w in each function. This reduces repetition and makes your query cleaner.

Next Steps

Window functions round out your querying skills. But writing great queries is only half the picture — the other half is having well-structured tables to query against. The next lesson covers database design: normalization, entity-relationship modeling, and the art of choosing primary keys, foreign keys, and constraints. Good design decisions made before the first INSERT prevent entire categories of bugs and performance problems down the road.