> ## 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.

# LEAD()

## Overview

The `LEAD()` window function takes a column and an integer offset as arguments and returns the value of the cell in that column that is located at the specified number of rows after the current row. 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}
LEAD (expression, offset, default) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
```

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

## Parameters

* `expression`: column, which will be referenced
* `offset`: numeric indicator of the row that is relative to the current one (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

In this example, we will use 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);	 
```

### LEAD(expression, offset)

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

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

The following query 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 | next_qty 
---------+------------+-----+----------
 c       | 2003-12-24 |  10 |       10
 c       | 2004-01-18 |  10 |       20
 c       | 2004-02-16 |  20 |       30
 c       | 2004-09-07 |  30 |         
(4 rows)
```

### Expression, Offset And Default Specified

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

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

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

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

### Time Series: LEAD() to Compare Next Day’s Sales Quantity

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

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

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

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