Rolling metrics over dates with gaps need a date spine or per-day pre-aggregation (bridge to P12).

This unit is a junction, not new machinery: the rows_vs_range flagship proved ROWS counts rows; date_spine built the complete timeline; here they compose into the production pattern — pre-aggregate to one row per day (peers collapsed), spine the gaps (quiet days zero-filled), then ROWS-over-days is days, on any engine. The composed form also emits rows for empty days, which the RANGE form can't.

SELECT
      strftime(d, '%m-%d') AS day,
  SUM(amt) OVER (
    ORDER BY
      d ROWS 2 PRECEDING
  ) AS roll
FROM
  sales
ORDER BY
  d;

WITH
  spine AS (
    SELECT
      g::DATE AS d
    FROM
      generate_series(
        DATE '2027-05-01',
        DATE '2027-05-05',
        INTERVAL 1 DAY
      ) AS t (g)
  ),
  daily AS (
    SELECT
      sp.d,
      COALESCE(SUM(s.amt), 0) AS amt
    FROM
      spine sp
      LEFT JOIN sales s ON s.d = sp.d
    GROUP BY
      sp.d
  )
SELECT
  strftime(d, '%m-%d') AS day,
  SUM(amt) OVER (
    ORDER BY
      d ROWS 2 PRECEDING
  ) AS roll
FROM
  daily
ORDER BY
  d;

The trailing-3 total at May 5 read 60 (reaching back to May 1, four days away) because ROWS counts rows, not days, over a gapped log — a date spine plus per-day pre-aggregation makes ROWS-over-days mean days, and emits the quiet May 3–4 rows the ROWS version never did.

RANGE-interval where supported and gap-rows aren't needed; spine + per-day + ROWS as the portable, complete-timeline form.