Window Functions

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

Unlike GROUP BY, they do not collapse rows.

Core Concepts

flowchart LR
  A[Input rows] --> B[Define window: PARTITION / ORDER]
  B --> C[Compute per-row analytic value]
  C --> D[Rows preserved with extra columns]

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