LAG and LEAD: the row before, the row after

LAG(x) is "x on the previous row" and LEAD(x) is "x on the next row", in the window's order. They answer every "compared with last time" question: days since the last match, runs compared with the previous over, a price change since yesterday.

LAG(x, n, default) looks n rows back (1 if you leave it out) and returns default instead of null when there's no such row.

The test case

For Chennai's 2025 matches, show each match date, the previous match date and the days of rest in between.

Each match has Chennai either as the home team or the away team, so the first step makes one row per team per match.

Expected result

Eight days off before match 10, then nine before the final. Plenty of time to rest, or to worry.

Way 1: LAG

with team_matches as (
  select match_id, match_date, home_team_id as team_id from cr_matches
  union all
  select match_id, match_date, away_team_id            from cr_matches
)
select to_char(match_date, 'YYYY-MM-DD')                                     as match_date,
       match_id,
       to_char(lag(match_date) over (order by match_date), 'YYYY-MM-DD')     as prev_match,
       match_date - lag(match_date) over (order by match_date)               as days_rest
from   team_matches
where  team_id = 2
and    match_date < date '2026-01-01'
order  by match_date;

Here the WHERE is fine in the same query: we want the window to see only Chennai's 2025 matches, so "previous" means Chennai's previous match. The first row has no previous match, so LAG gives null.

Way 2: number the rows and join each to the one before

with team_matches as (
  select match_id, match_date, home_team_id as team_id from cr_matches
  union all
  select match_id, match_date, away_team_id            from cr_matches
),
numbered as (
  select match_id, match_date, row_number() over (order by match_date) as n
  from   team_matches
  where  team_id = 2 and match_date < date '2026-01-01'
)
select to_char(c.match_date, 'YYYY-MM-DD') as match_date,
       c.match_id,
       to_char(p.match_date, 'YYYY-MM-DD') as prev_match,
       c.match_date - p.match_date         as days_rest
from   numbered c
left   join numbered p on p.n = c.n - 1
order  by c.match_date;

A self-join on "row number minus one". The LEFT JOIN keeps the first match, which has no partner.

Way 3: the latest earlier date (no window functions)

with team_matches as (
  select match_id, match_date, home_team_id as team_id from cr_matches
  union all
  select match_id, match_date, away_team_id            from cr_matches
),
chennai as (
  select match_id, match_date from team_matches
  where  team_id = 2 and match_date < date '2026-01-01'
)
select to_char(c.match_date, 'YYYY-MM-DD') as match_date,
       c.match_id,
       to_char(p.prev_date, 'YYYY-MM-DD') as prev_match,
       c.match_date - p.prev_date         as days_rest
from   chennai c
outer  apply (select max(x.match_date) as prev_date
              from   chennai x
              where  x.match_date < c.match_date) p
order  by c.match_date;

OUTER APPLY runs the little query once for each row of chennai and keeps the row even when the subquery finds nothing (a LATERAL join, Part 2). It's here to show the definition: "previous" is the latest date before this one.

19c and 26ai

The same on both. LAG and LEAD don't take a frame: they always count rows in the window order.

Gotchas

• LAG needs an ORDER BY, and a unique one, or "previous" is ambiguous.
• Use the default argument when null would be wrong: lag(score, 1, 0) for "previous score, or 0 at the start".
• IGNORE NULLS: lag(x ignore nulls) skips rows where x is null, so it finds the last known value. Great for filling gaps in sensor data.