SQLite Window Functions: ROW_NUMBER, RANK, LAG and Running Totals (Tested)

Ninad Pathak

October 7, 2026

GROUP BY collapses rows into one per group, so it can’t show each month next to its region’s total. Window functions can, because they compute across rows without collapsing them. In this article I’ll walk you through the ones you’ll reach for, from running totals to the top row per group.

TLDR

A window function is any function with an OVER (…) clause. It returns a value for every row instead of one per group.
PARTITION BY splits rows into groups, and ORDER BY inside OVER sets the order within each group.

Running total: SUM(revenue) OVER (PARTITION BY region ORDER BY month).
rank() and row_number() number the rows in order. lag() and lead() read the previous and next row.
You can’t use a window function in WHERE. Wrap the query in a subquery and filter outside.

What are window functions in SQLite?

SQLite’s docs: “A window function is an SQL function where the input values are taken from a ‘window’ of one or more rows in the results set of a SELECT statement”, and “if a function has an OVER clause, then it is a window function” (SQLite window functions). Support “was first added to SQLite with release version 3.25.0 (2018-09-15)”, using PostgreSQL’s behaviour as the reference.

The difference from GROUP BY is easiest to see side by side:

sqlite> SELECT region, SUM(revenue) AS total FROM sales GROUP BY region;
region  total
------  -----
East    510
West    760
sqlite> SELECT month, region, revenue, SUM(revenue) OVER (PARTITION BY region) AS region_total FROM sales ORDER BY region, month;
month    region  revenue  region_total
-------  ------  -------  ------------
2026-01  East    120      510
2026-02  East    150      510
2026-03  East    90       510
2026-04  East    150      510
2026-01  West    200      760
2026-02  West    180      760
2026-03  West    210      760
2026-04  West    170      760

SQLite window function syntax

function_name(args) OVER (
  PARTITION BY column
  ORDER BY column
  frame_spec
)

Part	What it does	If you leave it out
`PARTITION BY`	Splits rows into independent groups	The whole result is one group
`ORDER BY`	Orders rows inside each group	Every row is a “peer” of every other
Frame (`ROWS`, `RANGE`, `GROUPS`)	Which rows around the current one count	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
`WINDOW w AS (…)`	Names a window so several functions can share it	Write the full `OVER (…)` each time

How to use a window function in SQLite

To show you how they work, I will use a sales table with four months of revenue for two regions:

CREATE TABLE sales (
  month TEXT NOT NULL,
  region TEXT NOT NULL,
  revenue INTEGER NOT NULL
);
INSERT INTO sales VALUES
  ('2026-01', 'East', 120), ('2026-02', 'East', 150), ('2026-03', 'East', 90),  ('2026-04', 'East', 150),
  ('2026-01', 'West', 200), ('2026-02', 'West', 180), ('2026-03', 'West', 210), ('2026-04', 'West', 170);

Step 1: Pick the function

For a running total, the function is the aggregate SUM(revenue).

Step 2: Add OVER with PARTITION BY and ORDER BY

PARTITION BY region restarts the total for each region. ORDER BY month adds the months up in order:

sqlite> SELECT region, month, revenue, SUM(revenue) OVER (PARTITION BY region ORDER BY month) AS running_total FROM sales ORDER BY region, month;
region  month    revenue  running_total
------  -------  -------  -------------
East    2026-01  120      120
East    2026-02  150      270
East    2026-03  90       360
East    2026-04  150      510
West    2026-01  200      200
West    2026-02  180      380
West    2026-03  210      590
West    2026-04  170      760

Step 3: Name the window if you reuse it

When several functions share a window, define it once with WINDOW w AS (…) right before ORDER BY and refer to it with OVER w. The ranking example below does this.

Examples

These examples run against the same eight rows.

Rank rows: ROW_NUMBER vs RANK vs DENSE_RANK

February and April tie at 150. row_number() still numbers them 1 and 2 (the month breaks the tie). rank() gives both 1 and skips to 3. dense_rank() gives both 1 and carries on with 2:

sqlite> SELECT region, month, revenue, row_number() OVER (ORDER BY revenue DESC, month) AS row_num, rank() OVER w AS rnk, dense_rank() OVER w AS dense FROM sales WHERE region = 'East' WINDOW w AS (ORDER BY revenue DESC) ORDER BY revenue DESC, month;
region  month    revenue  row_num  rnk  dense
------  -------  -------  -------  ---  -----
East    2026-02  150      1        1    1
East    2026-04  150      2        1    1
East    2026-01  120      3        3    2
East    2026-03  90       4        4    3

Top row per group

A window function can’t go in WHERE:

sqlite> SELECT region, month, revenue FROM sales WHERE row_number() OVER (PARTITION BY region ORDER BY revenue DESC) = 1;
Error: in prepare, misuse of window function row_number()
  SELECT region, month, revenue FROM sales WHERE row_number() OVER (PARTITION BY
                                   error here ---^

Compute it in a subquery, then filter outside. This returns each region’s best month:

sqlite> SELECT region, month, revenue FROM (SELECT region, month, revenue, row_number() OVER (PARTITION BY region ORDER BY revenue DESC, month) AS rn FROM sales) WHERE rn = 1;
region  month    revenue
------  -------  -------
East    2026-02  150
West    2026-03  210

Compare with the previous or next row: LAG and LEAD

lag() reads the previous row in the window, and lead() the next one. The first row has no previous value, so it’s NULL:

sqlite> SELECT region, month, revenue, lag(revenue) OVER w AS prev_month, revenue - lag(revenue) OVER w AS change, lead(revenue) OVER w AS next_month FROM sales WHERE region = 'West' WINDOW w AS (PARTITION BY region ORDER BY month) ORDER BY month;
region  month    revenue  prev_month  change  next_month
------  -------  -------  ----------  ------  ----------
West    2026-01  200                          180
West    2026-02  180      200         -20     210
West    2026-03  210      180         30      170
West    2026-04  170      210         -40

Moving average with a ROWS frame

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW averages the current month and the two before it:

sqlite> SELECT region, month, revenue, round(avg(revenue) OVER (PARTITION BY region ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS avg_3_months FROM sales WHERE region = 'East' ORDER BY month;
region  month    revenue  avg_3_months
------  -------  -------  ------------
East    2026-01  120      120.0
East    2026-02  150      135.0
East    2026-03  90       120.0
East    2026-04  150      130.0

First value and percentage of the group

first_value() returns the first row of each window. Dividing by a partition total gives each month’s share of its region:

sqlite> SELECT region, month, revenue, first_value(revenue) OVER (PARTITION BY region ORDER BY month) AS first_month, round(100.0 * revenue / SUM(revenue) OVER (PARTITION BY region), 1) AS pct_of_region FROM sales ORDER BY region, month;
region  month    revenue  first_month  pct_of_region
------  -------  -------  -----------  -------------
East    2026-01  120      120          23.5
East    2026-02  150      120          29.4
East    2026-03  90       120          17.6
East    2026-04  150      120          29.4
West    2026-01  200      200          26.3
West    2026-02  180      200          23.7
West    2026-03  210      200          27.6
West    2026-04  170      200          22.4

Split rows into buckets with NTILE

ntile(2) splits the rows into two equal groups by revenue:

sqlite> SELECT month, region, revenue, ntile(2) OVER (ORDER BY revenue DESC) AS half FROM sales ORDER BY revenue DESC, month, region;
month    region  revenue  half
-------  ------  -------  ----
2026-03  West    210      1
2026-01  West    200      1
2026-02  West    180      1
2026-04  West    170      1
2026-02  East    150      2
2026-04  East    150      2
2026-01  East    120      2
2026-03  East    90       2

Count only some rows with FILTER

FILTER (WHERE …) limits which rows an aggregate window function counts. Here it counts months at or above 150, cumulatively:

sqlite> SELECT month, region, revenue, COUNT(*) FILTER (WHERE revenue >= 150) OVER (PARTITION BY region ORDER BY month) AS months_150_plus FROM sales ORDER BY region, month;
month    region  revenue  months_150_plus
-------  ------  -------  ---------------
2026-01  East    120      0
2026-02  East    150      1
2026-03  East    90       1
2026-04  East    150      2
2026-01  West    200      1
2026-02  West    180      2
2026-03  West    210      3
2026-04  West    170      4

Edge cases

Ties in the default frame: with ORDER BY and no frame, SQLite uses RANGE, so rows with equal sort values (“peers”) are added together. Both 150 rows get 510 below. A ROWS frame adds them one at a time (SQLite window functions: frame specifications):

sqlite> SELECT month, revenue, SUM(revenue) OVER (ORDER BY revenue) AS range_default, SUM(revenue) OVER (ORDER BY revenue, month ROWS UNBOUNDED PRECEDING) AS rows_frame FROM sales WHERE region = 'East' ORDER BY revenue, month;
month    revenue  range_default  rows_frame
-------  -------  -------------  ----------
2026-03  90       90             90
2026-01  120      210            210
2026-02  150      510            360
2026-04  150      510            510

Window functions run after WHERE and grouping. That’s why a WHERE clause can’t use them.
Older SQLite: versions before 3.25.0 don’t have window functions. GROUPS frames and EXCLUDE need 3.28.0 or later (same docs).

Conclusion

Reach for a window function when each row’s answer depends on other rows, like a running total or a rank. Partition and order inside OVER, and filter on the result from an outer query.