liveBook · Manning

Lead function

As explained in DuckDB in Action

A lead function returns an expression's value from a following row within an ordered window partition, or a default value when no such row exists.

A lead function returns an expression's value from a following row in an ordered window partition. It returns NULL when no following row exists. section 4.5.4

Use lead alongside window specifications to inspect nearby rows:

  • Order rows by a date or other orderable column. section 4.5.4
  • Define partitions when comparisons should restart for each group. section 4.5
  • Choose an offset and default value where supported; otherwise replace NULL with coalesce. section 4.5.4

Compare successive energy prices

Apply lead or lag to the prices table, ordered by valid_from, to compare adjacent compensation values. Use a CTE before aggregating the resulting changes because window functions cannot be nested inside aggregate functions. section 4.5.4

How DuckDB in Action covers it

How deep it goes
Explains ordering, partitions, offsets, defaults, and NULL handling, while stopping short of detailed execution internals.
Examples
Demonstrates adjacent compensation prices for German renewable-energy sales during 2019.
What it assumes
Assumes familiarity with SQL queries and introduces windows, ordering, partitions, and frames as the comparison structure.
What you can do after
Apply it via DuckDB SQL to retrieve following rows, handle missing neighbors, and prepare changes for later aggregation.
How it connects
Connects lead with window functions, lag, partitions, frames, coalesce, CTEs, and aggregate functions.
Cover of DuckDB in Action
liveBookmanning.com
Read DuckDB in Action on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of DuckDB in Action. The section links point to the parts of the book it draws on.