LEAD

Accesses data in a subsequent row in the same result set without having to join the table to itself.

See also: LAG

Syntax

LEAD ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ]
  OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] )

Arguments

• expr: The expression whose value is returned.
• offset: Number of rows forward from the current row to fetch the value from. For example, an offset of 2 returns the expr value with an interval of 2 rows. Negative values behave like LAG. Default: 1. If IGNORE NULLS is specified, maximum is 1,000,000.
• default: The expression to return when the offset is out of bounds of the window. Type must be compatible with expr. Default: NULL.
• { IGNORE | RESPECT } NULLS:
  • IGNORE NULLS: Excludes rows where the expression evaluates to NULL when counting offset rows.
  • RESPECT NULLS: Includes rows where the expression evaluates to NULL when counting offset rows.
  • Default: RESPECT NULLS.

Usage notes

• PARTITION BY partitions the result set; the function is applied per partition. For more information, see Window function syntax and usage.
• ORDER BY defines the order within each partition.

Examples

Compare revenue to the next year per employee:

SELECT 
    emp_id,
    year,
    revenue,
    LEAD(revenue) OVER (
        PARTITION BY emp_id 
        ORDER BY year
    ) - revenue AS diff_to_next
FROM (
    SELECT 0 AS emp_id, 2010 AS year, 1000.00 AS revenue UNION ALL
    SELECT 0, 2011, 1500.00 UNION ALL
    SELECT 0, 2012, 500.00 UNION ALL
    SELECT 0, 2013, 750.00 UNION ALL
    SELECT 1, 2010, 10000.00 UNION ALL
    SELECT 1, 2011, 12500.00 UNION ALL
    SELECT 1, 2012, 15000.00 UNION ALL
    SELECT 1, 2013, 20000.00 UNION ALL
    SELECT 2, 2012, 500.00 UNION ALL
    SELECT 2, 2013, 800.00
) sales
ORDER BY emp_id, year;

Output:

+--------+------+----------+--------------+
| EMP_ID | YEAR |  REVENUE | DIFF_TO_NEXT |
|--------+------+----------+--------------|
|      0 | 2010 |  1000.00 |       500.00 |
|      0 | 2011 |  1500.00 |     -1000.00 |
|      0 | 2012 |   500.00 |       250.00 |
|      0 | 2013 |   750.00 |         NULL |
|      1 | 2010 | 10000.00 |      2500.00 |
|      1 | 2011 | 12500.00 |      2500.00 |
|      1 | 2012 | 15000.00 |      5000.00 |
|      1 | 2013 | 20000.00 |         NULL |
|      2 | 2012 |   500.00 |       300.00 |
|      2 | 2013 |   800.00 |         NULL |
+--------+------+----------+--------------+

Using IGNORE NULLS:

SELECT 
    c1,
    c2,
    LEAD(c2) IGNORE NULLS OVER (
        ORDER BY c1
    )
FROM (
    SELECT 1 AS c1, 5 AS c2 UNION ALL
    SELECT 2, 4 UNION ALL
    SELECT 3, NULL UNION ALL
    SELECT 4, 2 UNION ALL
    SELECT 5, NULL UNION ALL
    SELECT 6, NULL UNION ALL
    SELECT 7, 6
) t1
ORDER BY c1;

Output:

+----+------+------------------------------------------+
| C1 | C2   | LEAD(C2) IGNORE NULLS OVER (ORDER BY C1) |
|----+------+------------------------------------------|
|  1 |  5   |                                        4 |
|  2 |  4   |                                        2 |
|  3 | NULL |                                        2 |
|  4 |  2   |                                        6 |
|  5 | NULL |                                        6 |
|  6 | NULL |                                        6 |
|  7 |  6   |                                     NULL |
+----+------+------------------------------------------+