Window Functions II: Frames, Moving Averages and Episodes

16.1 Window Frames (25 min)

With ORDER BY inside OVER, you can also say which rows around the current one the function should see. That set of rows is the frame.

                        month   value
                        ─────   ─────
2 PRECEDING  ────────►  Mar     2.9    ┐
1 PRECEDING  ────────►  Apr     2.6    │  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
CURRENT ROW  ────────►  May     2.4    ┘  → a 3-month window
1 FOLLOWING             Jun     2.1

Frame vocabulary

The default frame, and the LAST_VALUE trap

When you write ORDER BY inside OVER without a frame, the default is "from the start up to the current row". That's why running totals work without extra syntax, but it breaks LAST_VALUE:

SELECT month, value,
       LAST_VALUE(value) OVER (ORDER BY month) AS last_default,          -- surprise!
       LAST_VALUE(value) OVER (ORDER BY month
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_true
FROM scratch.monthly_long
WHERE name = 'overnight_rate' AND month >= DATE '2025-01-01'
ORDER BY month;

With the default frame, the "last" row of each window is the current row, so last_default just repeats value. Spell out the frame whenever it matters.

16.2 Moving Averages, Momentum and Rolling Volatility (25 min)

Create day16/rolling.sql. We start from a CPI YoY CTE that we'll reuse all day:

WITH cpi AS (
    SELECT month, cpi,
           100 * (cpi / LAG(cpi, 12) OVER (ORDER BY month) - 1) AS yoy
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
)
SELECT month,
       ROUND(yoy, 2) AS yoy,
       ROUND(AVG(yoy) OVER (ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2)  AS yoy_ma3,
       ROUND(AVG(yoy) OVER (ORDER BY month
             ROWS BETWEEN 11 PRECEDING AND CURRENT ROW), 2) AS yoy_ma12
FROM cpi
WHERE yoy IS NOT NULL
ORDER BY month DESC
LIMIT 12;

pandas equivalent: yoy.rolling(3).mean() and yoy.rolling(12).mean().

min_periods: don't average too few observations

pandas' rolling(12) returns NaN until 12 values exist. SQL averages whatever is in the frame, so the first rows are averages of 1, 2, 3… values. Reproduce pandas' behaviour by counting:

WITH cpi AS (
    SELECT month,
           100 * (cpi / LAG(cpi, 12) OVER (ORDER BY month) - 1) AS yoy
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
      AND month >= DATE '2019-01-01'
)
SELECT month, ROUND(yoy, 2) AS yoy,
       CASE WHEN COUNT(yoy) OVER w12 = 12
            THEN ROUND(AVG(yoy) OVER w12, 2) END AS yoy_ma12
FROM cpi
WINDOW w12 AS (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
ORDER BY month
LIMIT 26;

(WINDOW w12 AS (...) names a window so you don't have to repeat it. More in 16.5.)

A CASE with no ELSE returns NULL. The notebook's housing-starts chart used min_periods=1, the opposite choice. Which is right depends on whether a partial average is meaningful for your chart.

3-month annualised inflation (momentum)

SELECT month, cpi,
       ROUND(100 * (POWER(cpi / LAG(cpi, 3) OVER (ORDER BY month), 4) - 1), 1) AS ann_3m
FROM raw.statcan_cpi
WHERE geo = 'Canada' AND component = 'All-items'
ORDER BY month DESC
LIMIT 6;

As on Day 5: this table is not seasonally adjusted, so short-horizon annualised rates carry seasonal noise.

Rolling volatility of the yield-curve spread

SELECT month,
       ROUND(spread_10_2_bp) AS spread_bp,
       ROUND(STDDEV_SAMP(spread_10_2_bp) OVER (ORDER BY month
             ROWS BETWEEN 35 PRECEDING AND CURRENT ROW), 1) AS vol_36m
FROM scratch.monthly_wide
WHERE spread_10_2_bp IS NOT NULL
ORDER BY month DESC
LIMIT 6;

16.3 Running Totals, Running Peaks and Drawdowns (23 min)

Example 1 — Cumulative policy moves

SELECT decision_date, change_bp,
       SUM(change_bp) OVER (ORDER BY decision_date) AS cumulative_bp
FROM raw.boc_decisions
ORDER BY decision_date;

pandas: .cumsum(). The default frame (start → current row) makes this a running sum automatically.

Example 2 — Resetting the running total each year

SELECT decision_date, change_bp,
       SUM(change_bp) OVER (PARTITION BY year(decision_date)
                            ORDER BY decision_date) AS ytd_bp
FROM raw.boc_decisions
ORDER BY decision_date;

Example 3 — The running peak

Day 2's accumulator loop and Day 5's expanding().max():

SELECT month,
       value AS rate,
       MAX(value) OVER (ORDER BY month
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak_so_far
FROM scratch.monthly_long
WHERE name = 'overnight_rate' AND month >= DATE '2022-01-01'
ORDER BY month;

Example 4 — Drawdown: distance from the recent high

How far has the Canadian dollar fallen from its best level of the previous 12 months?

SELECT month,
       ROUND(cad_usd, 4) AS cad_usd,
       ROUND(MAX(cad_usd) OVER w, 4)                          AS high_12m,
       ROUND(100 * (cad_usd / MAX(cad_usd) OVER w - 1), 2)    AS drawdown_pct
FROM scratch.monthly_wide
WHERE cad_usd IS NOT NULL
WINDOW w AS (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
ORDER BY drawdown_pct
LIMIT 5;                                       -- the deepest drawdowns

Try it 16.3

For the overnight rate since 2022, compute the running peak and the number of basis points below the peak each month. Which month first showed the rate 100bp below its peak?

WITH r AS (
    SELECT month, value AS rate,
           MAX(value) OVER (ORDER BY month
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS peak
    FROM scratch.monthly_long
    WHERE name = 'overnight_rate' AND month >= DATE '2022-01-01'
)
SELECT month, rate, peak, (peak - rate) * 100 AS bp_below_peak
FROM r
WHERE (peak - rate) * 100 >= 100
ORDER BY month
LIMIT 1;

16.4 Date-Based Frames with RANGE (13 min)

ROWS counts rows. If a month is missing, "11 preceding rows" silently covers 13 months. RANGE with an interval counts in time instead, so it's robust to gaps (the frame-level version of Day 15's self-join):

SELECT month,
       ROUND(value, 2) AS wti,
       ROUND(AVG(value) OVER (ORDER BY month
             RANGE BETWEEN INTERVAL 11 MONTH PRECEDING AND CURRENT ROW), 2) AS wti_avg_12m,
       COUNT(*) OVER (ORDER BY month
             RANGE BETWEEN INTERVAL 11 MONTH PRECEDING AND CURRENT ROW)    AS months_in_window
FROM scratch.monthly_long
WHERE name = 'wti'
ORDER BY month DESC
LIMIT 6;

Now delete a month to see the difference:

WITH gappy AS (
    SELECT month, value FROM scratch.monthly_long
    WHERE name = 'wti' AND month <> DATE '2025-06-01'      -- simulate a missing month
)
SELECT month,
       COUNT(*) OVER (ORDER BY month ROWS  BETWEEN 11 PRECEDING AND CURRENT ROW) AS rows_frame,
       COUNT(*) OVER (ORDER BY month RANGE BETWEEN INTERVAL 11 MONTH PRECEDING
                                           AND CURRENT ROW)                     AS range_frame
FROM gappy
WHERE month BETWEEN DATE '2025-05-01' AND DATE '2026-06-01'
ORDER BY month;

After the gap, the ROWS frame reaches back further in time; the RANGE frame covers exactly 12 calendar months and simply contains 11 observations.

16.5 Named Windows (8 min)

When several functions share a window definition, name it once with WINDOW:

SELECT month,
       ROUND(value, 2)             AS rate,
       ROUND(AVG(value) OVER w, 2) AS avg_12m,
       MIN(value) OVER w           AS min_12m,
       MAX(value) OVER w           AS max_12m
FROM scratch.monthly_long
WHERE name = 'fed_funds'
WINDOW w AS (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
ORDER BY month DESC
LIMIT 6;

This is the SQL equivalent of assigning a repeated expression to a variable: less typing, fewer inconsistencies.

16.6 Episodes: The Gaps-and-Islands Technique (23 min)

Economists constantly ask about episodes: How long did inflation stay above 3%? When did each yield-curve inversion start and end? These are consecutive runs ("islands") of rows sharing a condition. The technique takes three steps.

Step 1 — Flag each row with the condition. Step 2 — Mark changes: 1 when the flag differs from the previous row's flag, else 0. Step 3 — Number the episodes with a running sum of the change markers.

month    yoy   above_3  changed  episode_id (running sum)
2021-03  2.2   false    1        1
2021-04  3.4   true     1        2   ┐
2021-05  3.6   true     0        2   │ episode 2: above 3%
2021-06  3.1   true     0        2   ┘
2021-07  2.9   false    1        3

Create day16/episodes.sql:

WITH cpi AS (
    SELECT month,
           100 * (cpi / LAG(cpi, 12) OVER (ORDER BY month) - 1) AS yoy
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
      AND month >= DATE '1995-01-01'
),
flagged AS (                                                   -- step 1
    SELECT month, yoy, yoy > 3 AS above_3
    FROM cpi
    WHERE yoy IS NOT NULL
),
changes AS (                                                   -- step 2
    SELECT *,
           CASE WHEN above_3 = LAG(above_3) OVER (ORDER BY month) THEN 0 ELSE 1 END AS changed
    FROM flagged
),
episodes AS (                                                  -- step 3
    SELECT *, SUM(changed) OVER (ORDER BY month) AS episode_id
    FROM changes
)
SELECT episode_id,
       MIN(month)        AS start_month,
       MAX(month)        AS end_month,
       COUNT(*)          AS months,
       ROUND(MAX(yoy),1) AS peak_yoy
FROM episodes
WHERE above_3
GROUP BY episode_id
ORDER BY months DESC
LIMIT 5;

For the first row, LAG returns NULL, the comparison is not true, and the CASE returns 1, which correctly starts the first episode.

This pattern works for any condition: recessions (negative growth), restrictive policy (real rate > 0), CAD below parity thresholds... Keep this query as a template.

16.7 Mini-Project: Yield-Curve Inversion Episodes (20 min)

An inverted curve (10-year yield below 2-year) is a well-known recession signal, and the notebook plots the spread in Module 02. Using scratch.monthly_wide:

1. Identify every episode in which spread_10_2_bp < 0.
2. For each episode report start month, end month, length in months, the most negative spread and the month it occurred.
3. Add the change in the overnight rate over the episode (rate at end minus rate at start).
4. Bonus: add the maximum unemployment rate in the 24 months after each episode ended. (Hint: a correlated subquery or a join on a date range.)

WITH base AS (
    SELECT month, spread_10_2_bp, overnight_rate, unemployment_rate,
           spread_10_2_bp < 0 AS inverted
    FROM scratch.monthly_wide
    WHERE spread_10_2_bp IS NOT NULL
),
changes AS (
    SELECT *,
           CASE WHEN inverted = LAG(inverted) OVER (ORDER BY month) THEN 0 ELSE 1 END AS changed
    FROM base
),
numbered AS (
    SELECT *, SUM(changed) OVER (ORDER BY month) AS episode_id
    FROM changes
),
episodes AS (
    SELECT episode_id,
           MIN(month)                                AS start_month,
           MAX(month)                                AS end_month,
           COUNT(*)                                  AS months,
           ROUND(MIN(spread_10_2_bp))                AS deepest_bp,
           arg_min(month, spread_10_2_bp)            AS deepest_month,
           arg_max(overnight_rate, month)
             - arg_min(overnight_rate, month)        AS rate_change_pp
    FROM numbered
    WHERE inverted
    GROUP BY episode_id
)
SELECT e.*,
       (SELECT MAX(unemployment_rate)
        FROM scratch.monthly_wide AS w
        WHERE w.month >  e.end_month
          AND w.month <= e.end_month + INTERVAL 24 MONTH) AS max_unemp_next_24m
FROM episodes AS e
ORDER BY start_month;

Day 16 checklist

• I can define window frames with ROWS BETWEEN … and avoid the LAST_VALUE trap
• I can compute moving averages (with a min_periods equivalent), momentum and rolling volatility
• I can compute running totals, running peaks and drawdowns
• I know when RANGE with an interval is safer than ROWS
• I can identify episodes with the gaps-and-islands technique

git add .
git commit -m "Day 16: frames, rolling stats, episodes"
git push