First Steps with SQL in the DuckDB CLI

11.1 Installing DuckDB and First Contact (18 min)

Step 1 — Install the command-line interface (CLI)

In the Codespaces terminal:

cd /workspaces/macro-data-course
curl https://install.duckdb.org | sh

The installer tells you where it put the program (usually ~/.duckdb/cli/latest/duckdb). Add that folder to your PATH so you can type duckdb anywhere:

echo 'export PATH="$HOME/.duckdb/cli/latest:$PATH"' >> ~/.bashrc
source ~/.bashrc
duckdb --version

wget https://github.com/duckdb/duckdb/releases/latest/download/duckdb_cli-linux-amd64.zip
unzip duckdb_cli-linux-amd64.zip -d ~/.local/bin
rm duckdb_cli-linux-amd64.zip
duckdb --version

Also install the Python package now; we'll use it on Day 13:

source .venv/bin/activate
pip install duckdb
pip freeze > requirements.txt

Step 2 — Your first queries

Start DuckDB in memory (nothing is saved):

duckdb

The prompt changes to D. SQL statements end with a semicolon ;.

SELECT 2.25 - 3.0;
SELECT 2.25 - 3.0 AS real_rate;
SELECT (1 + 0.0225) / (1 + 0.03) - 1 AS real_rate_exact;
SELECT 3180 * POWER(1.015, 5) AS gdp_in_5_years;

These are the Day 1 calculations, now in SQL. AS gives a result column a name, called an alias.

Step 3 — Dot commands (CLI settings, not SQL)

Commands starting with . control the CLI itself and don't need a semicolon:

.mode box
.timer on
SELECT 'Hello, DuckDB' AS greeting, 2026 AS year;

Step 4 — A persistent database file

Exit with .quit, then open (or create) a database file:

duckdb day11/practice.duckdb

Anything you create is now saved to that file and will still be there next time.

Working style for Days 11–12: write SQL in a .sql file in VS Code (you get syntax highlighting and can save your work), then run it in the CLI with .read day11/filename.sql. For quick experiments, type directly at the D prompt.

11.2 Tables: CREATE TABLE and INSERT (20 min)

A table is a DataFrame with a fixed schema: every column has a name and a declared type. Create day11/provinces.sql:

-- Provincial fiscal positions FY2026 (% of provincial GDP) — from the course notebook
CREATE OR REPLACE TABLE provinces (
    province        VARCHAR,
    region          VARCHAR,
    balance_pct_gdp DOUBLE
);

INSERT INTO provinces VALUES
    ('BC', 'West',     -0.8),
    ('AB', 'West',      0.2),
    ('SK', 'Prairies',  0.5),
    ('MB', 'Prairies', -0.9),
    ('ON', 'Central',  -1.4),
    ('QC', 'Central',  -1.2),
    ('NB', 'Atlantic', -0.5),
    ('NS', 'Atlantic', -0.3),
    ('PE', 'Atlantic', -0.7),
    ('NL', 'Atlantic',  0.1);

Run it:

.read day11/provinces.sql
.tables
DESCRIBE provinces;
SELECT * FROM provinces;

Notes:

• -- starts a comment (like # in Python).
• Text values use single quotes 'BC'. Double quotes are for column names that contain spaces (Day 13).
• CREATE OR REPLACE lets you rerun the file without "table already exists" errors.
• SQL keywords are case-insensitive. Writing them in CAPITALS is a convention that makes queries easier to read.

Now a second table: the notebook's BoC decision log. Create day11/decisions.sql:

CREATE OR REPLACE TABLE boc_decisions (
    decision_date DATE,
    rate          DOUBLE,
    change_bp     INTEGER,
    note          VARCHAR
);

INSERT INTO boc_decisions VALUES
    ('2022-03-02', 0.50,   25, 'First 2022 hike'),
    ('2022-04-13', 1.00,   50, 'Accelerating'),
    ('2022-06-01', 1.50,   50, NULL),
    ('2022-07-13', 2.50,  100, 'Jumbo hike'),
    ('2022-09-07', 3.25,   75, NULL),
    ('2022-10-26', 3.75,   50, NULL),
    ('2022-12-07', 4.25,   50, NULL),
    ('2023-01-25', 4.25,    0, 'Conditional pause'),
    ('2023-03-08', 4.25,    0, NULL),
    ('2023-04-12', 4.25,    0, NULL),
    ('2023-06-07', 4.75,   50, 'Resumed hiking'),
    ('2023-07-12', 5.00,   25, 'Peak rate'),
    ('2023-09-06', 5.00,    0, NULL),
    ('2024-06-05', 4.75,  -25, 'First cut'),
    ('2024-07-24', 4.50,  -25, NULL),
    ('2024-09-04', 4.25,  -25, NULL),
    ('2024-10-23', 3.75,  -50, 'Accelerating cuts'),
    ('2024-12-11', 3.25,  -50, NULL),
    ('2025-01-29', 3.00,  -25, NULL),
    ('2025-03-12', 2.75,  -25, NULL),
    ('2025-04-16', 2.25,  -50, 'Tariff shock'),
    ('2025-06-04', 2.25,    0, NULL),
    ('2025-07-30', 2.25,    0, NULL),
    ('2025-09-10', 2.25,    0, NULL),
    ('2025-10-29', 2.25,    0, 'Current hold');

The notebook stored changes as text ("+25bp", "Hold"). Here we store change_bp as a number, with 0 for holds, so we can do arithmetic on it. Choosing good types when you design a table saves effort in every later query.

NULL is SQL's missing value, the equivalent of pandas' NaN.

11.3 SELECT, WHERE, ORDER BY, LIMIT (30 min)

Create day11/select_basics.sql and try each query in turn.

Example 1 — Choosing columns (projection)

SELECT province, balance_pct_gdp FROM provinces;

SELECT province,
       balance_pct_gdp,
       balance_pct_gdp * 100 AS balance_bp          -- a calculated column
FROM provinces;

Example 2 — Choosing rows (filtering)

SELECT * FROM provinces WHERE balance_pct_gdp > 0;

SELECT * FROM provinces
WHERE region = 'Atlantic' AND balance_pct_gdp < 0;

SELECT * FROM provinces
WHERE region IN ('West', 'Prairies');

SELECT * FROM provinces
WHERE balance_pct_gdp BETWEEN -1.0 AND -0.5;        -- inclusive at both ends

Example 3 — Missing values need IS NULL

SELECT * FROM boc_decisions WHERE note IS NULL;
SELECT * FROM boc_decisions WHERE note IS NOT NULL;

-- Wrong! NULL is never "equal" to anything, not even NULL:
SELECT * FROM boc_decisions WHERE note = NULL;      -- returns zero rows

Example 4 — Text patterns

SELECT * FROM boc_decisions WHERE note LIKE '%hike%';     -- % = any characters
SELECT * FROM boc_decisions WHERE note ILIKE '%CUT%';     -- ILIKE ignores case

Example 5 — Sorting and limiting

SELECT * FROM provinces ORDER BY balance_pct_gdp;               -- worst first
SELECT * FROM provinces ORDER BY balance_pct_gdp DESC LIMIT 3;  -- top 3
SELECT * FROM provinces ORDER BY region, balance_pct_gdp;       -- two keys

Example 6 — Unique values

SELECT DISTINCT region FROM provinces ORDER BY region;

Try it 11.3

Write queries to find: (a) all rate cuts (negative change_bp), newest first; (b) decisions in 2023 (hint: decision_date BETWEEN '2023-01-01' AND '2023-12-31'); (c) the three largest moves in absolute size (hint: ABS()).

-- (a)
SELECT * FROM boc_decisions WHERE change_bp < 0 ORDER BY decision_date DESC;

-- (b)
SELECT * FROM boc_decisions
WHERE decision_date BETWEEN '2023-01-01' AND '2023-12-31';

-- (c)
SELECT decision_date, change_bp
FROM boc_decisions
ORDER BY ABS(change_bp) DESC
LIMIT 3;

11.4 CASE WHEN: Classification in SQL (20 min)

CASE WHEN is SQL's if / elif / else. It's the same logic as your Day 3 classify_stance function.

Example 1 — Policy stance

SELECT decision_date,
       rate,
       CASE
           WHEN rate < 2.25  THEN 'Stimulative'
           WHEN rate <= 2.75 THEN 'Neutral'
           ELSE 'Restrictive'
       END AS stance
FROM boc_decisions
ORDER BY decision_date;

As in Python, conditions are checked top to bottom and the first match wins.

Example 2 — Direction of each decision

SELECT decision_date,
       change_bp,
       CASE
           WHEN change_bp > 0 THEN 'Hike'
           WHEN change_bp < 0 THEN 'Cut'
           ELSE 'Hold'
       END AS action
FROM boc_decisions;

Example 3 — Replacing missing values with COALESCE

SELECT decision_date,
       COALESCE(note, '—') AS note        -- first non-NULL value
FROM boc_decisions;

Try it 11.4

Classify provinces as 'Surplus' (> 0), 'Small deficit' (between −0.5 and 0) or 'Large deficit', matching the Day 4 fiscal_status function. Sort from best to worst.

SELECT province,
       balance_pct_gdp,
       CASE
           WHEN balance_pct_gdp > 0     THEN 'Surplus'
           WHEN balance_pct_gdp >= -0.5 THEN 'Small deficit'
           ELSE 'Large deficit'
       END AS status
FROM provinces
ORDER BY balance_pct_gdp DESC;

11.5 Data Types, CAST, TRY_CAST and Dates (18 min)

Common types

Converting types

SELECT CAST('2.25' AS DOUBLE) + 1;          -- 3.25
SELECT '2.25'::DOUBLE + 1;                  -- shorthand, same result

SELECT CAST('Bank holiday' AS DOUBLE);      -- ERROR
SELECT TRY_CAST('Bank holiday' AS DOUBLE);  -- NULL — no error

TRY_CAST is SQL's version of pd.to_numeric(errors="coerce"). We rely on it on Day 13 to clean raw BoC files.

Working with dates

SELECT DATE '2024-06-05' - DATE '2023-07-12' AS days_between;       -- 329
SELECT date_diff('month', DATE '2023-07-12', DATE '2024-06-05');    -- months
SELECT year(DATE '2024-06-05'), month(DATE '2024-06-05'), quarter(DATE '2024-06-05');
SELECT date_trunc('month', DATE '2024-06-05');                      -- 2024-06-01
SELECT date_trunc('quarter', DATE '2024-06-05');                    -- 2024-04-01
SELECT DATE '2024-06-05' + INTERVAL 3 MONTH;
SELECT strftime(DATE '2024-06-05', '%B %Y');                        -- June 2024

date_trunc('month', …) does the same job as the notebook's .dt.to_period("M").dt.to_timestamp(): it moves every date to the start of its month.

SELECT decision_date,
       year(decision_date)                  AS yr,
       date_trunc('quarter', decision_date) AS quarter_start
FROM boc_decisions;

11.6 Aggregation: GROUP BY and HAVING (20 min)

Example 1 — Whole-table summaries

SELECT COUNT(*)                 AS n_decisions,
       MIN(rate)                AS min_rate,
       MAX(rate)                AS peak_rate,
       ROUND(AVG(rate), 2)      AS avg_rate,
       SUM(change_bp)           AS net_change_bp
FROM boc_decisions;

Example 2 — Per-group summaries

SELECT year(decision_date) AS yr,
       COUNT(*)            AS n_decisions,
       SUM(change_bp)      AS net_change_bp,
       MAX(rate)           AS max_rate
FROM boc_decisions
GROUP BY yr
ORDER BY yr;

This is df.groupby(df["date"].dt.year).agg(...) from Day 6.

The golden rule of GROUP BY: every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function (COUNT, SUM, AVG, MIN, MAX...). DuckDB's shortcut GROUP BY ALL groups by every non-aggregated column automatically.

Example 3 — Counting within a group with a condition

SELECT year(decision_date)                   AS yr,
       COUNT(*) FILTER (WHERE change_bp > 0) AS hikes,
       COUNT(*) FILTER (WHERE change_bp < 0) AS cuts,
       COUNT(*) FILTER (WHERE change_bp = 0) AS holds
FROM boc_decisions
GROUP BY ALL
ORDER BY yr;

Example 4 — Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters groups after aggregation.

SELECT region,
       COUNT(*)                        AS n_provinces,
       ROUND(AVG(balance_pct_gdp), 2)  AS avg_balance
FROM provinces
GROUP BY region
HAVING AVG(balance_pct_gdp) < 0
ORDER BY avg_balance;

11.7 Mini-Project: The BoC Decision Log (10 min)

Create day11/decision_log.sql answering:

1. How many hikes, cuts and holds were there in total?
2. What was the total tightening in 2022–2023 and the total easing since 2024?
3. How many days passed between the first 2022 hike and the peak rate?
4. List each year with its first and last policy rate (hint: MIN/MAX on decision_date, or DuckDB's arg_min(rate, decision_date) / arg_max(rate, decision_date)).

-- 1
SELECT CASE WHEN change_bp > 0 THEN 'Hike'
            WHEN change_bp < 0 THEN 'Cut' ELSE 'Hold' END AS action,
       COUNT(*) AS n
FROM boc_decisions
GROUP BY action;

-- 2
SELECT SUM(change_bp) FILTER (WHERE decision_date < '2024-01-01')  AS tightening_bp,
       SUM(change_bp) FILTER (WHERE decision_date >= '2024-01-01') AS easing_bp
FROM boc_decisions;

-- 3
SELECT MAX(decision_date) FILTER (WHERE note = 'Peak rate')
     - MIN(decision_date) AS days_first_hike_to_peak
FROM boc_decisions;

-- 4
SELECT year(decision_date)                 AS yr,
       arg_min(rate, decision_date)        AS first_rate,
       arg_max(rate, decision_date)        AS last_rate
FROM boc_decisions
GROUP BY yr
ORDER BY yr;

Day 11 checklist

• I can open DuckDB in memory or with a database file, and use dot commands
• I can create tables with typed columns and insert rows
• I can filter (WHERE), sort (ORDER BY), limit and classify (CASE WHEN)
• I handle NULL correctly with IS NULL and COALESCE
• I can convert types (CAST, TRY_CAST) and manipulate dates
• I can aggregate with GROUP BY, FILTER and HAVING

git add .
git commit -m "Day 11: DuckDB CLI and SQL basics"
git push