Building a Budget Tracker: Querying the Chart of Accounts

This is Part 3 of the budget tracker series. In Part 2, we built the schema: account_types and accounts tables with hierarchical expense categories. Now let's query it.

A schema is only as useful as the questions you can answer with it. We'll write queries that explore the chart of accounts from multiple directions: listing, counting, navigating the hierarchy, finding leaf accounts, and validating data integrity before we build transactions on top of it.

Setup

Create the schema from Part 2 (click to expand, then run)

If you're starting fresh or your session has expired, run this to create the tables and seed data from Part 2.

-- Drop tables in correct order (children before parents)
DROP TABLE IF EXISTS accounts;
DROP TABLE IF EXISTS account_types;

-- The five fundamental account types
CREATE TABLE account_types (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  normal_balance TEXT NOT NULL CHECK (normal_balance IN ('debit', 'credit')),
  description TEXT
);

INSERT INTO account_types (id, name, normal_balance, description) VALUES
  (1, 'Asset',     'debit',  'Resources owned — bank accounts, cash, receivables'),
  (2, 'Liability', 'credit', 'Obligations owed — credit cards, loans, mortgages'),
  (3, 'Equity',    'credit', 'Net worth — the difference between assets and liabilities'),
  (4, 'Income',    'credit', 'Revenue streams — salary, freelance, interest'),
  (5, 'Expense',   'debit',  'Spending categories — rent, groceries, utilities');

-- Accounts table with hierarchical structure
CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  account_type_id INTEGER NOT NULL REFERENCES account_types(id),
  parent_account_id INTEGER REFERENCES accounts(id),
  account_code TEXT UNIQUE,
  description TEXT,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE(name, account_type_id)
);

CREATE INDEX idx_accounts_type ON accounts(account_type_id);
CREATE INDEX idx_accounts_parent ON accounts(parent_account_id);

-- Seed data: Assets
INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Checking Account',    1, '1001', 'Primary bank account'),
  ('Savings Account',     1, '1002', 'Emergency fund and savings'),
  ('Cash',                1, '1003', 'Physical cash on hand'),
  ('Investments',         1, '1004', 'Brokerage and retirement accounts');

-- Liabilities
INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Credit Card',         2, '2001', 'Visa credit card'),
  ('Student Loan',        2, '2002', 'Federal student loan'),
  ('Car Loan',            2, '2003', 'Auto financing');

-- Equity
INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Opening Balance',     3, '3001', 'Initial balances when starting the system');

-- Income
INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Salary',              4, '4001', 'Primary employment income'),
  ('Freelance Income',    4, '4002', 'Side project and consulting income'),
  ('Interest Income',     4, '4003', 'Bank and investment interest');

-- Expenses - parent categories first, then children
INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Housing',             5, '5100', 'Housing-related expenses');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Rent',                5, (SELECT id FROM accounts WHERE account_code = '5100'), '5101', 'Monthly rent payment'),
  ('Utilities',           5, (SELECT id FROM accounts WHERE account_code = '5100'), '5102', 'Electric, water, gas, internet'),
  ('Renters Insurance',   5, (SELECT id FROM accounts WHERE account_code = '5100'), '5103', 'Monthly insurance premium');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Food & Dining',       5, '5200', 'Food-related expenses');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Groceries',           5, (SELECT id FROM accounts WHERE account_code = '5200'), '5201', 'Grocery store purchases'),
  ('Restaurants',         5, (SELECT id FROM accounts WHERE account_code = '5200'), '5202', 'Dining out and takeout'),
  ('Coffee',              5, (SELECT id FROM accounts WHERE account_code = '5200'), '5203', 'Coffee shops');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Transportation',      5, '5300', 'Getting around');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Gas',                 5, (SELECT id FROM accounts WHERE account_code = '5300'), '5301', 'Fuel for car'),
  ('Car Insurance',       5, (SELECT id FROM accounts WHERE account_code = '5300'), '5302', 'Auto insurance premium'),
  ('Parking & Tolls',     5, (SELECT id FROM accounts WHERE account_code = '5300'), '5303', 'Parking fees and toll charges');

INSERT INTO accounts (name, account_type_id, account_code, description) VALUES
  ('Personal',            5, '5400', 'Personal spending');
INSERT INTO accounts (name, account_type_id, parent_account_id, account_code, description) VALUES
  ('Entertainment',       5, (SELECT id FROM accounts WHERE account_code = '5400'), '5401', 'Movies, games, streaming'),
  ('Health & Fitness',    5, (SELECT id FROM accounts WHERE account_code = '5400'), '5402', 'Gym, sports, medical co-pays'),
  ('Clothing',            5, (SELECT id FROM accounts WHERE account_code = '5400'), '5403', 'Clothes and accessories');

Query 1: View the Full Chart of Accounts

The most basic question: what's in the chart?

SELECT
  a.account_code,
  a.name AS account_name,
  at.name AS account_type,
  at.normal_balance,
  a.description
FROM accounts a
JOIN account_types at ON a.account_type_id = at.id
WHERE a.is_active = true
ORDER BY a.account_code;

Output:

account_code | account_name      | account_type | normal_balance | description
-------------|-------------------|--------------|----------------|----------------------------
1001         | Checking Account  | Asset        | debit          | Primary bank account
1002         | Savings Account   | Asset        | debit          | Emergency fund and savings
1003         | Cash              | Asset        | debit          | Physical cash on hand
1004         | Investments       | Asset        | debit          | Brokerage and retirement...
2001         | Credit Card       | Liability    | credit         | Visa credit card
2002         | Student Loan      | Liability    | credit         | Federal student loan
2003         | Car Loan          | Liability    | credit         | Auto financing
3001         | Opening Balance   | Equity       | credit         | Initial balances when...
4001         | Salary            | Income       | credit         | Primary employment income
4002         | Freelance Income  | Income       | credit         | Side project and consulting
4003         | Interest Income   | Income       | credit         | Bank and investment interest
5100         | Housing           | Expense      | debit          | Housing-related expenses
5101         | Rent              | Expense      | debit          | Monthly rent payment
5102         | Utilities         | Expense      | debit          | Electric, water, gas...
5103         | Renters Insurance | Expense      | debit          | Monthly insurance premium
5200         | Food & Dining     | Expense      | debit          | Food-related expenses
5201         | Groceries         | Expense      | debit          | Grocery store purchases
5202         | Restaurants       | Expense      | debit          | Dining out and takeout
5203         | Coffee            | Expense      | debit          | Coffee shops
5300         | Transportation    | Expense      | debit          | Getting around
5301         | Gas               | Expense      | debit          | Fuel for car
5302         | Car Insurance     | Expense      | debit          | Auto insurance premium
5303         | Parking & Tolls   | Expense      | debit          | Parking fees and toll...
5400         | Personal          | Expense      | debit          | Personal spending
5401         | Entertainment     | Expense      | debit          | Movies, games, streaming
5402         | Health & Fitness  | Expense      | debit          | Gym, sports, medical...
5403         | Clothing          | Expense      | debit          | Clothes and accessories

The account codes give clean sort order: assets first, then liabilities, equity, income, expenses. The JOIN to account_types pulls in the type name and normal balance without repeating that data in every account row.

Query 2: Count Accounts by Type

How are accounts distributed?

SELECT
  at.name AS account_type,
  COUNT(*) AS account_count,
  COUNT(a.parent_account_id) AS sub_accounts,
  COUNT(*) - COUNT(a.parent_account_id) AS top_level
FROM account_types at
LEFT JOIN accounts a ON at.id = a.account_type_id AND a.is_active = true
GROUP BY at.id, at.name
ORDER BY at.id;

Output:

account_type | account_count | sub_accounts | top_level
-------------|---------------|--------------|----------
Asset        | 4             | 0            | 4
Liability    | 3             | 0            | 3
Equity       | 1             | 0            | 1
Income       | 3             | 0            | 3
Expense      | 16            | 12           | 4

Expenses dominate: 16 of 27 accounts. That's typical. The complexity in personal budgeting is almost entirely in categorizing where money goes.

Notice the LEFT JOIN instead of JOIN. If we used JOIN, an account type with zero accounts would disappear from the results. LEFT JOIN guarantees all five types appear even if one is empty.

The trick with COUNT(a.parent_account_id) is worth knowing: COUNT(column) counts non-null values only. So it counts only accounts that have a parent, i.e., sub-accounts.

Query 3: Show the Expense Hierarchy

A self-join (joining a table to itself via a parent foreign key) reveals the tree structure:

SELECT
  COALESCE(parent.name, '(top level)') AS category,
  child.account_code,
  child.name AS account_name
FROM accounts child
LEFT JOIN accounts parent ON child.parent_account_id = parent.id
WHERE child.account_type_id = 5
ORDER BY child.account_code;

Output:

category       | account_code | account_name
---------------|--------------|------------------
(top level)    | 5100         | Housing
Housing        | 5101         | Rent
Housing        | 5102         | Utilities
Housing        | 5103         | Renters Insurance
(top level)    | 5200         | Food & Dining
Food & Dining  | 5201         | Groceries
Food & Dining  | 5202         | Restaurants
Food & Dining  | 5203         | Coffee
(top level)    | 5300         | Transportation
Transportation | 5301         | Gas
Transportation | 5302         | Car Insurance
Transportation | 5303         | Parking & Tolls
(top level)    | 5400         | Personal
Personal       | 5401         | Entertainment
Personal       | 5402         | Health & Fitness
Personal       | 5403         | Clothing

The LEFT JOIN accounts parent ON child.parent_account_id = parent.id joins the table to itself. Each row in child looks up its parent row. Top-level accounts have parent_account_id = NULL, so the LEFT JOIN produces NULL for the parent columns. COALESCE turns that into "(top level)".

This same pattern works for org charts, file systems, product categories, comment threads. Once you see it, you'll recognize it everywhere.

Query 4: Find Leaf Accounts

In accounting, transactions go to leaf accounts, accounts with no children. Parent accounts exist only for grouping and rollup. You'd never post a transaction to "Food & Dining" directly; you'd post to Groceries, Restaurants, or Coffee.

SELECT
  a.account_code,
  a.name,
  at.name AS account_type
FROM accounts a
JOIN account_types at ON a.account_type_id = at.id
WHERE a.is_active = true
  AND NOT EXISTS (
    SELECT 1 FROM accounts child
    WHERE child.parent_account_id = a.id
  )
ORDER BY a.account_code;

Output:

account_code | name              | account_type
-------------|-------------------|-------------
1001         | Checking Account  | Asset
1002         | Savings Account   | Asset
1003         | Cash              | Asset
1004         | Investments       | Asset
2001         | Credit Card       | Liability
2002         | Student Loan      | Liability
2003         | Car Loan          | Liability
3001         | Opening Balance   | Equity
4001         | Salary            | Income
4002         | Freelance Income  | Income
4003         | Interest Income   | Income
5101         | Rent              | Expense
5102         | Utilities         | Expense
5103         | Renters Insurance | Expense
5201         | Groceries         | Expense
5202         | Restaurants       | Expense
5203         | Coffee            | Expense
5301         | Gas               | Expense
5302         | Car Insurance     | Expense
5303         | Parking & Tolls   | Expense
5401         | Entertainment     | Expense
5402         | Health & Fitness  | Expense
5403         | Clothing          | Expense

23 leaf accounts. Housing (5100), Food & Dining (5200), Transportation (5300), and Personal (5400) are absent because they're parent accounts.

NOT EXISTS is the key technique. For each account, the subquery asks: "does any other account reference this one as its parent?" If no child exists, the account is a leaf. This is more efficient than a LEFT JOIN ... WHERE child.id IS NULL approach on larger datasets, because the database can stop searching as soon as it finds one child.

Query 5: Validate the Schema

Before building transactions on top of this schema, verify the data is clean. These three checks catch the most common integrity problems:

-- Check 1: Any account pointing to a non-existent parent?
SELECT a.name, a.parent_account_id
FROM accounts a
WHERE a.parent_account_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM accounts p WHERE p.id = a.parent_account_id
  );

-- Check 2: Any parent-child type mismatches?
-- A child should have the same account type as its parent
SELECT
  child.name AS child_name,
  ct.name AS child_type,
  parent.name AS parent_name,
  pt.name AS parent_type
FROM accounts child
JOIN accounts parent ON child.parent_account_id = parent.id
JOIN account_types ct ON child.account_type_id = ct.id
JOIN account_types pt ON parent.account_type_id = pt.id
WHERE child.account_type_id != parent.account_type_id;

-- Check 3: Any duplicate account codes?
SELECT account_code, COUNT(*) AS cnt
FROM accounts
WHERE account_code IS NOT NULL
GROUP BY account_code
HAVING COUNT(*) > 1;

All three should return empty results. If they don't, there's a data integrity problem to fix before moving forward.

Check 1 is technically redundant since the foreign key constraint on parent_account_id prevents orphaned references. But foreign keys can be deferred or disabled during bulk loads, so it's worth verifying.

Check 2 catches a subtler problem: an expense account parented under an asset account. The foreign key only validates that the parent exists, not that it's the right type. This is the kind of bug that won't cause errors but will produce wrong reports.

Check 3 uses HAVING, which filters after grouping (unlike WHERE which filters before). HAVING COUNT(*) > 1 keeps only groups with duplicates.

Challenge 1: Add a New Expense Category

Goal: Add a "Subscriptions" category under Personal, with child accounts for "Streaming Services", "Software", and "Gym Membership".

Hints:

Insert the parent first (Subscriptions, code 5410)
Then insert children referencing the parent's id
Follow the existing code pattern (5401-5403 are taken, so use 5411, 5412, 5413)

Pseudocode:

INSERT parent: name='Subscriptions', type=5, parent=(SELECT id WHERE code='5400'), code='5410'
INSERT children: each referencing Subscriptions' id

Challenge 2: Account Summary View

Goal: Write a query showing each account type with total accounts, leaf accounts, and parent accounts.

Expected output:

account_type | total_accounts | leaf_accounts | parent_accounts
-------------|----------------|---------------|----------------
Asset        | 4              | 4             | 0
Liability    | 3              | 3             | 0
Equity       | 1              | 1             | 0
Income       | 3              | 3             | 0
Expense      | 16             | 12            | 4

Hints:

Start from account_types, LEFT JOIN to accounts
Use a CASE expression to check if an account has children
NOT EXISTS (SELECT 1 FROM accounts child WHERE child.parent_account_id = a.id) identifies leaf accounts

Pseudocode:

SELECT
  type_name,
  COUNT(*) as total,
  SUM(CASE WHEN not_exists_child THEN 1 ELSE 0 END) as leaf,
  total - leaf as parent
FROM account_types
LEFT JOIN accounts ...
GROUP BY type

Challenge 3: Account Code Gap Finder

Goal: Find gaps in the account code numbering within each type.

Expected output:

account_type | last_code | next_code | gap_size
-------------|-----------|-----------|--------
Expense      | 5103      | 5200      | 97
Expense      | 5203      | 5300      | 97
Expense      | 5303      | 5400      | 97

Hints:

Use LEAD() window function to peek at the next account code
Cast codes to integer for arithmetic
Filter to gaps larger than 1

Pseudocode:

WITH ordered AS (
  SELECT
    account_code,
    account_type_id,
    LEAD(account_code) OVER (PARTITION BY account_type_id ORDER BY account_code) AS next_code
  FROM accounts
)
SELECT * FROM ordered
WHERE next_code::int - account_code::int > 1

Real-World Applications

These querying patterns show up constantly in production:

Data validation before migrations - run integrity checks like Query 5 before adding new tables that reference existing ones
Self-joins for any hierarchy - org charts, category trees, threaded comments, bill-of-materials
NOT EXISTS for exclusion queries - "find customers who haven't ordered", "find products never reviewed"
LEAD/LAG for gap analysis - sequence gaps, time gaps between events, missing data detection
LEFT JOIN for optional relationships - "show all types even if they have zero accounts"

Performance Tips

For large charts of accounts (1000+ accounts):

Index the parent for hierarchy queries (already done):

CREATE INDEX idx_accounts_parent ON accounts(parent_account_id);

Index account_code for lookups:

CREATE INDEX idx_accounts_code ON accounts(account_code);

Partial index for active accounts:

CREATE INDEX idx_accounts_active ON accounts(account_type_id)
  WHERE is_active = true;

Most queries filter on is_active = true. A partial index skips inactive rows entirely.

Materialized path for deep hierarchies:
If your hierarchy goes more than 3-4 levels deep, store the full path as an array for faster rollup queries.

What's Next

The chart of accounts is set up and verified. In Part 4, we'll build the transaction system: journal entries and line items using double-entry bookkeeping. Every transaction will have debits and credits that must balance, enforced by SQL constraints. We'll record paychecks, rent payments, credit card purchases, and loan payments.