Common Table Expressions: Writing SQL in Steps

14.1 Why CTEs? (20 min)

Question: "For each series, show its latest value next to its average since 2020." With only Day 12 tools, you'd nest subqueries:

SELECT l.name, l.latest_value, a.avg_since_2020
FROM (
    SELECT name, arg_max(value, date) AS latest_value
    FROM raw.observations
    GROUP BY name
) AS l
JOIN (
    SELECT name, AVG(value) AS avg_since_2020
    FROM raw.observations
    WHERE date >= '2020-01-01'
    GROUP BY name
) AS a USING (name)
ORDER BY l.name;

It works, but you have to read it inside-out. A Common Table Expression (CTE) names each step up front:

WITH latest AS (
    SELECT name, arg_max(value, date) AS latest_value
    FROM raw.observations
    GROUP BY name
),
averages AS (
    SELECT name, AVG(value) AS avg_since_2020
    FROM raw.observations
    WHERE date >= '2020-01-01'
    GROUP BY name
)
SELECT name, latest_value, ROUND(avg_since_2020, 3) AS avg_since_2020
FROM latest
JOIN averages USING (name)
ORDER BY name;

Now the query reads top to bottom, like a Python script:

latest = ...
averages = ...
result = latest.merge(averages, on="name")

CTE = a named, temporary table that exists only for the duration of one query. It's the SQL version of assigning an intermediate DataFrame to a variable.

14.2 WITH Syntax (25 min)

Create day14/cte_basics.sql.

Example 1 — A single CTE

WITH rates AS (
    SELECT CAST(date AS DATE) AS date, value AS overnight_rate
    FROM raw.observations
    WHERE name = 'overnight_rate'
)
SELECT MIN(date) AS first_date, MAX(date) AS last_date,
       MIN(overnight_rate) AS lowest, MAX(overnight_rate) AS highest
FROM rates;

Why CAST(date AS DATE)? Dates coming from pandas are stored as timestamps (date + time). For daily and monthly economic data, a plain DATE is simpler to compare and join. We cast once, in the first step, and never think about it again.

Example 2 — Chained CTEs (each step uses the previous one)

WITH rates AS (
    SELECT CAST(date AS DATE) AS date, value AS overnight_rate
    FROM raw.observations
    WHERE name = 'overnight_rate'
),
yearly AS (
    SELECT year(date) AS yr,
           AVG(overnight_rate) AS avg_rate,
           MAX(overnight_rate) AS max_rate
    FROM rates
    GROUP BY yr
),
labelled AS (
    SELECT *,
           CASE WHEN avg_rate < 2.25  THEN 'Stimulative'
                WHEN avg_rate <= 2.75 THEN 'Neutral'
                ELSE 'Restrictive' END AS stance
    FROM yearly
)
SELECT * FROM labelled ORDER BY yr;

The rules:

• One WITH keyword, then CTEs separated by commas.
• No comma after the last CTE.
• A CTE can reference any CTE defined above it.
• The final SELECT is the query's output.

Example 3 — Reusing one CTE twice

WITH decisions AS (
    SELECT * FROM raw.boc_decisions
),
hikes AS (SELECT COUNT(*) AS n FROM decisions WHERE change_bp > 0),
cuts  AS (SELECT COUNT(*) AS n FROM decisions WHERE change_bp < 0)
SELECT hikes.n AS hikes, cuts.n AS cuts
FROM hikes, cuts;                        -- one row each, so this cross join is safe

CTE vs view vs table

In dbt (Days 31–40), every model is essentially a SELECT with CTEs, and dbt decides whether to materialise it as a view or a table.

Try it 14.2

Using CTEs, find for each year the number of days the overnight rate was above 4% and the share of that year's observations this represents (as a percentage).

WITH rates AS (
    SELECT CAST(date AS DATE) AS date, value AS rate
    FROM raw.observations
    WHERE name = 'overnight_rate'
),
by_year AS (
    SELECT year(date) AS yr,
           COUNT(*)                             AS n_obs,
           COUNT(*) FILTER (WHERE rate > 4)     AS n_above_4
    FROM rates
    GROUP BY yr
)
SELECT yr, n_above_4, ROUND(100.0 * n_above_4 / n_obs, 1) AS pct_above_4
FROM by_year
WHERE n_above_4 > 0
ORDER BY yr;

14.3 A Cleaning Chain: raw → typed → monthly (30 min)

This is the most common real-world use of CTEs: a small pipeline inside one query. Create day14/monthly_chain.sql.

Step 1 — Typed daily/monthly observations

WITH obs AS (
    SELECT CAST(date AS DATE) AS date, name, value
    FROM raw.observations
)
SELECT name, COUNT(*) AS n, MIN(date) AS first, MAX(date) AS last
FROM obs
GROUP BY name
ORDER BY name;

Step 2 — Collapse to monthly with the right rule for each series

Day 6 taught that aggregation must make economic sense: last for policy rates, mean for prices and yields.

WITH obs AS (
    SELECT CAST(date AS DATE) AS date, name, value
    FROM raw.observations
),
monthly AS (
    SELECT name,
           CAST(date_trunc('month', date) AS DATE) AS month,
           CASE
               WHEN name IN ('overnight_rate', 'fed_funds')
                   THEN arg_max(value, date)          -- value on the last day of the month
               ELSE AVG(value)                        -- monthly average
           END AS value,
           COUNT(*) AS n_obs_in_month
    FROM obs
    GROUP BY name, month
)
SELECT * FROM monthly
WHERE name IN ('overnight_rate', 'cad_usd')
ORDER BY name, month DESC
LIMIT 10;

The CASE sits outside the aggregates. That's allowed because name is in the GROUP BY, so it has a single value per group.

Step 3 — Label with the dimension table

WITH obs AS (
    SELECT CAST(date AS DATE) AS date, series_id, name, value
    FROM raw.observations
),
monthly AS (
    SELECT series_id, name,
           CAST(date_trunc('month', date) AS DATE) AS month,
           CASE WHEN name IN ('overnight_rate', 'fed_funds')
                THEN arg_max(value, date) ELSE AVG(value) END AS value
    FROM obs
    GROUP BY ALL
),
labelled AS (
    SELECT m.month, m.name, d.source, m.value
    FROM monthly AS m
    JOIN raw.dim_series AS d USING (series_id)
)
SELECT source, name, COUNT(*) AS months, MAX(month) AS latest_month
FROM labelled
GROUP BY ALL
ORDER BY source, name;

Debugging tip: to inspect an intermediate step, change only the final line to SELECT * FROM monthly LIMIT 20;. That's the SQL equivalent of printing a variable halfway through a script.

14.4 Long ↔ Wide (23 min)

Our data is long (one row per month and series). For comparisons across series we want wide (one row per month). There are three ways to get there in SQL.

Method 1 — Conditional aggregation (portable to any database)

WITH monthly AS (
    SELECT name,
           CAST(date_trunc('month', CAST(date AS DATE)) AS DATE) AS month,
           CASE WHEN name IN ('overnight_rate', 'fed_funds')
                THEN arg_max(value, date) ELSE AVG(value) END AS value
    FROM raw.observations
    GROUP BY ALL
)
SELECT month,
       MAX(value) FILTER (WHERE name = 'overnight_rate') AS overnight_rate,
       MAX(value) FILTER (WHERE name = 'fed_funds')      AS fed_funds,
       MAX(value) FILTER (WHERE name = 'yield_2yr')      AS yield_2yr,
       MAX(value) FILTER (WHERE name = 'yield_10yr')     AS yield_10yr
FROM monthly
GROUP BY month
ORDER BY month DESC
LIMIT 6;

Each FILTER picks the single value for that series in that month. MAX is just a way to turn "one value" into an aggregate.

Method 2 — DuckDB's PIVOT statement

CREATE OR REPLACE TEMP TABLE monthly_long AS
SELECT name,
       CAST(date_trunc('month', CAST(date AS DATE)) AS DATE) AS month,
       CASE WHEN name IN ('overnight_rate', 'fed_funds')
            THEN arg_max(value, date) ELSE AVG(value) END AS value
FROM raw.observations
GROUP BY ALL;

PIVOT monthly_long ON name USING first(value) ORDER BY month DESC LIMIT 6;

PIVOT creates one column per distinct name automatically. That's convenient for exploration, but new series change the output's columns, which can surprise downstream code. For pipelines, prefer Method 1's explicit columns.

A TEMP table disappears when you close the session, which makes it handy for experiments.

Method 3 — UNPIVOT: wide back to long

CREATE OR REPLACE TEMP TABLE monthly_wide AS
PIVOT monthly_long ON name USING first(value);

UNPIVOT monthly_wide
ON COLUMNS(* EXCLUDE (month))
INTO NAME name VALUE value
ORDER BY month DESC, name
LIMIT 10;

COLUMNS(* EXCLUDE (month)) means "every column except month", a DuckDB shortcut you'll see again on Day 17.

14.5 Recursive CTEs and Date Spines (20 min)

Recursive CTEs: SQL's while loop

A WITH RECURSIVE CTE starts with a base row, then repeatedly applies a step to produce new rows until a condition stops it. Remember Day 2's doubling-time loop?

WITH RECURSIVE growth(year, level) AS (
    SELECT 0, 100.0                          -- base case
    UNION ALL
    SELECT year + 1, level * 1.02            -- step
    FROM growth
    WHERE level < 200                        -- stop condition
)
SELECT MAX(year) AS years_to_double FROM growth;     -- 36, same as Day 2

And the Day 2 exercise of cutting from 5.00% in 25bp steps down to the neutral band:

WITH RECURSIVE path(cut, rate) AS (
    SELECT 0, 5.00
    UNION ALL
    SELECT cut + 1, rate - 0.25 FROM path WHERE rate > 2.75
)
SELECT * FROM path;

Recursive CTEs are powerful but rarely needed in everyday analytics. The next tool is used far more often.

Date spines: a complete calendar

Economic time series should have no missing periods (Day 5's gap check). A date spine is a table containing every expected period. Compare your data against it to find holes:

SELECT CAST(month AS DATE) AS month
FROM range(DATE '2024-01-01', DATE '2025-01-01', INTERVAL 1 MONTH) AS t(month);

range(start, stop, step) excludes the stop value; generate_series includes it.

Finding missing months per series

WITH monthly AS (
    SELECT DISTINCT name,
           CAST(date_trunc('month', CAST(date AS DATE)) AS DATE) AS month
    FROM raw.observations
),
spine AS (
    SELECT CAST(month AS DATE) AS month
    FROM range(DATE '2018-01-01', DATE '2025-01-01', INTERVAL 1 MONTH) AS t(month)
),
expected AS (
    SELECT s.name, sp.month
    FROM (SELECT DISTINCT name FROM monthly) AS s
    CROSS JOIN spine AS sp                     -- every series × every month
)
SELECT name, COUNT(*) AS missing_months, MIN(month) AS first_missing
FROM expected
ANTI JOIN monthly USING (name, month)
GROUP BY name
ORDER BY missing_months DESC;

The cross join builds what should exist; the anti-join (Day 12) finds what doesn't. Quarterly series such as US GDP will show gaps here, which is expected. Checks must respect each series' frequency. Day 17 revisits this with the frequency column of a richer dimension table.

14.6 Mini-Project: The scratch.monthly_* Views (18 min)

Create day14/monthly_views.sql that defines two views you'll reuse on Days 15–17:

1. scratch.monthly_long: columns month, name, value, using the aggregation rules from 14.3, for all series in raw.observations.
2. scratch.monthly_wide: one row per month from 2006-01-01, with explicit columns overnight_rate, fed_funds, yield_2yr, yield_10yr, cad_usd, wti, oecd_cli, unemployment_rate, plus derived columns boc_fed_diff, spread_10_2_bp and usd_cad.

Then check the latest 3 rows against your Day 7 latest_values.csv.

CREATE OR REPLACE VIEW scratch.monthly_long AS
WITH obs AS (
    SELECT CAST(date AS DATE) AS date, name, value
    FROM raw.observations
)
SELECT CAST(date_trunc('month', date) AS DATE) AS month,
       name,
       CASE WHEN name IN ('overnight_rate', 'fed_funds')
            THEN arg_max(value, date) ELSE AVG(value) END AS value
FROM obs
GROUP BY ALL;


CREATE OR REPLACE VIEW scratch.monthly_wide AS
WITH wide AS (
    SELECT month,
           MAX(value) FILTER (WHERE name = 'overnight_rate')    AS overnight_rate,
           MAX(value) FILTER (WHERE name = 'fed_funds')         AS fed_funds,
           MAX(value) FILTER (WHERE name = 'yield_2yr')         AS yield_2yr,
           MAX(value) FILTER (WHERE name = 'yield_10yr')        AS yield_10yr,
           MAX(value) FILTER (WHERE name = 'cad_usd')           AS cad_usd,
           MAX(value) FILTER (WHERE name = 'wti')               AS wti,
           MAX(value) FILTER (WHERE name = 'oecd_cli')          AS oecd_cli,
           MAX(value) FILTER (WHERE name = 'unemployment_rate') AS unemployment_rate
    FROM scratch.monthly_long
    WHERE month >= DATE '2006-01-01'
    GROUP BY month
)
SELECT *,
       overnight_rate - fed_funds        AS boc_fed_diff,
       (yield_10yr - yield_2yr) * 100    AS spread_10_2_bp,
       1 / cad_usd                       AS usd_cad
FROM wide;


SELECT * FROM scratch.monthly_wide ORDER BY month DESC LIMIT 3;

Day 14 checklist

• I can rewrite nested subqueries as readable, chained CTEs
• I can debug a CTE chain by selecting from an intermediate step
• I can aggregate daily data to monthly with economically sensible rules
• I can go long → wide (conditional aggregation, PIVOT) and back (UNPIVOT)
• I can build a date spine and find missing periods with a cross join + anti-join

git add .
git commit -m "Day 14: CTEs, monthly chain, pivots, date spines"
git push