SQLite Window Functions: OVER, PARTITION BY, and Frames

A Window Function Adds a Column Without Collapsing Rows

GROUP BY reduces many rows into one. A window function does something different: it computes a value across a set of related rows but keeps every input row in the output. You get the row-by-row detail and the aggregate, side by side.

The shape is always the same: a function, then OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

The total_all column shows the grand total of every row, repeated on each line. The original rows are untouched. Compare that to SELECT SUM(amount) FROM sales — same number, but only one row comes back. Window functions give you both views at once.

PARTITION BY: Aggregate Within Groups

An empty OVER () aggregates over the whole table. Add PARTITION BY to aggregate within groups, much like GROUP BY — but again, without collapsing rows.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Each row gets its region's total and its share of that total. With a plain GROUP BY you'd lose the per-employee detail. That's the headline win of window functions: detail and aggregate in one query.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

The ranking family numbers rows according to an ORDER BY inside OVER. The three flavors differ in how they handle ties.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Reading the output:

• ROW_NUMBER() is always unique — ties get broken arbitrarily. Use it when you need a stable, distinct number per row.
• RANK() gives tied rows the same rank, then skips the next numbers. Two players tied at 1 are followed by rank 3.
• DENSE_RANK() also ties, but doesn't skip. The next rank is 2.

For "top N per group," combine ranking with PARTITION BY and filter in an outer query — WHERE can't reference window functions directly:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Top two earners per region.

LAG and LEAD: Look at Neighboring Rows

LAG(col) returns the value of col from the previous row in the window. LEAD(col) reaches forward. Both are perfect for change-over-time questions.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

The first row's yesterday is NULL — there's nothing before it. You can supply a default: LAG(celsius, 1, celsius) OVER (ORDER BY day) would use today's value when no previous row exists.

LEAD is the mirror image. Combine the two with PARTITION BY to get per-group sequences, like comparing this month's sales to the previous month within each region.

Running Totals With Window Frames

Add ORDER BY inside OVER, and aggregate functions like SUM, AVG, COUNT start computing cumulatively:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Two things to notice:

• SUM(amount) OVER (ORDER BY day) is a running total. The default frame when you write ORDER BY without an explicit frame is "from the start of the window through the current row."
• The second column uses an explicit frame: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. That's a 3-row sliding window — a moving average.

The mental model for frames: every window function evaluates over a frame of rows, defined relative to the current row. Common frames:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — running total (the implicit default).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW — trailing window.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — the whole partition.

ROWS counts physical rows. There's also RANGE, which groups by value — handy when you have ties in the ORDER BY column and want them treated as a single step.

FIRST_VALUE, LAST_VALUE, NTILE

A few more window functions worth knowing:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE and LAST_VALUE return the first or last value within the frame. With LAST_VALUE, watch the frame: the default frame ends at CURRENT ROW, so you usually want ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to get the actual last value of the partition.
• NTILE(n) splits the rows into n roughly equal buckets — useful for quartiles, percentiles, A/B-style splits.

Naming a Window With WINDOW

When several columns share the same OVER (...) clause, the repetition gets tedious. SQLite lets you name a window once and reuse it:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

Same query, less noise. The WINDOW clause goes after WHERE/GROUP BY/HAVING and before ORDER BY.

Window Functions vs GROUP BY

Both involve aggregation, but they answer different questions:

• GROUP BY reduces. One row per group. Use it when you only want the summary.
• Window functions preserve. Every input row survives, with extra computed columns alongside.

If you ever find yourself doing a GROUP BY and then re-joining the aggregates back to the original table, that's a strong signal a window function would do the job in one query.

A Couple of Pitfalls

• WHERE can't reference window functions. Filters happen before windows are computed. Wrap the query in a subquery or CTE and filter on the outer level.
• Implicit frames bite. SUM(x) OVER (ORDER BY y) is a running total because the default frame is RANGE UNBOUNDED PRECEDING. If you wanted the full-partition sum, write OVER (PARTITION BY ...) without an ORDER BY, or specify the frame explicitly.
• LAST_VALUE surprises everyone the first time. With the default frame ending at the current row, it returns the current value, not the partition's last. Override the frame.
• Window functions need SQLite 3.25+ (released in 2018). Any reasonably modern install has them, but some embedded environments lag behind.

Next: Generated Columns

Window functions are query-time computation. The next page covers storage-time computation: generated columns, where the column's value is defined by an expression and updated automatically as the underlying data changes.