Window Functions I: Keeping Rows While Looking Around

15.1 The Idea (23 min)

Compare two questions:

1. "What is the average overnight rate per year?" → one row per year (GROUP BY)
2. "For each month, how far is the rate from that year's average?" → one row per month, but each row needs a group statistic

Question 2 needs a window function: a calculation across a set of related rows (the window) that keeps every row.

GROUP BY year                          Window: AVG(...) OVER (PARTITION BY year)
┌──────┬──────────┐                    ┌─────────┬──────┬──────────┐
│ year │ avg_rate │                    │ month   │ rate │ year_avg │
├──────┼──────────┤                    ├─────────┼──────┼──────────┤
│ 2024 │   4.40   │                    │ 2024-01 │ 5.00 │   4.40   │
│ 2025 │   2.60   │                    │ 2024-02 │ 5.00 │   4.40   │
└──────┴──────────┘                    │   ...   │ ...  │   ...    │
  rows collapse                        │ 2025-01 │ 3.00 │   2.60   │
                                       └─────────┴──────┴──────────┘
                                         rows kept, statistic attached

Anatomy of a window function

function(...) OVER (
    PARTITION BY ...     -- split rows into independent groups (optional)
    ORDER BY ...         -- order within each group (needed for LAG, running totals...)
    ROWS BETWEEN ...     -- which neighbouring rows to include (Day 16)
)

Your first window

Create day15/windows_intro.sql:

SELECT month,
       value AS rate,
       AVG(value) OVER (PARTITION BY year(month))           AS year_avg,
       value - AVG(value) OVER (PARTITION BY year(month))   AS deviation
FROM scratch.monthly_long
WHERE name = 'overnight_rate' AND month >= DATE '2024-01-01'
ORDER BY month;

An empty OVER () means "the whole result is one window":

SELECT month, value,
       AVG(value) OVER () AS full_sample_avg
FROM scratch.monthly_long
WHERE name = 'overnight_rate'
ORDER BY month DESC
LIMIT 5;

15.2 Aggregates over a Window (23 min)

Example 1 — z-scores: how unusual is each month?

WITH spread AS (
    SELECT month, spread_10_2_bp
    FROM scratch.monthly_wide
    WHERE spread_10_2_bp IS NOT NULL
)
SELECT month,
       ROUND(spread_10_2_bp, 0) AS spread_bp,
       ROUND((spread_10_2_bp - AVG(spread_10_2_bp) OVER ())
             / STDDEV_SAMP(spread_10_2_bp) OVER (), 2) AS z_score
FROM spread
ORDER BY z_score
LIMIT 5;                                 -- the most unusually inverted months

Example 2 — Shares of a total

What share of the 2022–23 tightening did each decision contribute?

SELECT decision_date,
       change_bp,
       ROUND(100.0 * change_bp / SUM(change_bp) OVER (), 1) AS pct_of_total
FROM raw.boc_decisions
WHERE change_bp > 0
ORDER BY decision_date;

Example 3 — Several partitions in one query

SELECT decision_date,
       change_bp,
       SUM(change_bp) OVER (PARTITION BY year(decision_date))  AS year_total_bp,
       COUNT(*)       OVER (PARTITION BY year(decision_date))  AS decisions_that_year,
       SUM(change_bp) OVER ()                                  AS all_time_net_bp
FROM raw.boc_decisions
ORDER BY decision_date;

Try it 15.2

For national CPI All-items (raw.statcan_cpi, geo = 'Canada'), show for each month of 2024 the CPI level and its percentage deviation from that year's average level.

SELECT month, cpi,
       ROUND(100 * (cpi / AVG(cpi) OVER (PARTITION BY year(month)) - 1), 2) AS pct_vs_year_avg
FROM raw.statcan_cpi
WHERE geo = 'Canada' AND component = 'All-items'
  AND month BETWEEN DATE '2024-01-01' AND DATE '2024-12-01'
ORDER BY month;

15.3 Ranking (28 min)

Create day15/ranking.sql.

The ranking functions

Example 1 — The biggest BoC moves

SELECT decision_date, change_bp,
       ROW_NUMBER() OVER (ORDER BY ABS(change_bp) DESC) AS row_num,
       RANK()       OVER (ORDER BY ABS(change_bp) DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY ABS(change_bp) DESC) AS dense_rnk
FROM raw.boc_decisions
WHERE change_bp <> 0
ORDER BY rnk;

Look at how the three columns treat the many 50bp moves differently.

Example 2 — The latest observation per series

A classic pattern: rank within each group, then keep rank 1.

WITH ranked AS (
    SELECT name, month, value,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY month DESC) AS rn
    FROM scratch.monthly_long
)
SELECT name, month, ROUND(value, 3) AS latest
FROM ranked
WHERE rn = 1
ORDER BY name;

Example 3 — The same thing with QUALIFY

You can't filter on a window function in WHERE, because windows are computed after WHERE (remember the evaluation order from Day 12). Standard SQL therefore needs the CTE above. DuckDB offers QUALIFY, a WHERE for window results:

SELECT name, month, ROUND(value, 3) AS latest
FROM scratch.monthly_long
QUALIFY ROW_NUMBER() OVER (PARTITION BY name ORDER BY month DESC) = 1
ORDER BY name;

Updated evaluation order: FROM → WHERE → GROUP BY → HAVING → window functions → QUALIFY → SELECT → ORDER BY → LIMIT.

Example 4 — Top-N per group

The three highest monthly WTI prices in each year since 2020:

SELECT year(month) AS yr, month, ROUND(value, 2) AS wti
FROM scratch.monthly_long
WHERE name = 'wti' AND month >= DATE '2020-01-01'
QUALIFY RANK() OVER (PARTITION BY year(month) ORDER BY value DESC) <= 3
ORDER BY yr, wti DESC;

Example 5 — Quartiles of the policy-rate history

WITH q AS (
    SELECT month, value,
           NTILE(4) OVER (ORDER BY value) AS quartile
    FROM scratch.monthly_long
    WHERE name = 'overnight_rate'
)
SELECT quartile, MIN(value) AS from_rate, MAX(value) AS to_rate, COUNT(*) AS months
FROM q
GROUP BY quartile
ORDER BY quartile;

15.4 LAG and LEAD (30 min)

LAG(x, n) gets the value n rows before the current row; LEAD(x, n) gets the value n rows after. They're SQL's shift(n) and shift(-n). Create day15/lag_lead.sql.

Example 1 — Verifying the decision log

The change_bp column was typed in by hand. Does it agree with the rates?

SELECT decision_date,
       rate,
       LAG(rate) OVER (ORDER BY decision_date)                       AS previous_rate,
       change_bp                                                     AS recorded_bp,
       ROUND((rate - LAG(rate) OVER (ORDER BY decision_date)) * 100) AS computed_bp
FROM raw.boc_decisions
ORDER BY decision_date;

Now list only the disagreements. This is a real data-quality check:

WITH checked AS (
    SELECT decision_date, change_bp,
           ROUND((rate - LAG(rate) OVER (ORDER BY decision_date)) * 100) AS computed_bp
    FROM raw.boc_decisions
)
SELECT * FROM checked
WHERE computed_bp IS NOT NULL AND computed_bp <> change_bp;

The first row has no previous rate, so LAG returns NULL. LAG(rate, 1, 0.25) would supply a default instead (0.25% was the rate before March 2022).

Example 2 — LEAD: time until the next decision

SELECT decision_date,
       LEAD(decision_date) OVER (ORDER BY decision_date)                 AS next_decision,
       LEAD(decision_date) OVER (ORDER BY decision_date) - decision_date AS days_to_next
FROM raw.boc_decisions
ORDER BY decision_date;

Example 3 — Year-over-year inflation with LAG(…, 12)

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

Compare with the latest value from your Day 5 fact sheet. It should match exactly.

Example 4 — Many series at once with PARTITION BY

SELECT geo, month, cpi,
       ROUND(100 * (cpi / LAG(cpi, 12) OVER (PARTITION BY geo ORDER BY month) - 1), 1) AS yoy
FROM raw.statcan_cpi
WHERE component = 'All-items'
  AND geo IN ('Canada', 'Ontario', 'Quebec', 'Alberta')
QUALIFY month = MAX(month) OVER ()
ORDER BY yoy DESC;

Without PARTITION BY geo, LAG would compare Ontario with Quebec at the boundaries, the same bug that Day 6's groupby("component") avoided.

Example 5 — A gap-safe YoY with a self-join

LAG(cpi, 12) means "12 rows back", not "12 months back". If a month is missing, every later value is wrong (Day 5). A self-join on the calendar is immune to gaps:

WITH cpi AS (
    SELECT month, cpi
    FROM raw.statcan_cpi
    WHERE geo = 'Canada' AND component = 'All-items'
)
SELECT cur.month, cur.cpi, prev.cpi AS cpi_year_ago,
       ROUND(100 * (cur.cpi / prev.cpi - 1), 1) AS yoy
FROM cpi AS cur
LEFT JOIN cpi AS prev
       ON prev.month = CAST(cur.month - INTERVAL 12 MONTH AS DATE)
ORDER BY cur.month DESC
LIMIT 5;

Which to use? LAG is shorter and faster; the self-join is safer. If you have proved there are no gaps (Day 14's date spine), LAG is fine. Days 18–20 build this choice into the staging layer.

Try it 15.4

For the monthly USD/CAD rate (scratch.monthly_wide), compute the month-over-month percentage change and list the five largest CAD depreciations (USD/CAD rising most).

SELECT month,
       ROUND(usd_cad, 4) AS usd_cad,
       ROUND(100 * (usd_cad / LAG(usd_cad) OVER (ORDER BY month) - 1), 2) AS mom_pct
FROM scratch.monthly_wide
WHERE usd_cad IS NOT NULL
QUALIFY mom_pct IS NOT NULL
ORDER BY mom_pct DESC
LIMIT 5;

15.5 Indexing to a Base Period: FIRST_VALUE (15 min)

The notebook's Module 05 "indicator race" rebases series to January 2020 = 100 (index_to_100). In SQL:

WITH hs AS (
    SELECT month, component, cpi
    FROM raw.statcan_cpi
    WHERE geo = 'Canada'
      AND component IN ('All-items', 'Shelter', 'Food', 'Energy')
      AND month >= DATE '2020-01-01'           -- WHERE runs before the window!
)
SELECT month, component,
       ROUND(100 * cpi / FIRST_VALUE(cpi) OVER (PARTITION BY component ORDER BY month), 1)
           AS index_jan2020
FROM hs
QUALIFY month = MAX(month) OVER ()
ORDER BY index_jan2020 DESC;

Because WHERE is evaluated before window functions, FIRST_VALUE sees January 2020 as the first row. Move the date filter into QUALIFY instead and the base period changes to the start of the whole series. Try it to see the evaluation order in action.

LAST_VALUE looks like the mirror image of FIRST_VALUE but has a trap involving window frames. It's the first topic on Day 16.

15.6 Mini-Project: Which CPI Components Run Hottest? (18 min)

Using raw.statcan_cpi for geo = 'Canada' and the eight major components used in the notebook's Module 05 (Food, Shelter, Household operations, furnishings and equipment, Clothing and footwear, Transportation, Health and personal care, Recreation, education and reading, Alcoholic beverages, tobacco products and recreational cannabis), plus All-items:

1. Compute YoY inflation per component (partitioned LAG).
2. For each month since 2021, rank the eight components from hottest (1) to coolest.
3. Count how many months each component ranked #1 since 2021.
4. Show the latest month's ranking, with each component's YoY and its gap to All-items.

(Check the exact component names first with SELECT DISTINCT component FROM raw.statcan_cpi ORDER BY 1;, as StatCan labels are long and must match exactly.)

WITH base AS (
    SELECT month, component, cpi
    FROM raw.statcan_cpi
    WHERE geo = 'Canada'
      AND component IN ('All-items', 'Food', 'Shelter',
                        'Household operations, furnishings and equipment',
                        'Clothing and footwear', 'Transportation',
                        'Health and personal care',
                        'Recreation, education and reading',
                        'Alcoholic beverages, tobacco products and recreational cannabis')
),
yoy AS (
    SELECT month, component,
           100 * (cpi / LAG(cpi, 12) OVER (PARTITION BY component ORDER BY month) - 1) AS yoy
    FROM base
),
ranked AS (
    SELECT y.month, y.component, y.yoy,
           y.yoy - h.yoy AS gap_to_headline,
           RANK() OVER (PARTITION BY y.month ORDER BY y.yoy DESC) AS heat_rank
    FROM yoy AS y
    JOIN yoy AS h ON h.month = y.month AND h.component = 'All-items'
    WHERE y.component <> 'All-items'
      AND y.month >= DATE '2021-01-01'
      AND y.yoy IS NOT NULL
)
-- 3. Months at #1
SELECT component, COUNT(*) AS months_hottest
FROM ranked
WHERE heat_rank = 1
GROUP BY component
ORDER BY months_hottest DESC;

-- 4. Latest ranking: replace the final SELECT above with
-- SELECT component, ROUND(yoy, 1) AS yoy, ROUND(gap_to_headline, 1) AS gap, heat_rank
-- FROM ranked
-- QUALIFY month = MAX(month) OVER ()
-- ORDER BY heat_rank;

Day 15 checklist

• I can explain the difference between GROUP BY and a window function
• I can compute group statistics without collapsing rows (AVG … OVER (PARTITION BY …))
• I can rank with ROW_NUMBER, RANK, DENSE_RANK, NTILE, and filter with QUALIFY
• I can compute changes and growth rates with LAG/LEAD, correctly partitioned
• I know when a gap-safe self-join beats LAG
• I can rebase series to an index with FIRST_VALUE

git add .
git commit -m "Day 15: window functions — ranking, LAG/LEAD, indexing"
git push