Moving averages: ROWS for the last n, RANGE for the last n days

A frame clause decides which rows around the current one the function sees. Two flavours do different jobs:

The test case

For Chennai's 2025 innings, in date order: runs scored, the average of the last three innings (one decimal), and the runs scored in the 7 days up to and including that match.

Expected result

Look at match 10: its 3-innings average still remembers matches 5 and 6, but its 7-day total is just 194, because the previous match was eight days earlier. Same rows, two different ideas of "recent".

Way 1: ROWS and RANGE frames

with innings as (
  select d.match_id, m.match_date, d.batting_team_id as team_id,
         sum(d.runs_batter + d.runs_extras) as runs
  from   cr_deliveries d
  join   cr_matches m on m.match_id = d.match_id
  group  by d.match_id, m.match_date, d.batting_team_id
)
select match_id,
       to_char(match_date, 'YYYY-MM-DD') as match_date,
       runs,
       round(avg(runs) over (order by match_date
                             rows between 2 preceding and current row), 1)               as avg_last_3,
       sum(runs) over (order by match_date
                       range between interval '6' day preceding and current row)          as runs_last_7_days
from   innings
where  team_id = 2
and    match_date < date '2026-01-01'
order  by match_date;

RANGE ... INTERVAL '6' DAY PRECEDING works because the ORDER BY is a date: "dates from 6 days before this one up to this one" is 7 days counting the match day. With a number ORDER BY, write RANGE BETWEEN 6 PRECEDING ... instead.

The first two rows average fewer than three innings, because there aren't three yet. That's usually what you want; if not, also compute count(*) over (same frame) and blank the rows where it's under 3.

Way 2: correlated subqueries

with innings as (
  select d.match_id, m.match_date, d.batting_team_id as team_id,
         sum(d.runs_batter + d.runs_extras) as runs
  from   cr_deliveries d
  join   cr_matches m on m.match_id = d.match_id
  group  by d.match_id, m.match_date, d.batting_team_id
),
chennai as (
  select match_id, match_date, runs,
         row_number() over (order by match_date) as n
  from   innings
  where  team_id = 2 and match_date < date '2026-01-01'
)
select c.match_id,
       to_char(c.match_date, 'YYYY-MM-DD') as match_date,
       c.runs,
       (select round(avg(x.runs), 1) from chennai x where x.n between c.n - 2 and c.n)               as avg_last_3,
       (select sum(x.runs) from chennai x where x.match_date between c.match_date - 6 and c.match_date) as runs_last_7_days
from   chennai c
order  by c.match_date;

The two frames, written as conditions: "row numbers from n − 2 to n" and "dates from 6 days before to today". Spelling them out like this is a good way to check what a frame really means.

19c and 26ai

The same on both.

Gotchas

• ROWS counts rows, RANGE measures values. A gap of eight days doesn't matter to ROWS 2 PRECEDING; it matters a lot to RANGE INTERVAL '6' DAY PRECEDING.
• RANGE with an offset needs exactly one ORDER BY column, of a number or date type.
• Ties in RANGE are included together (page 5.2). Two innings on the same date would both count.