Appendix F: SQL Breakdown Code Examples

This appendix 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:

SELECT
    am.account_id AS acct,
    tx.new_balance AS balance,
    TO_CHAR(am.month_end, 'YYYY-MM') AS month
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
ORDER BY am.account_id, am.month_end;

Reference: See Final SELECT in SQL Breakdown.

Alternative Approaches (Not Used)

Alternative 1: Simple GROUP BY

This approach is incorrect because it doesn't 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
CREATE TABLE gold.monthly_balances AS
SELECT
    am.account_id AS acct,
    tx.new_balance AS balance,
    TO_CHAR(am.month_end, 'YYYY-MM') AS month,
    CURRENT_TIMESTAMP AS as_of_timestamp
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
ORDER BY am.account_id, am.month_end;

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

Base Table Filter​

CTE 1: months​

CTE 2: unique_accounts​

CTE 3: account_months​

CTE 4: monthly_last_tx​

Final SELECT​

Alternative Approaches (Not Used)​

Alternative 1: Simple GROUP BY​

Alternative 2: SUM(amount)​

Gold Layer Storage Example​

See Also​