🪟

SQLite Window Functions

SQLite

Complete reference for SQLite window functions covering row_number, rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value, and nth_value. Window functions were added in SQLite 3.25.0. Updated for SQLite 3.52.

11 functions

Jump to function

row_number rank dense_rank percent_rank cume_dist ntile lag lead first_value last_value nth_value

What are SQLite Window Functions?

SQLite window functions (added in SQLite 3.25.0, released 2018) compute results across a set of rows related to the current row using the OVER() clause, without collapsing rows like aggregate functions do. They support PARTITION BY, ORDER BY, and frame specifications. SQLite's window function syntax is compatible with PostgreSQL and standard SQL, making queries portable between the two databases for rank, lag, and running total calculations.

row_number

SQLite 3.25.0→ INTEGER

Assigns a unique sequential integer to each row within its window partition, ordered by the ORDER BY clause inside the OVER clause. The numbering starts at 1. Unlike rank(), row_number() never produces ties — every row receives a distinct number, even rows with identical ORDER BY values.

Signature

row_number() OVER (window-defn)

Examples

Use row_number() when you need a guaranteed-unique sequential number, such as for pagination or deduplication.

sql

SELECT name, salary,
  row_number() OVER (ORDER BY salary DESC) AS rn
FROM employees;

→

Each employee gets a unique rank by salary, highest first. Two employees with the same salary still get different numbers (e.g., 3 and 4).

PARTITION BY resets the counter for every distinct dept value.

sql

SELECT dept, name, salary,
  row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

→Numbering restarts at 1 for each department. The top earner in each dept gets rn=1.

SQLite does not support LIMIT/OFFSET inside a window frame, so this subquery approach is the standard way to paginate.

sql

-- Pagination: page 2 of 10 rows
SELECT * FROM (
  SELECT *, row_number() OVER (ORDER BY created_at DESC) AS rn
  FROM orders
) WHERE rn BETWEEN 11 AND 20;

→Returns rows 11-20 ordered by most recent first.

row_number() is the canonical way to deduplicate — rank() won't work reliably here because ties would give the same number.

sql

-- Remove duplicate emails, keeping the most recent row
DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id,
      row_number() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
  ) WHERE rn > 1
);

→Deletes all but the most recently created row for each duplicate email.

Useful for tracking the nth occurrence of an event per entity — e.g., the 3rd purchase by a customer.

sql

SELECT product_id, sale_date, amount,
  row_number() OVER (PARTITION BY product_id ORDER BY sale_date) AS sale_seq
FROM sales;

→Each sale for a product gets a sequential number (1st sale, 2nd sale, etc.).

⚠Anti-Pattern— Omitting ORDER BY in the OVER clause

Calling row_number() without an ORDER BY inside OVER produces non-deterministic numbering — the database engine may assign numbers in any order it chooses, and that order can change between queries or after a VACUUM. This silently undermines deduplication and pagination logic that depends on a stable sequence.

✓ Instead: Always include ORDER BY inside the OVER clause, e.g. row_number() OVER (PARTITION BY email ORDER BY created_at DESC), to guarantee a deterministic and meaningful row number.

Always use row_number() for deduplication tasks, not rank(). When two rows tie on the ORDER BY column, rank() assigns the same number to both and you cannot predict which one will be kept. row_number() assigns a unique value to each row, so 'WHERE rn = 1' always returns exactly one row per partition.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Behavior is identical to PostgreSQL's row_number(). SQLite does not support the RESPECT NULLS / IGNORE NULLS modifier that some other databases allow on certain window functions — row_number() does not take arguments so this is not relevant here.

rank

SQLite 3.25.0→ INTEGER

Assigns a rank to each row within its window partition, ordered by the ORDER BY clause. Rows with identical ORDER BY values (ties) receive the same rank. After a tie group, rank skips numbers — if three rows share rank 2, the next rank is 5 (not 3). This is sometimes called 'Olympic ranking' (1, 1, 3, 4...).

Signature

rank() OVER (window-defn)

Examples

Classic use case: public leaderboards, competition standings.

sql

SELECT name, score,
  rank() OVER (ORDER BY score DESC) AS rnk
FROM leaderboard;

→

Players with the same score share a rank, and the next distinct score gets rank = previous_rank + tie_count. E.g., two players with score 100 both get rank 1; the next player gets rank 3.

PARTITION BY applies ranking independently per department.

sql

SELECT dept, name, salary,
  rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank
FROM employees;

→Within each department, employees are ranked by salary. Ties get the same rank and the next rank is skipped.

When ties exist at the boundary (rnk=3), all tied rows are included — you may get more than 3 rows per category. Use row_number() if you need exactly 3.

sql

-- Find the top-3 ranked products by revenue in each category
SELECT * FROM (
  SELECT category, product, revenue,
    rank() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_sales
) WHERE rnk <= 3;

→Returns up to 3 rows per category, but may return more if there are ties at position 3.

Combining rank() with count() OVER () in the same SELECT is efficient — SQLite processes both in one pass.

sql

SELECT student_id, exam_score,
  rank() OVER (ORDER BY exam_score DESC) AS class_rank,
  count(*) OVER () AS total_students
FROM exam_results;

→Each student gets their class rank alongside the total number of students.

The final ORDER BY order_date controls display order without affecting the rank calculation.

sql

SELECT order_date, daily_revenue,
  rank() OVER (ORDER BY daily_revenue DESC) AS revenue_rank
FROM daily_sales
ORDER BY order_date;

→Each day is ranked by its revenue; days with the same revenue share a rank.

⚠Anti-Pattern— Using rank() when sequential category numbering is needed

When the goal is to number distinct groups or tiers consecutively — such as salary bands or product categories — rank() is the wrong choice because it leaves gaps after ties (1, 1, 3 instead of 1, 1, 2). Downstream code that iterates from 1 to max(rank) will skip tier numbers and produce misleading labels or off-by-one errors.

✓ Instead: Use dense_rank() whenever you need gap-free consecutive integers that enumerate distinct groups, reserving rank() only for competition-style standings where gaps are intentional.

rank() produces gaps after ties: if ranks 1, 1, 1 are tied, the next rank is 4 (not 2). This models real-world sporting conventions where tied athletes all get the same place and the next place number accounts for all tied athletes. If you want no gaps, use dense_rank(). If you want unique sequential numbers regardless of ties, use row_number().

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Behavior matches PostgreSQL's rank() exactly, including the gap-after-tie semantics. Unlike some databases, SQLite's rank() does not support FILTER clauses when used as a window function.

dense_rank

SQLite 3.25.0→ INTEGER

Assigns a rank to each row within its window partition with no gaps. Tied rows share the same rank, and the next distinct value always gets the immediately following integer — there are no skipped numbers. For example, the sequence 1, 1, 2, 3 (not 1, 1, 3, 4 like rank()).

Signature

dense_rank() OVER (window-defn)

Examples

Use dense_rank() when the number of distinct rank levels matters (e.g., 'salary tier 1, tier 2, tier 3').

sql

SELECT name, salary,
  dense_rank() OVER (ORDER BY salary DESC) AS dr
FROM employees;

→Employees with the same salary share a dense rank. The next unique salary level gets the very next integer — no gaps.

dense_rank() is ideal when you want to enumerate distinct groups without gaps.

sql

SELECT product, price_tier,
  dense_rank() OVER (ORDER BY price_tier) AS tier_number
FROM products;

→

Products in the same price tier share a tier_number. If there are 5 distinct tiers, dense_rank values will be 1 through 5.

The key difference: rank() skips numbers after a tie; dense_rank() never does.

sql

-- Show how rank() and dense_rank() differ side by side
SELECT name, score,
  rank() OVER (ORDER BY score DESC) AS rnk,
  dense_rank() OVER (ORDER BY score DESC) AS dense_rnk
FROM scores;

→Scores: 100,100,90,80 → rank: 1,1,3,4 — dense_rank: 1,1,2,3

Dense rank is particularly useful for grouping by compensation band within a business unit.

sql

SELECT dept, name, salary,
  dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_level
FROM employees;

→

Within each department, salary levels are numbered consecutively. A dept with 3 unique salary levels will have dense_rank values 1, 2, 3.

Because dense_rank() has no gaps, its maximum value always equals the count of distinct ORDER BY values.

sql

-- Count how many distinct rank levels exist per department
SELECT dept,
  max(dense_rank() OVER (PARTITION BY dept ORDER BY salary)) AS salary_levels
FROM employees
GROUP BY dept;

→The maximum dense_rank value within each department equals the number of distinct salary levels.

⚠Anti-Pattern— Using dense_rank() for pagination offsets

Because dense_rank() assigns the same number to tied rows, using it as a page-offset key is unreliable — multiple rows share the same dense_rank value, so 'WHERE dense_rank BETWEEN 11 AND 20' can return more or fewer than 10 rows. This causes page overlap or missing rows that are hard to debug.

✓ Instead: Use row_number() for pagination, which guarantees exactly one row per integer value and makes BETWEEN ranges predictable.

A useful property of dense_rank(): the maximum value in a partition always equals the number of distinct values in the ORDER BY column(s). You can use MAX(dense_rank() OVER (...)) to count distinct groups efficiently without a separate COUNT(DISTINCT ...) query.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Behavior is identical to PostgreSQL's dense_rank(). The key difference from rank() is the absence of gaps after ties — dense_rank always produces consecutive integers starting at 1. Both databases follow the SQL standard definition.

percent_rank

SQLite 3.25.0→ REAL

Returns the relative rank of the current row as a fraction between 0.0 and 1.0. Calculated as (rank - 1) / (total_rows_in_partition - 1). The first row in each partition always returns 0.0; the last row always returns 1.0. Returns 0.0 for all rows if the partition contains only one row.

Signature

percent_rank() OVER (window-defn)

Examples

A value of 0.75 means this row ranks higher than 75% of the partition.

sql

SELECT name, salary,
  percent_rank() OVER (ORDER BY salary) AS pct_rank
FROM employees;

→Returns values from 0.0 to 1.0. The lowest-paid employee gets 0.0, the highest gets 1.0.

Multiply by 100 to express as a percentage. Use ROUND() to avoid floating-point noise.

sql

SELECT name, salary,
  round(percent_rank() OVER (ORDER BY salary) * 100, 1) AS percentile
FROM employees;

→Displays percentile as a human-readable number (e.g., 75.0 meaning 75th percentile).

PARTITION BY makes the percentile relative to peers in the same group.

sql

SELECT dept, name, salary,
  percent_rank() OVER (PARTITION BY dept ORDER BY salary) AS dept_percentile
FROM employees;

→Each employee's percentile rank within their own department, not company-wide.

Order DESC so percent_rank 0.0 corresponds to the highest earner.

sql

-- Flag employees in top 10% of each dept by salary
SELECT dept, name, salary,
  CASE WHEN percent_rank() OVER (PARTITION BY dept ORDER BY salary DESC) <= 0.10
       THEN 'top 10%' ELSE 'other' END AS tier
FROM employees;

→Employees whose percent_rank (descending) is 0.10 or less are in the top 10% of their department.

Good for spotting outliers: products at 0.0 are the slowest sellers, products at 1.0 are the best.

sql

SELECT product, units_sold,
  percent_rank() OVER (ORDER BY units_sold) AS sales_percentile
FROM product_monthly_sales
WHERE month = '2024-12';

→Shows each product's relative sales performance compared to all products in December 2024.

⚠Anti-Pattern— Treating percent_rank as a 0–100 percentage

percent_rank() returns a REAL value between 0.0 and 1.0, not 0 to 100. Comparing it directly to whole-number thresholds like WHERE percent_rank() > 75 will never match any row (since the maximum value is 1.0), silently returning an empty result set with no error.

✓ Instead: Multiply by 100 before comparing to percentage thresholds, e.g. WHERE percent_rank() OVER (...) * 100 > 75, or store the multiplied value in a subquery column.

percent_rank() computes (rank-1)/(N-1) and is based on the row's rank position. cume_dist() computes the fraction of rows with values less than or equal to the current row's value. For ties, percent_rank() can return the same value for multiple rows (matching rank()), while cume_dist() counts all rows up to and including the tie group. Use percent_rank() for 'what percentile is this row at' and cume_dist() for 'what fraction of rows are at most this value'.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Formula is (rank() - 1) / (count(*) OVER (PARTITION BY ...) - 1). Returns 0.0 when the partition has only 1 row. Behavior matches PostgreSQL's percent_rank() exactly. The result is always REAL (float), never INTEGER.

cume_dist

SQLite 3.25.0→ REAL

Returns the cumulative distribution of a value within a window partition: the fraction of rows with a value less than or equal to the current row's ORDER BY value. Result is always in the range (0, 1]. The last row (highest value) always returns 1.0. Unlike percent_rank(), cume_dist() never returns 0.0.

Signature

cume_dist() OVER (window-defn)

Examples

cume_dist() is always > 0 and <= 1. The highest-valued row always gets 1.0.

sql

SELECT name, salary,
  cume_dist() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

→A value of 0.8 means 80% of employees earn less than or equal to this salary.

Multiply by 100 to get a percentage value.

sql

SELECT name, salary,
  round(cume_dist() OVER (ORDER BY salary) * 100, 1) AS cum_pct
FROM employees;

→Shows the cumulative percentage. An employee with cum_pct=60 is at or below the 60th percentile.

Use cume_dist() <= threshold to filter by percentile position.

sql

-- Show rows that fall in the bottom 40% by score
SELECT student_id, score
FROM (
  SELECT student_id, score,
    cume_dist() OVER (ORDER BY score) AS cd
  FROM exam_results
) WHERE cd <= 0.4;

→Returns students whose score places them in the bottom 40% of the class.

Key difference: for tied rows, cume_dist() returns the position of the last tied row; percent_rank() returns the position of the first tied row.

sql

SELECT product, revenue,
  cume_dist() OVER (ORDER BY revenue) AS cd,
  percent_rank() OVER (ORDER BY revenue) AS pr
FROM sales
ORDER BY revenue;

→

Side-by-side comparison. For the lowest revenue product: cd > 0, pr = 0. For ties, cume_dist groups them at the top of the tie range; percent_rank groups them at the bottom.

PARTITION BY means a salary that is median company-wide might be at the top of a lower-paid department.

sql

SELECT dept, name, salary,
  cume_dist() OVER (PARTITION BY dept ORDER BY salary) AS dept_cd
FROM employees;

→Cumulative distribution of salary within each department separately.

⚠Anti-Pattern— Confusing cume_dist with percent_rank for the first row

Developers sometimes swap cume_dist() and percent_rank() assuming they are interchangeable. A critical difference is that cume_dist() is always greater than 0 — even the very first row gets a value of (tie-group-size / total-rows) — while percent_rank() returns exactly 0.0 for the first row. Code that checks for a 0.0 sentinel to identify the lowest-ranked row will work with percent_rank() but silently fail with cume_dist().

✓ Instead: Use percent_rank() when you need the bottom row to return 0.0, and use cume_dist() when you need to know what fraction of rows fall at or below a given value (always > 0).

When rows are tied on the ORDER BY value, cume_dist() assigns all tied rows the same value — specifically the cumulative fraction that includes all of them. For example, if 3 rows are tied at rank 5 out of 10 total rows, all 3 get cume_dist = 7/10 = 0.7 (positions 5, 6, 7 are all accounted for). This means cume_dist() can jump from 0.4 to 0.7 if there are 3 ties.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Computed as count(rows with value <= current row value) / count(all rows in partition). Never returns 0 (unlike percent_rank which returns 0 for the first row). Result is always REAL. Behavior matches PostgreSQL's cume_dist() exactly.

ntile

SQLite 3.25.0→ INTEGER

Divides the ordered rows in each partition into N roughly equal buckets and returns the bucket number (1 through N) for each row. If the number of rows is not evenly divisible by N, the first (rows % N) buckets contain one extra row each. For example, with 10 rows and N=3: buckets are 4, 3, 3 rows.

Signature

ntile(N) OVER (window-defn)

Parameters

Parameter	Type	Description
N	INTEGER	The number of buckets to divide rows into. Must be a positive integer greater than zero.

Examples

ntile(4) gives quartiles. ntile(10) gives deciles. ntile(100) gives percentiles (though cume_dist is usually better for that).

sql

SELECT name, salary,
  ntile(4) OVER (ORDER BY salary) AS quartile
FROM employees;

→Divides employees into 4 salary quartiles. Quartile 1 = lowest earners, quartile 4 = highest.

CASE on the ntile result lets you map bucket numbers to descriptive labels.

sql

SELECT name, salary,
  ntile(4) OVER (ORDER BY salary) AS quartile,
  CASE ntile(4) OVER (ORDER BY salary)
    WHEN 1 THEN 'Bottom 25%'
    WHEN 2 THEN 'Lower-mid 25%'
    WHEN 3 THEN 'Upper-mid 25%'
    WHEN 4 THEN 'Top 25%'
  END AS quartile_label
FROM employees;

→Each employee is labeled by their salary quartile.

ORDER BY DESC puts the highest earners in tier 1. Useful for compensation reviews.

sql

SELECT dept, name, salary,
  ntile(3) OVER (PARTITION BY dept ORDER BY salary DESC) AS salary_tier
FROM employees;

→Within each department, employees are divided into 3 tiers by salary (descending). Tier 1 = top earners.

Using ORDER BY random() with ntile() is a simple way to create test cohorts, though for production A/B tests a dedicated randomization strategy is better.

sql

-- A/B/C testing: randomly assign users to 3 equal groups
SELECT user_id,
  ntile(3) OVER (ORDER BY random()) AS test_group
FROM users;

→Users are pseudo-randomly split into 3 equal-sized groups.

ntile(5) gives quintiles; bucket 1 is always the lowest-scoring group.

sql

-- Identify the bottom 20% performers for review
SELECT employee_id, performance_score
FROM (
  SELECT employee_id, performance_score,
    ntile(5) OVER (ORDER BY performance_score) AS quintile
  FROM performance_reviews
  WHERE year = 2024
) WHERE quintile = 1;

→Returns the bottom quintile (bottom 20%) of employees by performance score.

⚠Anti-Pattern— Assuming all ntile buckets are the same size

When the row count is not evenly divisible by N, ntile() places one extra row in the first (rows % N) buckets. Treating all buckets as exactly equal-sized — for example, multiplying bucket number by an assumed per-bucket row count — produces off-by-one errors and incorrect range labels in reports or dashboards.

✓ Instead: Count rows per bucket explicitly with COUNT(*) GROUP BY ntile_col after the fact, or document the uneven distribution in any report that presents bucket sizes as equal.

When the number of rows does not divide evenly into N buckets, SQLite distributes the remainder across the first buckets. With 11 rows and ntile(3): bucket 1 gets 4 rows, buckets 2 and 3 get 3 rows each. This means the first few buckets are always at least as large as later ones — they are never smaller. Keep this in mind when interpreting results near bucket boundaries.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). The argument N must be a positive constant integer; it cannot be a column reference or expression. If N is greater than the number of rows in the partition, some buckets will be empty (no rows assigned). Behavior matches PostgreSQL's ntile() exactly.

lag

SQLite 3.25.0→ same as expr

Returns the value of expr from a row that is offset rows before the current row within the window partition. If no such row exists (e.g., you are at the first row and offset is 1), returns the default value (or NULL if no default is specified). The default offset is 1 (the immediately preceding row). offset must be a non-negative integer.

Signatures

lag(expr) OVER (window-defn)

lag(expr, offset) OVER (window-defn)

lag(expr, offset, default) OVER (window-defn)

Parameters

Parameter	Type	Description
expr	any	The expression or column to retrieve from the preceding row.
offset	INTEGER	How many rows back to look. Defaults to 1. Must be a non-negative integer constant.
default	any	Value to return when the offset goes beyond the partition boundary. Defaults to NULL.

Examples

The default offset is 1, so lag(revenue) is equivalent to lag(revenue, 1).

sql

SELECT sale_date, revenue,
  lag(revenue) OVER (ORDER BY sale_date) AS prev_day_revenue
FROM daily_sales;

→Each row shows the previous day's revenue. The first row gets NULL (no preceding row).

Using 0.0 as the default prevents NULL arithmetic. Provide a meaningful default for the boundary row.

sql

SELECT sale_date, revenue,
  lag(revenue, 1, 0.0) OVER (ORDER BY sale_date) AS prev_revenue,
  revenue - lag(revenue, 1, 0.0) OVER (ORDER BY sale_date) AS day_over_day_change
FROM daily_sales;

→Shows the revenue change compared to the prior day. The first row reports the full revenue as change (prev was 0).

lag() with a large offset is perfect for year-over-year calculations when data is monthly.

sql

SELECT product_id, month, units_sold,
  lag(units_sold, 12, NULL) OVER (PARTITION BY product_id ORDER BY month) AS same_month_last_year
FROM monthly_sales;

→For each product and month, shows the units sold exactly 12 months prior (year-over-year comparison).

PARTITION BY session_id ensures lag() does not cross session boundaries.

sql

SELECT session_id, event_time, event_type,
  lag(event_type) OVER (PARTITION BY session_id ORDER BY event_time) AS prev_event
FROM user_events;

→Each event is paired with the previous event type in the same session.

Using lag(rating, 1, rating) as the default makes the first row's change 0 instead of NULL.

sql

SELECT employee_id, review_date, rating,
  lag(rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS prev_rating,
  rating - lag(rating, 1, rating) OVER (PARTITION BY employee_id ORDER BY review_date) AS rating_change
FROM performance_reviews;

→

Shows each employee's rating change from the previous review. The first review shows 0 change (default = current rating).

⚠Anti-Pattern— Not providing a default for the first row

Omitting the third argument means lag() returns NULL for the first row of every partition. When that NULL flows into arithmetic expressions such as revenue - lag(revenue) OVER (...), the entire result for that row becomes NULL, silently dropping it from aggregations or producing a misleading gap at the start of each time series.

✓ Instead: Always supply a third default argument: use lag(revenue, 1, 0.0) for numeric deltas or lag(status, 1, status) to treat the first row as having no change.

When lag() reaches the beginning of a partition, it returns NULL by default. This can silently corrupt calculations (e.g., NULL - revenue = NULL). Always supply a meaningful third argument: lag(col, 1, 0) for numeric differences, lag(col, 1, col) to default to the current value (making the change = 0), or lag(col, 1, 'N/A') for string contexts. This avoids surprise NULLs propagating through calculations.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Unlike PostgreSQL, SQLite's lag() does not support the RESPECT NULLS / IGNORE NULLS modifier. The offset argument must be a non-negative constant integer — it cannot be a column reference. Behavior otherwise matches PostgreSQL's lag() exactly.

lead

SQLite 3.25.0→ same as expr

Returns the value of expr from a row that is offset rows after the current row within the window partition. If no such row exists (e.g., you are at the last row and offset is 1), returns the default value (or NULL if no default is specified). The default offset is 1 (the immediately following row). offset must be a non-negative integer.

Signatures

lead(expr) OVER (window-defn)

lead(expr, offset) OVER (window-defn)

lead(expr, offset, default) OVER (window-defn)

Parameters

Parameter	Type	Description
expr	any	The expression or column to retrieve from the following row.
offset	INTEGER	How many rows forward to look. Defaults to 1. Must be a non-negative integer constant.
default	any	Value to return when the offset goes beyond the partition boundary. Defaults to NULL.

Examples

lead() is the forward-looking mirror of lag().

sql

SELECT sale_date, revenue,
  lead(revenue) OVER (ORDER BY sale_date) AS next_day_revenue
FROM daily_sales;

→Each row shows the following day's revenue. The last row gets NULL (no next row).

lead() is ideal for computing inter-event durations. julianday() converts timestamps to comparable floats in SQLite.

sql

SELECT event_time, event_type,
  lead(event_time) OVER (PARTITION BY session_id ORDER BY event_time) AS next_event_time,
  (julianday(lead(event_time) OVER (PARTITION BY session_id ORDER BY event_time)) -
   julianday(event_time)) * 86400 AS seconds_to_next_event
FROM user_events;

→Calculates the time in seconds between consecutive events in each session.

PARTITION BY dept ensures lead() only looks ahead within the same department.

sql

SELECT employee_id, start_date,
  lead(start_date) OVER (PARTITION BY dept ORDER BY start_date) AS next_hire_date
FROM employees;

→Shows when the next person was hired in the same department after each employee.

Providing a sentinel default value avoids NULL handling in downstream logic.

sql

SELECT project_id, milestone, due_date,
  lead(due_date, 1, '9999-12-31') OVER (PARTITION BY project_id ORDER BY due_date) AS next_milestone_date
FROM milestones;

→Each milestone shows the date of the next milestone in the same project. The last milestone gets '9999-12-31'.

Computing lead() twice in the same query is valid but slightly inefficient; use a subquery or CTE to compute it once if performance matters.

sql

-- Detect price drops: flag any day where next day's price is lower
SELECT ticker, trade_date, close_price,
  lead(close_price) OVER (PARTITION BY ticker ORDER BY trade_date) AS next_close,
  CASE WHEN lead(close_price) OVER (PARTITION BY ticker ORDER BY trade_date) < close_price
       THEN 1 ELSE 0 END AS followed_by_drop
FROM stock_prices;

→Each row is flagged 1 if the next trading day's price is lower.

⚠Anti-Pattern— Not providing a default for the last row

Without a third default argument, lead() returns NULL for the last row of every partition. When this is used in inter-event duration calculations — such as julianday(lead(event_time)) - julianday(event_time) — the final event in every session silently produces NULL, causing it to be excluded from AVG() or SUM() aggregations without any warning.

✓ Instead: Provide a meaningful default as the third argument, e.g. lead(event_time, 1, event_time) to make the last row's duration zero, or a sentinel date like '9999-12-31' when NULL exclusion would skew results.

A common pattern before window functions was to self-join a table on consecutive keys: SELECT a.date, a.value, b.value AS next_value FROM t a LEFT JOIN t b ON b.date = a.date + 1. With lead(), this becomes a single scan: SELECT date, value, lead(value) OVER (ORDER BY date). The window function version is not only cleaner but typically much faster on large tables because it avoids the join.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Like lag(), SQLite's lead() does not support RESPECT NULLS / IGNORE NULLS modifiers. The offset must be a non-negative constant integer — not a column reference. lead(x) is exactly equivalent to lag(x, -1) conceptually but lead() is the idiomatic form and should be preferred.

first_value

SQLite 3.25.0→ same as expr

Returns the value of expr from the first row of the current window frame. By default the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so first_value() returns the first row in the partition up to and including the current row — which is simply the first row of the partition when combined with an ORDER BY clause. The result is constant for all rows in the same partition when no custom frame is specified.

Signature

first_value(expr) OVER (window-defn)

Parameters

Parameter	Type	Description
expr	any	The expression or column to retrieve from the first row of the window frame.

Examples

With the default frame (RANGE UNBOUNDED PRECEDING TO CURRENT ROW) and ORDER BY salary DESC, the first row of each partition is the highest earner.

sql

SELECT name, salary, dept,
  first_value(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner
FROM employees;

→Every row shows the name of the highest-paid employee in the same department.

ROWS UNBOUNDED PRECEDING to UNBOUNDED FOLLOWING makes the frame the entire partition, so first_value() always returns the first row's value.

sql

SELECT sale_date, revenue,
  first_value(revenue) OVER (ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_day_revenue
FROM daily_sales;

→Every row shows the revenue from the very first day on record.

Explicit ROWS UNBOUNDED FOLLOWING ensures we always see the global first hire, not just the first up to the current row.

sql

SELECT dept, hire_date, name,
  first_value(name) OVER (PARTITION BY dept ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_hire
FROM employees;

→Every employee row shows who was hired first in their department.

Useful for funnel analysis: identify what action started each session.

sql

SELECT session_id, event_time, event_type,
  first_value(event_type) OVER (PARTITION BY session_id ORDER BY event_time) AS first_event
FROM user_events;

→Every event in a session shows the type of the session's first event (the entry point).

first_value() is great for computing changes relative to a baseline period.

sql

SELECT product_id, month, price,
  first_value(price) OVER (PARTITION BY product_id ORDER BY month) AS initial_price,
  price / first_value(price) OVER (PARTITION BY product_id ORDER BY month) - 1 AS price_change_pct
FROM pricing_history;

→Each row shows how much the price has changed as a percentage compared to the product's initial price.

⚠Anti-Pattern— Assuming first_value works correctly without an explicit frame

The default frame (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) happens to work for first_value() because the frame always starts at the partition boundary. However, developers who learn this habit and then add a sliding frame (e.g., ROWS BETWEEN 3 PRECEDING AND CURRENT ROW for a rolling window) will unknowingly change what first_value() returns — it now returns the value 3 rows back instead of the partition's first row, with no error produced.

✓ Instead: Always declare the frame explicitly on every first_value() call so that the intent is clear and future frame changes do not silently alter the result.

first_value() returns the first row of the current frame, not necessarily the first row of the partition. With the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, the frame always starts at the partition's first row, so first_value() does return the first partition row. But if you change the frame (e.g., ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), the 'first' row shifts to 2 rows back. Always be explicit about your frame when using first_value().

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). Behavior matches PostgreSQL's first_value() with one important note: SQLite does not support RESPECT NULLS / IGNORE NULLS on first_value(). If the first row of the frame contains a NULL, NULL is returned regardless. PostgreSQL added IGNORE NULLS support later; SQLite has not.

last_value

SQLite 3.25.0→ same as expr

Returns the value of expr from the last row of the current window frame. CRITICAL: The default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means last_value() returns the current row's own value by default — not the last row of the partition! To get the true last row of the partition, you must explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Signature

last_value(expr) OVER (window-defn)

Parameters

Parameter	Type	Description
expr	any	The expression or column to retrieve from the last row of the window frame.

Examples

This is the single most common mistake with last_value(). The default frame ends at CURRENT ROW, so the 'last' row is always the current row itself.

sql

-- WRONG: default frame makes last_value() return the current row itself
SELECT name, salary,
  last_value(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS wrong_last
FROM employees;
-- This returns each row's own name, not the lowest earner in dept!

→With the default frame, last_value() equals the current row's value for every row.

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING extends the frame to the entire partition. This is required for last_value() to be useful.

sql

-- CORRECT: explicit frame to get the true last row of the partition
SELECT name, salary, dept,
  last_value(name) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

→Every row correctly shows the name of the lowest-paid employee in the department (last row when ordered by salary DESC).

Without the explicit frame, each row would show its own revenue — which is usually not useful.

sql

SELECT sale_date, revenue,
  last_value(revenue) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS most_recent_revenue
FROM daily_sales;

→Every row shows the most recent day's revenue.

Combined with first_value(), you can identify the entry and exit points of every user session in one query.

sql

SELECT session_id, event_time, event_type,
  last_value(event_type) OVER (
    PARTITION BY session_id
    ORDER BY event_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS final_event
FROM user_events;

→Every event in a session shows the type of the session's last event (exit point).

Useful for showing current state alongside historical data.

sql

SELECT product_id, month, price,
  last_value(price) OVER (
    PARTITION BY product_id
    ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS current_price
FROM pricing_history;

→Shows the most recent (last) price for each product on every historical row.

⚠Anti-Pattern— Using last_value() with the default frame

The default frame ends at CURRENT ROW, so last_value() simply returns the current row's own value on every single row — making it indistinguishable from referencing the column directly. The query produces no error, the results look plausible, and the bug can go undetected until someone notices all values in the column are identical to the base column.

✓ Instead: Always add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to the OVER clause whenever using last_value(), making the frame cover the full partition so the true last row is returned.

last_value() is the most frequently misused window function. The default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes the frame end at the current row, so last_value() trivially returns the current row's value — which is never what you want. Always write an explicit frame: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to cover the entire partition, or ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING to look from the current row to the end. Forgetting this is a silent bug that produces wrong results with no error message.

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). The default frame gotcha affects SQLite and PostgreSQL equally — both use RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as the default. PostgreSQL documentation explicitly warns about this; always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING when using last_value() in either database. SQLite does not support IGNORE NULLS.

nth_value

SQLite 3.25.0→ same as expr

Returns the value of expr from the Nth row of the current window frame (1-indexed). If the frame contains fewer than N rows, returns NULL. Like last_value(), nth_value() is affected by the default frame ending at CURRENT ROW — specify an explicit frame when you want to access rows beyond the current row.

Signature

nth_value(expr, N) OVER (window-defn)

Parameters

Parameter	Type	Description
expr	any	The expression or column to retrieve from the Nth row of the window frame.
N	INTEGER	The 1-based position of the row to retrieve. Must be a positive integer. Returns NULL if the frame has fewer than N rows.

Examples

N=1 is equivalent to first_value(). The explicit frame is required to access rows beyond the current row.

sql

SELECT dept, name, salary,
  nth_value(name, 2) OVER (
    PARTITION BY dept
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS second_highest_earner
FROM employees;

→Every row shows the name of the second-highest earner in the same department.

Returns NULL until at least N rows exist in the frame.

sql

SELECT sale_date, revenue,
  nth_value(revenue, 3) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS third_day_revenue
FROM daily_sales;

→Every row shows the revenue from the third day. Rows before the third day show NULL.

Using nth_value() with multiple N values in the same SELECT is efficient — SQLite computes them in one pass.

sql

SELECT product_id, sale_month, revenue,
  nth_value(revenue, 1) OVER (PARTITION BY product_id ORDER BY sale_month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_month_rev,
  nth_value(revenue, 6) OVER (PARTITION BY product_id ORDER BY sale_month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sixth_month_rev
FROM monthly_product_sales;

→Shows each product's revenue from month 1 and month 6 alongside every monthly row.

Provides fallback rankings if the first choice declines the offer.

sql

-- Get the 2nd and 3rd ranked candidates per job posting
SELECT job_id, candidate_name, score,
  nth_value(candidate_name, 2) OVER (
    PARTITION BY job_id
    ORDER BY score DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS backup_candidate_1,
  nth_value(candidate_name, 3) OVER (
    PARTITION BY job_id
    ORDER BY score DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS backup_candidate_2
FROM interview_scores;

→Every candidate row shows who the top 2 backup candidates are for the same job posting.

Returns NULL for fifth_lap if the runner completed fewer than 5 laps.

sql

SELECT runner, lap_time,
  nth_value(lap_time, 1) OVER (PARTITION BY runner ORDER BY lap_number
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_lap,
  nth_value(lap_time, 5) OVER (PARTITION BY runner ORDER BY lap_number
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fifth_lap
FROM race_laps;

→Each lap row shows the runner's first and fifth lap time for comparison.

⚠Anti-Pattern— Using nth_value() without explicit frame bounds

With the default frame ending at CURRENT ROW, nth_value(expr, N) returns NULL for all rows that precede position N in the partition — but it also returns NULL for rows after the Nth position if N is beyond the current row, because those rows are outside the frame. This creates a confusing pattern where only a small window of rows returns the expected value while the rest silently return NULL.

✓ Instead: Always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so the full partition is in frame, ensuring nth_value() returns the Nth row's value consistently across all rows in the partition.

Like last_value(), nth_value() is bounded by the current frame. With the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, nth_value(x, 3) returns NULL for the first 2 rows (frame has fewer than 3 rows), returns the 3rd row's value starting from row 3, but never sees rows after the current row. If you want to access any row in the partition by position, always specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This is distinct from first_value() which always works correctly with the default frame (the frame always starts at the partition's beginning).

◆SQLite Note

Added in SQLite 3.25.0 (2018-09-15). N is 1-indexed (N=1 returns the same as first_value()). Returns NULL when the frame has fewer than N rows. The frame boundary gotcha applies here just as with last_value() — always provide an explicit frame. SQLite does not support IGNORE NULLS. Behavior matches PostgreSQL's nth_value() when the same explicit frame is specified.

Related SQLite Categories

Aggregate Functions →Core Functions →