Intermediate Models: Inflation Measures in SQL

19.1 int_cpi__growth: Gap-Safe Growth Rates (30 min)

On Day 15 you had two choices: LAG(cpi, 12) (fast, but wrong if a month is missing) or a self-join (safe, but heavier). Here's a third technique that gets both: lag the date too, and only compute the rate when the lagged date is exactly right.

Create sql/20_int_cpi__growth.sql:

-- intermediate.int_cpi__growth
-- Grain:  one row per month × geography × component (same as staging)
-- Adds:   mom, ann_3m, ann_6m, yoy (all %), each NULL unless the comparison
--         month is exactly 1/3/6/12 months earlier (gap-safe).

CREATE OR REPLACE VIEW intermediate.int_cpi__growth AS
WITH base AS (
    SELECT month, geo, geo_code, geo_level, component, cpi
    FROM staging.stg_statcan__cpi
    WHERE NOT is_terminated
),

lagged AS (
    SELECT *,
           LAG(cpi, 1)    OVER w AS cpi_l1,   LAG(month, 1)  OVER w AS month_l1,
           LAG(cpi, 3)    OVER w AS cpi_l3,   LAG(month, 3)  OVER w AS month_l3,
           LAG(cpi, 6)    OVER w AS cpi_l6,   LAG(month, 6)  OVER w AS month_l6,
           LAG(cpi, 12)   OVER w AS cpi_l12,  LAG(month, 12) OVER w AS month_l12
    FROM base
    WINDOW w AS (PARTITION BY geo, component ORDER BY month)
)

SELECT month, geo, geo_code, geo_level, component, cpi,
       CASE WHEN month_l1 = CAST(month - INTERVAL 1 MONTH AS DATE)
            THEN 100 * (cpi / cpi_l1 - 1) END                    AS mom,
       CASE WHEN month_l3 = CAST(month - INTERVAL 3 MONTH AS DATE)
            THEN 100 * (POWER(cpi / cpi_l3, 4) - 1) END          AS ann_3m,
       CASE WHEN month_l6 = CAST(month - INTERVAL 6 MONTH AS DATE)
            THEN 100 * (POWER(cpi / cpi_l6, 2) - 1) END          AS ann_6m,
       CASE WHEN month_l12 = CAST(month - INTERVAL 12 MONTH AS DATE)
            THEN 100 * (cpi / cpi_l12 - 1) END                   AS yoy
FROM lagged;

Understanding the annualisation exponents:

Seasonality reminder: table 18-10-0004 is not seasonally adjusted. yoy is robust to seasonality; mom, ann_3m and ann_6m are not. The BoC and StatCan publish seasonally adjusted series for short-horizon analysis. We keep NSA-based momentum here as a teaching tool and label it clearly in the marts.

Check it against published figures

.read sql/01_schemas.sql
.read sql/20_int_cpi__growth.sql

SELECT month, cpi, ROUND(mom, 1) AS mom, ROUND(yoy, 1) AS yoy
FROM intermediate.int_cpi__growth
WHERE geo_code = 'CA' AND component = 'All-items'
ORDER BY month DESC
LIMIT 6;

StatCan computes published percentage changes from the rounded index levels, which is exactly what this table contains. So your yoy rounded to one decimal should match the Daily release precisely. Compare with the notebook's "Latest CPI YoY" printout too.

Round only for display. Keep full precision in the models; round in the final SELECT of a report. Rounding early and then computing again compounds errors.

Try it 19.1

Delete (in a CTE) the row for one mid-2024 month of national All-items and show that yoy for the month 12 months later becomes NULL instead of wrong.

WITH gappy AS (
    SELECT month, cpi
    FROM staging.stg_statcan__cpi
    WHERE geo_code = 'CA' AND component = 'All-items'
      AND month <> DATE '2024-06-01'
),
lagged AS (
    SELECT *, LAG(cpi, 12) OVER (ORDER BY month)   AS cpi_l12,
              LAG(month, 12) OVER (ORDER BY month) AS month_l12
    FROM gappy
)
SELECT month,
       100 * (cpi / cpi_l12 - 1) AS naive_yoy,
       CASE WHEN month_l12 = CAST(month - INTERVAL 12 MONTH AS DATE)
            THEN 100 * (cpi / cpi_l12 - 1) END AS safe_yoy
FROM lagged
WHERE month BETWEEN DATE '2025-05-01' AND DATE '2025-08-01'
ORDER BY month;

19.2 Smoothing: Moving Averages and Acceleration (20 min)

We layer smoothing on top of growth rates in a second view, keeping each model focused.

Add to sql/20_int_cpi__growth.sql:

-- intermediate.int_cpi__smoothed
-- Grain: month × geo × component. Adds moving averages of yoy and its 12-month change.
CREATE OR REPLACE VIEW intermediate.int_cpi__smoothed AS
SELECT *,
       CASE WHEN COUNT(yoy) OVER w3 = 3   THEN AVG(yoy) OVER w3  END AS yoy_ma3,
       CASE WHEN COUNT(yoy) OVER w12 = 12 THEN AVG(yoy) OVER w12 END AS yoy_ma12,
       CASE WHEN COUNT(mom) OVER w3 = 3   THEN AVG(mom) OVER w3  END AS mom_ma3,
       yoy - LAG(yoy, 12) OVER w_lag                                  AS yoy_change_12m
FROM intermediate.int_cpi__growth
WINDOW w_lag AS (PARTITION BY geo, component ORDER BY month),
       w3    AS (PARTITION BY geo, component ORDER BY month
                 RANGE BETWEEN INTERVAL 2 MONTH PRECEDING AND CURRENT ROW),
       w12   AS (PARTITION BY geo, component ORDER BY month
                 RANGE BETWEEN INTERVAL 11 MONTH PRECEDING AND CURRENT ROW);

Three techniques from Day 16 combine here:

• RANGE with intervals, so a window always covers the right calendar months;
• COUNT(...) = n, pandas' min_periods, so partial windows give NULL;
• named windows, keeping the definitions readable.

yoy_change_12m measures acceleration: is inflation higher or lower than a year ago, in percentage points?

SELECT month, ROUND(yoy, 1) AS yoy, ROUND(yoy_ma3, 2) AS ma3,
       ROUND(yoy_ma12, 2) AS ma12, ROUND(yoy_change_12m, 1) AS accel_pp
FROM intermediate.int_cpi__smoothed
WHERE geo_code = 'CA' AND component = 'All-items'
ORDER BY month DESC
LIMIT 6;

19.3 Base Effects (23 min)

Why does YoY inflation sometimes jump even when prices barely move this month? Because YoY also depends on what happened a year ago. That's the base effect.

Year-over-year inflation is (approximately) the sum of the last 12 monthly changes:

yoy_t ≈ mom_t + mom_t−1 + … + mom_t−11

Next month, one new monthly change enters the window and the change from 12 months ago drops out. If a big increase drops out, YoY falls mechanically.

A useful projection: next month's YoY if prices stay flat

If the index is unchanged next month (P_t+1 = P_t), then:

yoy_t+1 = P_t / P_t−11 − 1

Create day19/base_effects.sql:

WITH s AS (
    SELECT month, cpi, mom, yoy,
           LAG(cpi, 11) OVER w   AS cpi_l11,
           LAG(month, 11) OVER w AS month_l11,
           LAG(mom, 11) OVER w   AS mom_dropping_out     -- leaves the window next month
    FROM intermediate.int_cpi__growth
    WHERE geo_code = 'CA' AND component = 'All-items'
    WINDOW w AS (ORDER BY month)
)
SELECT month,
       ROUND(yoy, 1)                                          AS yoy,
       ROUND(mom_dropping_out, 2)                             AS mom_leaving_window,
       ROUND(CASE WHEN month_l11 = CAST(month - INTERVAL 11 MONTH AS DATE)
                  THEN 100 * (cpi / cpi_l11 - 1) END, 1)      AS next_yoy_if_flat
FROM s
ORDER BY month DESC
LIMIT 6;

How to read it: if next_yoy_if_flat is well below today's yoy, a large monthly increase is about to leave the window, and headline inflation will fall unless this month's price change is large.

Try it 19.3

Check the approximation: for the latest month, compare yoy with the sum of the last 12 mom values. How big is the gap, and why isn't it zero?

SELECT month, ROUND(yoy, 2) AS yoy,
       ROUND(SUM(mom) OVER (ORDER BY month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW), 2)
           AS sum_12_mom
FROM intermediate.int_cpi__growth
WHERE geo_code = 'CA' AND component = 'All-items'
QUALIFY month = MAX(month) OVER ()

19.4 Core Measures, Median and Diffusion (23 min)

Headline CPI is volatile (energy, food). Central banks watch core measures. The BoC's preferred measures, CPI-trim and CPI-median, are computed from about 55 detailed components with seasonally adjusted data and official weights. We'll build simpler teaching proxies from what we have, and label them honestly.

Step 1 — Find the exclusion aggregates StatCan publishes

SELECT DISTINCT component
FROM staging.stg_statcan__cpi
WHERE geo_code = 'CA' AND component ILIKE 'All-items excluding%'
ORDER BY component;

You'll see aggregates such as All-items excluding food and energy. Use the exact labels your query returns in the next steps.

Step 2 — A median across major components (a teaching proxy)

WITH majors AS (
    SELECT g.month, g.component, g.yoy
    FROM intermediate.int_cpi__growth AS g
    JOIN seeds.seed_cpi_weights AS w USING (component)       -- the 7 major groups
    WHERE g.geo_code = 'CA' AND g.yoy IS NOT NULL
)
SELECT month,
       ROUND(MEDIAN(yoy), 2)                          AS median_major_yoy,
       ROUND(MIN(yoy), 1)                             AS min_yoy,
       ROUND(MAX(yoy), 1)                             AS max_yoy,
       COUNT(*)                                       AS n_components
FROM majors
GROUP BY month
ORDER BY month DESC
LIMIT 6;

Joining to the seed table is a neat way to say "only these components". The list of components now lives in one place, not in every query.

Step 3 — Diffusion: how broad-based is inflation?

A diffusion index is the share of components above a threshold. High diffusion means price pressure is broad, not driven by one or two categories.

WITH majors AS (
    SELECT g.month, g.yoy
    FROM intermediate.int_cpi__growth AS g
    JOIN seeds.seed_cpi_weights USING (component)
    WHERE g.geo_code = 'CA' AND g.yoy IS NOT NULL
)
SELECT month,
       ROUND(100.0 * COUNT(*) FILTER (WHERE yoy > 3) / COUNT(*), 0) AS pct_above_3,
       ROUND(100.0 * COUNT(*) FILTER (WHERE yoy < 1) / COUNT(*), 0) AS pct_below_1
FROM majors
GROUP BY month
ORDER BY month DESC
LIMIT 12;

With only seven components, this index is coarse. Applied to the hundreds of detailed product groups in the full table, it becomes a serious indicator. Try it as a stretch goal (hint: filter to leaf-level components carefully, since aggregates would double-count).

19.5 Contributions to Headline Inflation (20 min)

The notebook's Module 05 stacks each component's contribution: yoy × weight / 100. Let's reproduce it and quantify how far the approximation is from the headline.

WITH comp AS (
    SELECT g.month, g.component, g.yoy, w.weight_pct,
           g.yoy * w.weight_pct / 100 AS contribution_pp
    FROM intermediate.int_cpi__growth AS g
    JOIN seeds.seed_cpi_weights AS w USING (component)
    WHERE g.geo_code = 'CA' AND g.yoy IS NOT NULL
),
headline AS (
    SELECT month, yoy AS headline_yoy
    FROM intermediate.int_cpi__growth
    WHERE geo_code = 'CA' AND component = 'All-items'
)
SELECT c.month,
       ROUND(h.headline_yoy, 2)                           AS headline_yoy,
       ROUND(SUM(c.contribution_pp), 2)                   AS sum_of_contributions,
       ROUND(h.headline_yoy - SUM(c.contribution_pp), 2)  AS residual,
       ROUND(SUM(c.weight_pct), 1)                        AS weights_covered
FROM comp AS c
JOIN headline AS h USING (month)
GROUP BY c.month, h.headline_yoy
ORDER BY c.month DESC
LIMIT 6;

Why the residual isn't zero:

1. The seven weights cover about 96% of the basket (alcohol and tobacco are missing).
2. The weights are illustrative and fixed, while official ones are updated with each basket revision.
3. The exact method uses price-updated weights (each component's weight adjusted by its relative price change since the basket reference period).

Lesson: always show the residual. A decomposition that silently doesn't add up misleads readers; one that reports its residual is honest and checkable.

Pivot for charting

WITH comp AS (
    SELECT g.month, g.component, g.yoy * w.weight_pct / 100 AS contribution_pp
    FROM intermediate.int_cpi__growth AS g
    JOIN seeds.seed_cpi_weights AS w USING (component)
    WHERE g.geo_code = 'CA' AND g.month >= DATE '2021-01-01'
)
PIVOT comp ON component USING first(ROUND(contribution_pp, 2))
ORDER BY month DESC
LIMIT 3;

19.6 Mini-Project: int_cpi__national_measures + Tests (20 min)

Create sql/21_int_cpi__national_measures.sql defining a wide, national, monthly view with one row per month. It should include:

Then add two tests:

• sql/tests/national_measures_unique_month.sql (error): month unique.
• sql/tests/national_measures_yoy_range.sql (error): headline_yoy between −5 and 20 when not null.

-- intermediate.int_cpi__national_measures
-- Grain: one row per month (Canada). All rates in %.
-- Momentum measures (mom, ann_3m) are based on NSA data — interpret with care.

CREATE OR REPLACE VIEW intermediate.int_cpi__national_measures AS
WITH nat AS (
    SELECT * FROM intermediate.int_cpi__smoothed WHERE geo_code = 'CA'
),

headline AS (
    SELECT month,
           cpi                AS cpi_headline,
           yoy                AS headline_yoy,
           mom                AS headline_mom,
           ann_3m             AS headline_ann_3m,
           yoy_ma3            AS headline_yoy_ma3,
           yoy_ma12           AS headline_yoy_ma12,
           yoy_change_12m     AS headline_accel_12m,
           LAG(cpi, 11)   OVER (ORDER BY month) AS cpi_l11,
           LAG(month, 11) OVER (ORDER BY month) AS month_l11
    FROM nat
    WHERE component = 'All-items'
),

components AS (
    SELECT month,
           MAX(yoy) FILTER (WHERE component = 'All-items excluding food and energy') AS core_ex_fe_yoy,
           MAX(yoy) FILTER (WHERE component = 'Shelter')                             AS shelter_yoy,
           MAX(yoy) FILTER (WHERE component = 'Energy')                              AS energy_yoy,
           MAX(yoy) FILTER (WHERE component = 'Food')                                AS food_yoy
    FROM nat
    GROUP BY month
),

majors AS (
    SELECT n.month,
           MEDIAN(n.yoy)                                                 AS median_major_yoy,
           100.0 * COUNT(*) FILTER (WHERE n.yoy > 3) / COUNT(*)          AS diffusion_above_3,
           SUM(n.yoy * w.weight_pct / 100)                               AS contrib_sum
    FROM nat AS n
    JOIN seeds.seed_cpi_weights AS w USING (component)
    WHERE n.yoy IS NOT NULL
    GROUP BY n.month
)

SELECT h.month,
       h.cpi_headline,
       h.headline_yoy,
       h.headline_mom,
       h.headline_ann_3m,
       h.headline_yoy_ma3,
       h.headline_yoy_ma12,
       h.headline_accel_12m,
       c.core_ex_fe_yoy,
       c.shelter_yoy,
       c.energy_yoy,
       c.food_yoy,
       m.median_major_yoy,
       m.diffusion_above_3,
       m.contrib_sum,
       h.headline_yoy - m.contrib_sum                                    AS contrib_residual,
       CASE WHEN h.month_l11 = CAST(h.month - INTERVAL 11 MONTH AS DATE)
            THEN 100 * (h.cpi_headline / h.cpi_l11 - 1) END              AS next_yoy_if_flat
FROM headline AS h
LEFT JOIN components AS c USING (month)
LEFT JOIN majors     AS m USING (month);

-- sql/tests/national_measures_unique_month.sql
-- severity: error
SELECT month, COUNT(*) FROM intermediate.int_cpi__national_measures
GROUP BY month HAVING COUNT(*) > 1

-- sql/tests/national_measures_yoy_range.sql
-- severity: error
SELECT month, headline_yoy FROM intermediate.int_cpi__national_measures
WHERE headline_yoy IS NOT NULL AND (headline_yoy < -5 OR headline_yoy > 20)

Day 19 checklist

• I compute gap-safe growth rates by lagging the date alongside the value
• I annualise correctly and know which measures are sensitive to seasonality
• I layer smoothing models on top of growth models
• I can explain and quantify base effects
• I can build (and honestly label) median, diffusion and contribution measures, showing residuals
• My intermediate models are tested for uniqueness and plausible ranges

python run_tests.py
git add .
git commit -m "Day 19: CPI growth, smoothing, base effects, national measures"
git push