> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# LAG()

## Overview

The `LAG()` window function returns the values from specific rows based on the offset argument (previous to the current row in the partition). It can be used with all [data types supported by Oxla](/sql-reference/sql-data-types/overview)

## Syntax

The syntax for this function is as follows:

```sql theme={null}
LAG (expression, offset, default) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```

The output's data type for this function is the same as the input's one. If there is no row that meets the offset criteria, it returns a default value (that must be of a type compatible with the expression value)

## Parameters

* `expression`: column, which will be referenced
* `offset`: numeric indicator of the previous row to access, that is relative to the current row (optional, if not specified 1 will be returned)
* `default`: value that wil be returned if the `offset` is out of range (optional, if not specified `NULL` will be returned)

## Examples

For the needs of this section, we will create the `winsales` table that stores details about some sales transactions:

```sql theme={null}
CREATE TABLE winsales(
    salesid int,
    dateid date,
    sellerid int,
    buyerid text,
    qty int,
    qty_shipped int);
INSERT INTO winsales VALUES
    (30001, '8/2/2003', 3, 'b', 10, 10),
    (10001, '12/24/2003', 1, 'c', 10, 10),
    (10005, '12/24/2003', 1, 'a', 30, null),
    (40001, '1/9/2004', 4, 'a', 40, null),
    (10006, '1/18/2004', 1, 'c', 10, null),
    (20001, '2/12/2004', 2, 'b', 20, 20),
    (40005, '2/12/2004', 4, 'a', 10, 10),
    (20002, '2/16/2004', 2, 'c', 20, 20),
    (30003, '4/18/2004', 3, 'b', 15, null),
    (30004, '4/18/2004', 3, 'b', 20, null),
    (30007, '9/7/2004', 3, 'c', 30, null);	 
```

### LAG(expression, offset)

In this example, we will focus on executing the `LAG()` function with expression and offset parameters' values specified:

```sql theme={null}
SELECT buyerid, dateid, qty
    LAG(qty,1) OVER (ORDER BY buyerid, dateid) AS prev_qty
FROM winsales WHERE buyerid = 'c'
ORDER BY buyerid, dateid;
```

When executing the query above, it returns the buyer ID, date ID, quantity and previous quantity for all rows with buyer ID equal to `c`:

```sql theme={null}
 buyerid |   dateid   | qty | prev_qty 
---------+------------+-----+----------
 c       | 2003-12-24 |  10 |         
 c       | 2004-01-18 |  10 |       10
 c       | 2004-02-16 |  20 |       10
 c       | 2004-09-07 |  30 |       20
(4 rows)
```

### LAG(expression, offset, default)

In this example, we will focus on executing the `LAG()` function with expression, offset and default parameters' values specified:

```sql theme={null}
SELECT buyerid, dateid, qty
    LAG(buyerid,1,'unknown') OVER (ORDER BY dateid) AS prev_buyerid
FROM winsales 
ORDER BY dateid;
```

The query above returns the buyer ID, date ID, quantity and previous buyer ID for all rows:

```sql theme={null}
 buyerid |   dateid   | qty | prev_buyerid 
---------+------------+-----+--------------
 b       | 2003-08-02 |  10 | unknown
 c       | 2003-12-24 |  10 | b
 a       | 2003-12-24 |  30 | c
 a       | 2004-01-09 |  40 | a
 c       | 2004-01-18 |  10 | a
 b       | 2004-02-12 |  20 | c
 a       | 2004-02-12 |  10 | b
 c       | 2004-02-16 |  20 | a
 b       | 2004-04-18 |  15 | c
 b       | 2004-04-18 |  20 | b
 c       | 2004-09-07 |  30 | b
(11 rows)
```

### Time Series: LAG() to Compare Daily Sales Quantities

In this example, we will use LAG() to compare each day's sales quantity (`qty`) with the previous day's quantity, ordered by `dateid`:

```sql theme={null}
SELECT dateid, qty,
    LAG(qty) OVER (ORDER BY dateid) AS prev_day_qty,
    qty - LAG(qty) OVER (ORDER BY dateid) AS qty_change
FROM winsales
ORDER BY dateid;
```

By executing the query above, we will get the following output:

```sql theme={null}
   dateid   | qty | prev_day_qty | qty_change 
------------+-----+--------------+------------
 2003-08-02 |  10 |              |           
 2003-12-24 |  10 |           10 |          0
 2003-12-24 |  30 |           10 |         20
 2004-01-09 |  40 |           30 |         10
 2004-01-18 |  10 |           40 |        -30
 2004-02-12 |  20 |           10 |         10
 2004-02-12 |  10 |           20 |        -10
 2004-02-16 |  20 |           10 |         10
 2004-04-18 |  15 |           20 |         -5
 2004-04-18 |  20 |           15 |          5
 2004-09-07 |  30 |           20 |         10
(11 rows)
```