Window Functions in SQLite

Window functions in SQLite let you compute values across a set of related rows without collapsing them into a single output row. They were added in SQLite 3.25.0 (released September 2018). If you're on an older build, you won't have them. Check your version with SELECT sqlite_version();.

A function becomes a window function when it has an OVER clause. That clause defines the "window" of rows the function sees.

Basic Syntax

SELECT
  column_name,
  WINDOW_FUNCTION() OVER (
    PARTITION BY grouping_column
    ORDER BY sorting_column
  ) AS alias
FROM table_name;

• PARTITION BY divides rows into groups; the function resets at each group boundary
• ORDER BY sets the logical order within each partition
• Both are optional, but ranking functions almost always need ORDER BY

Ranking Functions

ROW_NUMBER()

Assigns a unique sequential integer to each row within a partition, starting at 1. Ties get different numbers, so the result is only deterministic when the ORDER BY is unique.

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;

Use this for top-N-per-group queries and deduplication.

RANK()

Gives tied rows the same rank, then skips numbers. Two rows tied at rank 2 produce 1, 2, 2, 4.

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank_position
FROM exam_results;

DENSE_RANK()

Like RANK(), but without gaps. Two rows tied at rank 2 produce 1, 2, 2, 3.

SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank_position
FROM exam_results;

Which to use: ROW_NUMBER when you need exactly one row per group, RANK when gaps after ties are acceptable, DENSE_RANK when they aren't.

NTILE(n)

Splits rows into n roughly equal buckets. Useful for quartiles, percentiles, and cohort grouping.

SELECT
  name,
  revenue,
  NTILE(4) OVER (ORDER BY revenue DESC) AS quartile
FROM customers;

If the row count doesn't divide evenly, the earlier buckets get the extra rows.

Offset Functions: LAG and LEAD

LAG reads a value from a previous row; LEAD reads from a following row. Both take an optional offset (default 1) and an optional default value for when the offset falls outside the partition.

SELECT
  sale_date,
  amount,
  LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount,
  amount - LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS day_over_day
FROM daily_sales;

This is the standard pattern for period-over-period change without a self-join.

FIRST_VALUE, LAST_VALUE, NTH_VALUE

These pull a specific value from the window frame.

SELECT
  name,
  department,
  salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_paid
FROM employees;

LAST_VALUE has a common trap: with the default frame it only sees up to the current row, so it returns the current row rather than the partition's last. See the frame section below for the fix.

Aggregate Functions as Window Functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX) becomes a running calculation when given an OVER clause with an ORDER BY.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total,
  AVG(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7day_avg
FROM daily_sales;

Frame Specifications

The frame controls which rows within the partition the function operates on. SQLite supports ROWS, RANGE, and GROUPS framing.

-- Physical rows
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
 
-- Logical value range
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 
-- Whole partition
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Key default: when you supply ORDER BY without an explicit frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That default is why LAST_VALUE often surprises people. To get the true last value, widen the frame:

LAST_VALUE(name) OVER (
  PARTITION BY department ORDER BY salary DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Named Windows

If several functions share the same window, define it once with a WINDOW clause to avoid repetition.

SELECT
  name,
  salary,
  RANK()       OVER w AS rank_pos,
  DENSE_RANK() OVER w AS dense_pos,
  ROW_NUMBER() OVER w AS row_num
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary DESC);

The FILTER Clause

SQLite lets you attach a FILTER clause to aggregate window functions to restrict which rows feed the aggregate.

SELECT
  sale_date,
  amount,
  SUM(amount) FILTER (WHERE amount > 100) OVER (ORDER BY sale_date) AS running_big_sales
FROM daily_sales;

Common Mistakes

Window functions in WHERE -- you can't filter on a window function directly. Wrap the query in a subquery or CTE and filter the result.

-- Wrong
SELECT name, RANK() OVER (ORDER BY salary DESC) AS r
FROM employees WHERE r <= 5;
 
-- Right
SELECT * FROM (
  SELECT name, RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) WHERE r <= 5;

Forgetting the version requirement -- window functions need SQLite 3.25.0+. Many bundled or embedded builds lag behind. Confirm with SELECT sqlite_version();.

LAST_VALUE returning the current row -- caused by the default RANGE frame. Set an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame.

Non-deterministic ROW_NUMBER -- if the ORDER BY has ties, add a unique column (like rowid) as a tiebreaker.

For background on the building blocks these queries rely on, see our SQLite aggregate functions and CTE guides.

Exploring These Queries

Mako's AI autocomplete can suggest window function syntax as you type. Connect your SQLite database at mako.ai and try these queries interactively.