SQLite lag() Function

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

`lag()` Syntax

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

lag(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 lag() function returns the value from the specified row before the current row in the partition where the current row is located.

`lag()` 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 lag() function to add a next_quarter_revenue column for comparing the revenue of the current quarter and the next quarter:

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

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

Note that the window function in the SQL statement above:

lag(revenue, 1) OVER (
  PARTITION BY year
  ORDER BY quarter DESC
) next_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 lag(revenue, 1) returns the revenue (revenue) from the previous row (1) for within the partition where the current row is located.

So the value of the next_quarter_revenue column is the revenue of the next quarter of the current row. And the value of the next_quarter_revenue column in the first row of each partition is null.

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

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

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

lag() Syntax