SQLite lead() Function

The SQLite lead() function returns the value from the specified row after the current row in the partition where the current row is located.

`lead()` Syntax

Here is the syntax of the SQLite lead() function:

lead(expr[, offset[, default]])
OVER (
  [PARTITION BY partition_column_list]
  [ORDER BY order_column_list]
)

Parameters

expr: Required. It can be a column name or an expression.
offset: Optional. The number of lines at the offset relative to the current line. The default value is 1.
default: Optional. It can be a column name or an expression.
partition_column_list: List of columns for partitioning.
partition_column_list: List of columns for sorting.

Return value

The SQLite lead() function returns the value from the specified row after the current row in the partition where the current row is located.

`lead()` Examples

Preparing Data

Use the following CREATE TABLE statement to create a table named tax_revenue to store tax returns for every quarter:

CREATE TABLE tax_revenue (
  id INTEGER PRIMARY KEY,
  year CHAR(4) NOT NULL,
  quarter CHAR(1) NOT NULL,
  revenue INT NOT NULL
);

This tax_revenue table has 4 columns as following:

id - The row ID, primary key.
year- The year.
quarter- The quarter of the year, from 1 to 4.
revenue- Tax revenue.

Insert some rows into the tax_revenue table using the following INSERT statement:

INSERT INTO tax_revenue
  (year, quarter, revenue)
VALUES
  ('2020', '1', 3515),
  ('2020', '2', 3678),
  ('2020', '3', 4203),
  ('2020', '4', 3924),
  ('2021', '1', 3102),
  ('2021', '2', 3293),
  ('2021', '3', 3602),
  ('2021', '4', 2901);

Use the following SELECT statement to show all rows in this table:

SELECT * FROM tax_revenue;

id  year  quarter  revenue
--  ----  -------  -------
1   2020  1        3515
2   2020  2        3678
3   2020  3        4203
4   2020  4        3924
5   2021  1        3102
6   2021  2        3293
7   2021  3        3602
8   2021  4        2901

Example 1

The following statement uses the SQLite lead() function to add a last_quarter_revenue column for comparing the revenue of the current quarter and the last quarter:

SELECT
  *,
  lead(revenue, 1) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

id  year  quarter  revenue  last_quarter_revenue
--  ----  -------  -------  --------------------
4   2020  4        3924     4203
3   2020  3        4203     3678
2   2020  2        3678     3515
1   2020  1        3515
8   2021  4        2901     3602
7   2021  3        3602     3293
6   2021  2        3293     3102
5   2021  1        3102

Note that the window function in the SQL statement above:

lead(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) last_quarter_revenue

In the OVER clause,

The PARTITION BY year partitions all rows by year.
The ORDER BY quarter DESC sort the rows within each partition in descending order by quarter.
The lead(revenue, 1) returns the revenue (revenue) from the next row (1) for within the partition where the current row is located.

So the value of the last_quarter_revenue column is the revenue of the previous quarter of the current row. And the value of the last_quarter_revenue column in the last row of each partition is null.

You can specify a default value for nulls in the last_quarter_revenue column. The following statement is used 0 as a default value:

SELECT
  *,
  lead(revenue, 1, 0) OVER (
    PARTITION BY year
    ORDER BY quarter DESC
  ) last_quarter_revenue
FROM tax_revenue;

id  year  quarter  revenue  last_quarter_revenue
--  ----  -------  -------  --------------------
4   2020  4        3924     4203
3   2020  3        4203     3678
2   2020  2        3678     3515
1   2020  1        3515     0
8   2021  4        2901     3602
7   2021  3        3602     3293
6   2021  2        3293     3102
5   2021  1        3102     0

lead() Syntax