Skip to main content

SQL Breakdown Code Examples

© 2026 Stephen Adei. All rights reserved. All content on this site is the intellectual property of Stephen Adei. See License for terms of use and attribution.

This section contains all SQL code snippets referenced in the SQL Breakdown document. These examples illustrate the query structure from inner to outer CTEs.

Base Table Filter​

The base table filter is the innermost data source:

FROM transactions
WHERE tx_date >= '2024-01-01' AND tx_date < '2024-04-01'

Reference: See Base Table: transactions in SQL Breakdown.

CTE 1: months​

Creates a hardcoded list of month-end dates for Q1 2024:

WITH months AS (
    SELECT CAST(date_column AS DATE) AS month_end
    FROM (
        VALUES
        ('2024-01-31'),
        ('2024-02-29'),
        ('2024-03-31')
    ) AS t (date_column)
)

Reference: See CTE 1: months in SQL Breakdown.

CTE 2: unique_accounts​

Extracts all unique account IDs from the transactions table:

unique_accounts AS (
    SELECT DISTINCT account_id FROM transactions
    -- WHERE tx_date >= '2024-01-01' -- Optimization: filter partition scan if possible
)

Reference: See CTE 2: unique_accounts in SQL Breakdown.

CTE 3: account_months​

Creates a complete spine of all account×month combinations using CROSS JOIN:

account_months AS (
    SELECT
        a.account_id,
        m.month_end
    FROM unique_accounts AS a
    CROSS JOIN months AS m
)

Reference: See CTE 3: account_months in SQL Breakdown.

CTE 4: monthly_last_tx​

Identifies the last transaction for each account in each month using a window function:

monthly_last_tx AS (
    SELECT
        account_id,
        new_balance,
        DATE_TRUNC('month', tx_date) AS tx_month_start,
        ROW_NUMBER() OVER (
            PARTITION BY account_id, DATE_TRUNC('month', tx_date)
            ORDER BY tx_date DESC, id DESC
        ) AS rn
    FROM transactions
    WHERE tx_date >= '2024-01-01' AND tx_date < '2024-04-01'
)

Reference: See CTE 4: monthly_last_tx in SQL Breakdown.

Final SELECT​

The outermost SELECT statement that joins the spine with the last transactions:

-- Join spine with data and fill gaps (Carry-Forward)
WITH raw_balances AS (
    SELECT
        am.account_id,
        am.month_end,
        tx.new_balance
    FROM account_months AS am
    LEFT JOIN monthly_last_tx AS tx
        ON am.account_id = tx.account_id
        AND DATE_TRUNC('month', am.month_end) = tx.tx_month_start
        AND tx.rn = 1
)
SELECT
    account_id AS acct,
    LAST_VALUE(new_balance IGNORE NULLS) OVER (
        PARTITION BY account_id
        ORDER BY month_end
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS balance,
    TO_CHAR(month_end, 'YYYY-MM') AS month
FROM raw_balances
ORDER BY account_id, month_end;

Reference: See Final SELECT in SQL Breakdown.

Alternative Approaches (Not Used)​

Alternative 1: Simple GROUP BY​

This approach is incorrect because it does not show months with no transactions:

SELECT account_id, MAX(tx_date), new_balance
FROM transactions
GROUP BY account_id, MONTH(tx_date)

Reference: See Alternative 1: Simple GROUP BY (Wrong) in SQL Breakdown.

Alternative 2: SUM(amount)​

This approach is incorrect per specification because it uses SUM instead of the last transaction's new_balance:

SELECT account_id, SUM(amount) as balance
FROM transactions
GROUP BY account_id, MONTH(tx_date)

Reference: See Alternative 2: SUM(amount) (Wrong per spec) in SQL Breakdown.

Gold Layer Storage Example​

Example of how to store query results in the Gold layer:

-- Example: Store query results in Gold layer with Carry-Forward
CREATE TABLE gold.monthly_balances AS
WITH raw_balances AS (
    SELECT
        am.account_id,
        am.month_end,
        tx.new_balance
    FROM account_months AS am
    LEFT JOIN monthly_last_tx AS tx
        ON am.account_id = tx.account_id
        AND DATE_TRUNC('month', am.month_end) = tx.tx_month_start
        AND tx.rn = 1
)
SELECT
    account_id AS acct,
    LAST_VALUE(new_balance) IGNORE NULLS OVER (
        PARTITION BY account_id
        ORDER BY month_end
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS balance,
    TO_CHAR(month_end, 'YYYY-MM') AS month,
    CURRENT_TIMESTAMP AS as_of_timestamp
FROM raw_balances
ORDER BY account_id, month_end;

Reference: See Storing Results in Gold Layer in SQL Breakdown.

See also​

© 2026 Stephen Adei•CC BY 4.0