Window Functions

Window functions compute values across related rows while still returning each row.

Unlike GROUP BY, they do not collapse rows.

Core Concepts

Window component	Purpose
PARTITION BY	Group rows for separate calculations
ORDER BY (in window)	Define row sequence inside partition
Window function	ROW_NUMBER, SUM(...) OVER, etc.

ROW_NUMBER

SELECT
  category,
  name,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY price DESC
  ) AS rn
FROM products_win
ORDER BY category, rn;

category	name	price	rn
hardware	Pro Keyboard	150.0	1
hardware	Keyboard	100.0	2
hardware	Mouse	50.0	3
stationery	Pen Set	40.0	1
stationery	Notebook	20.0	2

Running Total

SELECT
  payment_id,
  amount,
  SUM(amount) OVER (
    ORDER BY payment_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM payments_agg
ORDER BY payment_id;

payment_id	amount	running_total
1	120.0	120.0
2	80.0	200.0
3	60.0	260.0
4	70.0	330.0
5	100.0	430.0

Code Examples

-- Setup data for window examples.
CREATE TABLE products_win (
  product_id INTEGER PRIMARY KEY,
  category   TEXT NOT NULL,
  name       TEXT NOT NULL,
  price      REAL NOT NULL
);

INSERT INTO products_win (category, name, price)
VALUES
  ('hardware', 'Mouse', 50.0),
  ('hardware', 'Keyboard', 100.0),
  ('hardware', 'Pro Keyboard', 150.0),
  ('stationery', 'Notebook', 20.0),
  ('stationery', 'Pen Set', 40.0);

Expected output
Table created and five rows inserted.

-- Compare each product price to category average.
SELECT
  category,
  name,
  price,
  AVG(price) OVER (PARTITION BY category) AS category_avg
FROM products_win
ORDER BY category, price DESC;

category	name	price	category_avg
hardware	Pro Keyboard	150.0	100.0
hardware	Keyboard	100.0	100.0
hardware	Mouse	50.0	100.0
stationery	Pen Set	40.0	30.0
stationery	Notebook	20.0	30.0

SQLite-Specific Nuances

SQLite Nuance

Window functions are available in SQLite 3.25.0 and newer.

Verify your version if queries fail with window-function syntax errors.

Common Pitfalls / Best Practices

Pitfall

Confusing ORDER BY inside OVER(...) with final result ordering.

They serve different purposes.

Best Practice

Use window functions for per-row analytics and keep an explicit final ORDER BY for output presentation.

Quick Challenge

Rank products within each category by ascending price (1 = cheapest).

View Solution

SELECT
  category,
  name,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY price ASC
  ) AS price_rank
FROM products_win
ORDER BY category, price_rank;

category	name	price	price_rank
hardware	Mouse	50.0	1
hardware	Keyboard	100.0	2
hardware	Pro Keyboard	150.0	3
stationery	Notebook	20.0	1
stationery	Pen Set	40.0	2