Build a Personal Finance Tracker

Design a PostgreSQL database with Views and window functions to track finances.

Introduction

30 Second Summary

Every month, money flows in and out of your accounts. Paychecks arrive, rent goes out, groceries add up, and subscriptions quietly drain your balance. Without a system to track it all, answering "where did my money go?" feels like guesswork.

In this project, you will build a personal finance tracker database using PostgreSQL. You will design tables that enforce data integrity, populate them with realistic transactions, and create reporting queries that answer questions like "how much did I spend on food this month?"

What You'll Build

A personal finance database with 3 related tables, managed through pgAdmin 4, that uses Views and window functions to generate spending reports automatically.

By the end of this project, you'll have:

  • A running PostgreSQL server on your machine with a fully designed finance database that enforces data integrity through CHECK constraints and foreign keys.
  • A collection of Views that automatically calculate monthly summaries and running balances using date functions and window functions.
  • Hands-on experience with production-grade SQL features (NUMERIC for money, date filtering, month-over-month comparisons) that transfer directly to real-world jobs.
  • Secret Mission: Add a budget tracking system that compares actual spending against monthly limits per category.

Are there any prerequisites?

This project assumes you know basic SQL: CREATE TABLE, INSERT, SELECT, WHERE, and JOIN. If you have completed "Build Your First Database with SQLite" or have equivalent experience, you are ready to go.

Before We Start

Before We Start...

Install PostgreSQL and Set Up pgAdmin

To build a personal finance tracker, you need a database server that can enforce data integrity rules and calculate reports automatically.

In this step, you will install PostgreSQL 17 on your Windows machine using the official EDB installer. This single installer gives you the database server, a graphical management tool called pgAdmin 4, and the command-line tools you will use throughout this project.

In this step, get ready to:
  • Download and install PostgreSQL 17 with all components.
  • Add PostgreSQL to your system PATH so you can run commands from anywhere.
  • Launch pgAdmin 4 and connect to your PostgreSQL server.
Download and run the PostgreSQL installer

The EDB interactive installer bundles everything you need in one download: the PostgreSQL server, pgAdmin 4, Stack Builder, and Command Line Tools.

  • Open your browser and go to the PostgreSQL Windows downloads page.
  • Click the Download the installer link to go to the EDB downloads page.
  • Select version 17 for Windows x86-64 and click the download button.
  • Once the download completes, double-click the installer file to launch it.

Why does it ask for admin privileges?

The installer needs administrator privileges to set up the PostgreSQL Windows service and write to the Program Files directory. Click Yes on the User Account Control prompt to continue.

  • Click Next on the welcome screen.
  • Leave the installation directory as the default (C:\Program Files\PostgreSQL\17) and click Next.
  • On the Select Components screen, make sure all four checkboxes are selected: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
  • Click Next.
  • Leave the data directory as the default and click Next.
  • On the password screen, enter a password for the postgres superuser account.
  • Remember this password. You will need it to connect to your database later.
  • Leave the port as 5432 (the default) and click Next.
  • Leave the locale as the default and click Next.
  • Review the summary and click Next to begin the installation.
  • Wait for the installation to complete. When finished, uncheck Launch Stack Builder and click Finish.

Skip Stack Builder for now

Stack Builder downloads additional tools and drivers. You do not need any of them for this project.

Installer stuck or showing an error?

Make sure no other PostgreSQL installation is already running on port 5432. If you have a previous version installed, uninstall it first or choose a different port during setup.

Also check that your antivirus is not blocking the installer. Temporarily disabling real-time protection can help if the install hangs.

help me troubleshoot this PostgreSQL installation error on Windows

Add PostgreSQL to your system PATH

The EDB installer does not automatically add PostgreSQL to your system PATH. Without this step, Windows will not recognize psql commands in the terminal.

  • Press the Windows key to open the Start menu.
  • Type Environment Variables and click Edit the system environment variables.
  • In the System Properties window, click Environment Variables.
  • In the System variables section (the bottom half), find the variable named Path and select it.
  • Click Edit.
  • Click New and paste the following path:
C:\Program Files\PostgreSQL\17\bin
  • Click OK to close the Edit window.
  • Click OK again to close Environment Variables.
  • Click OK to close System Properties.

Why a new Command Prompt?

Any Command Prompt windows that were already open will not pick up the new PATH variable. You must open a fresh one for the change to take effect.

  • Open a new Command Prompt by pressing the Windows key, typing cmd, and pressing Enter.
  • Verify that PostgreSQL is on your PATH by running this command:
psql --version

You should see output that looks like psql (PostgreSQL) 17.10 (the exact patch number may differ).

✔️ I see version 17 or higher

PostgreSQL is installed and your PATH is configured correctly. You are ready to move on.

ⓧ I see an older version

You have a previous PostgreSQL installation that is shadowing the new one in your PATH.

  • Go back into Environment Variables and edit the Path variable.
  • Find the entry pointing to the older PostgreSQL version (for example C:\Program Files\PostgreSQL\16\bin) and delete it.
  • Make sure C:\Program Files\PostgreSQL\17\bin is still in the list.
  • Click OK to save, open a new Command Prompt, and run psql --version again.

ⓧ 'psql' is not recognized

Windows cannot find the psql program. The PATH entry is missing or incorrect.

  • Open File Explorer and navigate to C:\Program Files\PostgreSQL\17\bin. Confirm that psql.exe exists in this folder.
  • If the folder does not exist, check your installation directory. The installer may have used a different drive letter.
  • Go back to Environment Variables and double-check that C:\Program Files\PostgreSQL\17\bin is listed under System variables > Path (not User variables).
  • Make sure you opened a new Command Prompt after saving the PATH change.

help me fix psql not recognized on Windows after installing PostgreSQL 17

Launch pgAdmin 4 and connect to your server

pgAdmin 4 is a graphical tool for managing your PostgreSQL databases. It runs in your browser and gives you a visual way to create databases, run queries, and inspect tables.

  • Press the Windows key to open the Start menu.
  • Type pgAdmin and press Enter to open it.
  • pgAdmin opens in your default web browser. It will ask you to set a master password. Enter a password and click OK.

What is the master password?

The master password is for pgAdmin itself. It protects your saved server connections. This is separate from the postgres database password you set during installation.

  • In the left panel, expand Servers.
  • Click PostgreSQL 17.
  • When prompted, enter the postgres password you chose during installation and click OK.

You should see the server icon turn green, indicating a successful connection. The left panel now shows your server's databases, roles, and tablespaces.

Connection refused or wrong password?

If pgAdmin shows a connection refused error, the PostgreSQL service may not be running. Press the Windows key, type Services, and find postgresql-x64-17. Right-click it and select Start.

If you are getting a wrong password error, try the password you set during installation (not the pgAdmin master password). These are two different passwords.

help me connect to my PostgreSQL 17 server in pgAdmin

Your database server is up and running. Next up, you will create a dedicated finance database and design tables with production-grade data types and constraints.

Create Your Database and Design the Schema

Your PostgreSQL server is running and pgAdmin 4 is connected. Now it's time to design the foundation of your finance tracker: a database schema that enforces data integrity from the start.

You will create three tables that work together to track your money. Unlike SQLite, PostgreSQL enforces CHECK constraints natively and supports NUMERIC for exact monetary calculations. This means your database actively rejects bad data instead of silently accepting it.

In this step, get ready to:
  • Create a dedicated finance_tracker database in PostgreSQL.
  • Design three tables with production-grade data types and constraints.
  • Verify the schema in pgAdmin's object browser.
Create the database and your first two tables

Every project deserves its own database. Keeping your finance data separate from the default postgres database keeps things organized and makes cleanup easy later.

  • In pgAdmin, expand **Servers** in the left panel, then expand **PostgreSQL 17**.
  • Right-click Databases and select Create > Database.
  • Enter finance_tracker in the Database name field.
  • Leave the owner as postgres and click Save.
  • Right-click your new finance_tracker database in the left panel and select Query Tool.

The Query Tool opens a SQL editor connected directly to your finance_tracker database. Any SQL you run here applies only to this database.

  • Create the accounts and categories tables by pasting this SQL into the Query Tool and clicking the Execute/Refresh button (the play icon in the toolbar):
-- Create the accounts table
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    account_type TEXT NOT NULL CHECK (account_type IN ('checking', 'savings', 'credit_card', 'cash')),
    balance NUMERIC(12, 2) NOT NULL DEFAULT 0.00
);

-- Create the categories table
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    category_type TEXT NOT NULL CHECK (category_type IN ('income', 'expense'))
);

What does this code do?

You are creating two reference tables that the transactions table will point to later.

  • SERIAL PRIMARY KEY auto-generates a unique ID for each new row.
  • NUMERIC(12, 2) stores monetary values with exact precision (up to 12 digits, 2 decimal places). PostgreSQL recommends this type for money because floating-point math can introduce rounding errors.
  • CHECK (account_type IN (...)) restricts the column to a fixed set of allowed values. PostgreSQL rejects any INSERT that does not match.
  • NOT NULL UNIQUE on the name columns means every account and category must have a name, and no two can share the same name.
  • Check the Messages tab in the lower panel of the Query Tool. You should see execution details confirming the queries completed successfully.

Seeing an error in the Messages tab?

  • If the error message mentions a syntax issue, pgAdmin underlines the problem SQL in the editor. Check for missing commas or parentheses.
  • Make sure you opened the Query Tool from the finance_tracker database, not from the default postgres database. The tab title at the top shows which database you are connected to.
  • If you see "relation already exists", it means you ran the SQL twice. That is fine. The tables already exist and you can move on.

Why is my CREATE TABLE failing in pgAdmin?

Create the transactions table

The transactions table is the core of your system. Each transaction links to an account and a category using foreign keys. This ensures you cannot record a transaction for an account or category that does not exist.

  • Clear the Query Tool editor (select all text and delete it), then paste and execute this SQL:
-- Create the transactions table
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL DEFAULT CURRENT_DATE,
    description TEXT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    transaction_type TEXT NOT NULL CHECK (transaction_type IN ('income', 'expense')),
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    category_id INTEGER NOT NULL REFERENCES categories(category_id)
);

What does this code do?

  • DATE NOT NULL DEFAULT CURRENT_DATE stores an exact date (no time). If you skip the date on an INSERT, PostgreSQL fills in today's date automatically.
  • CHECK (amount > 0) guarantees every transaction has a positive amount. The transaction_type column records whether it is income or expense.
  • REFERENCES accounts(account_id) creates a foreign key. PostgreSQL will reject any transaction that points to an account_id that does not exist in the accounts table.
  • Check the Messages tab again. You should see execution details confirming the transactions table was created successfully.

Table creation failed?

  • If you see an error about accounts not existing, it means the foreign key cannot find the referenced table. Go back and run the accounts/categories SQL from the previous chunk first.
  • If you accidentally pasted both code blocks together and see a duplicate table error, that is fine. The transactions table is the only new one. As long as the Messages tab does not show an error specifically for the transactions CREATE TABLE, you are good.

Help me fix my transactions table creation error.

✔️ Awesome, I've got everything!

Great. Make sure your Query Tool shows successful execution for all three tables before moving on.

ⓧ I'd like to double check the full code

Here is the complete schema SQL. All three CREATE TABLE statements should have run successfully in your Query Tool.

-- Create the accounts table
CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    account_type TEXT NOT NULL CHECK (account_type IN ('checking', 'savings', 'credit_card', 'cash')),
    balance NUMERIC(12, 2) NOT NULL DEFAULT 0.00
);

-- Create the categories table
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    category_type TEXT NOT NULL CHECK (category_type IN ('income', 'expense'))
);

-- Create the transactions table
CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL DEFAULT CURRENT_DATE,
    description TEXT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    transaction_type TEXT NOT NULL CHECK (transaction_type IN ('income', 'expense')),
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    category_id INTEGER NOT NULL REFERENCES categories(category_id)
);
Verify the schema in pgAdmin

pgAdmin's object browser gives you a visual way to confirm your tables, columns, and constraints were created correctly.

  • In the left panel, expand finance_tracker > Schemas > public > Tables.
  • You should see all three tables listed: accounts, categories, and transactions.
  • Click on the transactions table, then expand Columns underneath it to inspect the column names and data types.
  • Expand Constraints to see the CHECK constraints and foreign keys listed.

What should I see?

Under the transactions table, the Columns node shows each column with its data type (integer, numeric, date, text). The Constraints node lists your CHECK constraints and foreign keys as separate entries.

Tables not showing up?

If the Tables node appears empty, right-click on Tables and select Refresh. pgAdmin sometimes needs a manual refresh to show newly created objects.

Why CHECK constraints matter

In SQLite, CHECK constraints exist in the syntax but are not always enforced. PostgreSQL enforces them on every INSERT and UPDATE. This means your database actively protects its own integrity without relying on application code to validate data first.

Your schema is locked in. Three tables with proper data types, constraints, and foreign key relationships. Next up, you will fill these tables with 3 months of realistic financial data so you have something meaningful to query.

Populate Your Database with Realistic Data

A database schema is only useful when it contains data. Right now your three tables are empty shells waiting to be filled with realistic financial records.

In this step, you will insert sample data covering 3 months of personal finances: multiple bank accounts, varied spending categories, and 30 transactions spanning April through June 2026. You will also prove that your CHECK constraints actively reject bad data.

In this step, get ready to:
  • Insert accounts and spending categories into your database.
  • Add 30 realistic transactions spanning 3 months.
  • Confirm that CHECK constraints reject invalid data.
Insert accounts and categories

Before you can record transactions, the database needs to know which accounts exist and what spending categories are available. These reference tables are the foundation that your transactions will link to via foreign keys.

  • In the Query Tool (connected to finance_tracker), insert your accounts and categories by running this SQL:
-- Insert accounts
INSERT INTO accounts (name, account_type, balance) VALUES
    ('Main Checking', 'checking', 2500.00),
    ('Savings', 'savings', 8000.00),
    ('Visa Credit Card', 'credit_card', 0.00),
    ('Cash', 'cash', 150.00);

-- Insert categories
INSERT INTO categories (name, category_type) VALUES
    ('Salary', 'income'),
    ('Freelance', 'income'),
    ('Food & Groceries', 'expense'),
    ('Transport', 'expense'),
    ('Entertainment', 'expense'),
    ('Utilities', 'expense'),
    ('Rent', 'expense'),
    ('Health', 'expense');

What does this code do?

The first INSERT creates 4 financial accounts: a checking account, a savings account, a credit card, and a cash wallet. Each has a starting balance.

The second INSERT creates 8 spending categories split between income (Salary, Freelance) and expenses (Food, Transport, Entertainment, Utilities, Rent, Health). The CHECK constraint on category_type ensures every category is either 'income' or 'expense'.

  • Click the Execute/Run button (play icon) in the Query Tool toolbar.
  • You should see messages in the Messages tab at the bottom confirming that 4 rows were inserted into accounts and 8 rows into categories.

Seeing a duplicate key error?

If you ran this INSERT more than once, the UNIQUE constraint on name rejects duplicate entries. Clear the existing data first by running DELETE FROM categories; DELETE FROM accounts; then re-run the INSERT statements.

Help me fix a duplicate key error when inserting accounts

Insert 3 months of transactions

Now you will add the actual transaction records. These reference the accounts and categories you just created through their IDs. Each transaction has a date, description, amount, type, and links to both an account and a category.

  • Insert April 2026 transactions by running this SQL in the Query Tool:
-- April 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-04-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-04-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-04-05', 'Grocery store', 85.50, 'expense', 1, 3),
    ('2026-04-08', 'Bus pass', 45.00, 'expense', 1, 4),
    ('2026-04-10', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-04-12', 'Grocery store', 62.30, 'expense', 1, 3),
    ('2026-04-15', 'Freelance project', 500.00, 'income', 1, 2),
    ('2026-04-18', 'Electric bill', 75.00, 'expense', 1, 6),
    ('2026-04-20', 'Restaurant dinner', 48.00, 'expense', 3, 5),
    ('2026-04-25', 'Pharmacy', 32.50, 'expense', 4, 8);

What do these IDs mean?

The account_id and category_id numbers are foreign keys that point back to rows in the accounts and categories tables. For example, account_id = 3 means 'Visa Credit Card' (the third account you inserted) and category_id = 5 means 'Entertainment' (the fifth category).

  • Now insert May 2026 transactions by running:
-- May 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-05-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-05-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-05-04', 'Grocery store', 92.00, 'expense', 1, 3),
    ('2026-05-07', 'Uber rides', 38.00, 'expense', 3, 4),
    ('2026-05-10', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-05-13', 'Grocery store', 71.80, 'expense', 1, 3),
    ('2026-05-16', 'Freelance project', 750.00, 'income', 1, 2),
    ('2026-05-18', 'Water bill', 40.00, 'expense', 1, 6),
    ('2026-05-22', 'Concert tickets', 120.00, 'expense', 3, 5),
    ('2026-05-28', 'Gym membership', 55.00, 'expense', 1, 8);
  • Finally, insert June 2026 transactions by running:
-- June 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-06-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-06-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-06-03', 'Grocery store', 105.20, 'expense', 1, 3),
    ('2026-06-06', 'Bus pass', 45.00, 'expense', 1, 4),
    ('2026-06-09', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-06-11', 'Grocery store', 58.90, 'expense', 1, 3),
    ('2026-06-14', 'Freelance project', 600.00, 'income', 1, 2),
    ('2026-06-17', 'Internet bill', 65.00, 'expense', 1, 6),
    ('2026-06-21', 'Movie night', 35.00, 'expense', 4, 5),
    ('2026-06-26', 'Doctor visit', 80.00, 'expense', 3, 8);
  • Verify your data by running a quick count query:
SELECT COUNT(*) FROM transactions;
  • You should see 30 in the result. This confirms all three months of transactions were inserted successfully.

Seeing a foreign key violation?

If you get an error mentioning a foreign key constraint, it means the accounts and categories data from the previous substep was not inserted. Go back and run those INSERT statements first. Transactions reference accounts and categories by ID, so those rows must exist before any transactions can be added.

Help me fix a foreign key violation when inserting transactions

Test that CHECK constraints reject invalid data

Your schema includes a CHECK constraint that ensures every transaction amount is greater than zero. Let's prove PostgreSQL is actively enforcing this rule by deliberately inserting a bad record.

  • Try inserting a transaction with a negative amount by running this SQL:
-- This should FAIL (amount must be > 0)
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id)
VALUES ('2026-06-01', 'Bad transaction', -50.00, 'expense', 1, 3);

What should I see?

PostgreSQL rejects the INSERT with a check constraint violation error. This proves your CHECK (amount > 0) constraint is working. The database will never allow a zero or negative transaction amount, protecting your data integrity without any application code.

Why is this different from SQLite?

In SQLite, CHECK constraints are not always enforced and foreign keys must be manually enabled with a PRAGMA command. PostgreSQL enforces both by default with no extra configuration needed. This means your data integrity rules are always active from the moment you create the table.

✔️ Awesome, I've got everything!

Great. Double check you have 4 accounts, 8 categories, and 30 transactions by running these quick queries:

SELECT COUNT(*) FROM accounts;
SELECT COUNT(*) FROM categories;
SELECT COUNT(*) FROM transactions;

ⓧ I'd like to double check the full code

Here is the complete SQL for all the data you inserted in this step:

-- Insert accounts
INSERT INTO accounts (name, account_type, balance) VALUES
    ('Main Checking', 'checking', 2500.00),
    ('Savings', 'savings', 8000.00),
    ('Visa Credit Card', 'credit_card', 0.00),
    ('Cash', 'cash', 150.00);

-- Insert categories
INSERT INTO categories (name, category_type) VALUES
    ('Salary', 'income'),
    ('Freelance', 'income'),
    ('Food & Groceries', 'expense'),
    ('Transport', 'expense'),
    ('Entertainment', 'expense'),
    ('Utilities', 'expense'),
    ('Rent', 'expense'),
    ('Health', 'expense');

-- April 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-04-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-04-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-04-05', 'Grocery store', 85.50, 'expense', 1, 3),
    ('2026-04-08', 'Bus pass', 45.00, 'expense', 1, 4),
    ('2026-04-10', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-04-12', 'Grocery store', 62.30, 'expense', 1, 3),
    ('2026-04-15', 'Freelance project', 500.00, 'income', 1, 2),
    ('2026-04-18', 'Electric bill', 75.00, 'expense', 1, 6),
    ('2026-04-20', 'Restaurant dinner', 48.00, 'expense', 3, 5),
    ('2026-04-25', 'Pharmacy', 32.50, 'expense', 4, 8);

-- May 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-05-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-05-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-05-04', 'Grocery store', 92.00, 'expense', 1, 3),
    ('2026-05-07', 'Uber rides', 38.00, 'expense', 3, 4),
    ('2026-05-10', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-05-13', 'Grocery store', 71.80, 'expense', 1, 3),
    ('2026-05-16', 'Freelance project', 750.00, 'income', 1, 2),
    ('2026-05-18', 'Water bill', 40.00, 'expense', 1, 6),
    ('2026-05-22', 'Concert tickets', 120.00, 'expense', 3, 5),
    ('2026-05-28', 'Gym membership', 55.00, 'expense', 1, 8);

-- June 2026 transactions
INSERT INTO transactions (transaction_date, description, amount, transaction_type, account_id, category_id) VALUES
    ('2026-06-01', 'Monthly salary', 3500.00, 'income', 1, 1),
    ('2026-06-02', 'Rent payment', 1200.00, 'expense', 1, 7),
    ('2026-06-03', 'Grocery store', 105.20, 'expense', 1, 3),
    ('2026-06-06', 'Bus pass', 45.00, 'expense', 1, 4),
    ('2026-06-09', 'Streaming subscription', 15.99, 'expense', 3, 5),
    ('2026-06-11', 'Grocery store', 58.90, 'expense', 1, 3),
    ('2026-06-14', 'Freelance project', 600.00, 'income', 1, 2),
    ('2026-06-17', 'Internet bill', 65.00, 'expense', 1, 6),
    ('2026-06-21', 'Movie night', 35.00, 'expense', 4, 5),
    ('2026-06-26', 'Doctor visit', 80.00, 'expense', 3, 8);

Your database now has 3 months of realistic financial data protected by constraints that reject bad input. Next up, you will build Views that automatically turn this raw data into monthly spending reports.

Build Reporting Views with Date Functions

Your database now holds 30 transactions across 3 months. The raw data is in place, but answering questions like "how much did I spend on food in May?" requires writing a full query every single time.

In this step, you will create Views that act like saved queries. Once a View exists, you can query it like a table and always get up-to-date results without rewriting the logic.

In this step, get ready to:
  • Create a monthly spending View that breaks down expenses by category.
  • Create a monthly balance View that compares income against expenses.
  • Query your Views with filters to answer specific financial questions.
Create the monthly spending View

A View is a named query stored in the database. It does not duplicate data. Every time you SELECT from a View, PostgreSQL runs the underlying query and returns fresh results.

  • Create the monthly_spending View by running this query in the Query Tool:
-- Monthly spending breakdown by category
CREATE VIEW monthly_spending AS
SELECT
    DATE_TRUNC('month', t.transaction_date)::DATE AS month,
    c.name AS category,
    c.category_type,
    COUNT(*) AS transaction_count,
    SUM(t.amount) AS total_amount
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
GROUP BY DATE_TRUNC('month', t.transaction_date), c.name, c.category_type
ORDER BY month, c.category_type, total_amount DESC;

What does this View do?

  • DATE_TRUNC('month', t.transaction_date)::DATE strips each date down to the first of its month (e.g. 2026-04-15 becomes 2026-04-01). The ::DATE cast converts the timestamp result into a clean date.
  • JOIN categories c ON t.category_id = c.category_id connects each transaction to its category name and type.
  • GROUP BY groups transactions by month and category, so COUNT(*) and SUM(t.amount) calculate totals for each combination.
  • Query the View to see your monthly spending report by running:
SELECT * FROM monthly_spending;

You should see rows showing each category's transaction count and total amount, grouped by month. The results are ordered by month, then by category type, then by highest amount first.

Seeing an error about the View already existing?

If you ran the CREATE VIEW statement more than once, PostgreSQL rejects duplicates. Run DROP VIEW monthly_spending; first, then re-run the CREATE VIEW statement.

If you see a column or table not found error, make sure your Query Tool is connected to finance_tracker (check the database name in the Query Tool tab title).

Still stuck? Help me troubleshoot my monthly_spending View in pgAdmin.

Create the monthly balance View

The first View shows spending by category. Now you need a View that answers a bigger question: are you saving money or losing it each month?

  • Create the monthly_balance View by running this query in the Query Tool:
-- Monthly income vs expenses summary
CREATE VIEW monthly_balance AS
SELECT
    DATE_TRUNC('month', transaction_date)::DATE AS month,
    SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) AS total_income,
    SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) AS total_expenses,
    SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) -
    SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) AS net_savings
FROM transactions
GROUP BY DATE_TRUNC('month', transaction_date)
ORDER BY month;

What does this View do?

  • CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END acts like an if-statement inside SQL. It only includes the amount when the transaction is income, otherwise contributes zero.
  • The same pattern is used for expenses, giving you separate total_income and total_expenses columns from a single pass through the data.
  • net_savings subtracts total expenses from total income. A positive number means you saved money that month.
  • Query the View to see your monthly balance report by running:
SELECT * FROM monthly_balance;

You should see three rows (one per month) with columns for total income, total expenses, and net savings. Each month should show positive net savings.

Getting zero values in your results?

Double-check that the CASE expressions use the exact strings 'income' and 'expense'. These must match the values in your transaction_type column exactly (case-sensitive).

Need help? Help me debug my monthly_balance View returning zeros.

Query your Views with filters

Views behave like tables. You can filter them with WHERE, sort with ORDER BY, and aggregate with GROUP BY. This makes answering specific questions quick and simple.

  • Run these three queries to answer specific financial questions:
-- What did I spend on Food & Groceries each month?
SELECT month, total_amount
FROM monthly_spending
WHERE category = 'Food & Groceries';

-- Which month did I save the most?
SELECT month, net_savings
FROM monthly_balance
ORDER BY net_savings DESC
LIMIT 1;

-- What are my expense categories ranked by total spending?
SELECT category, SUM(total_amount) AS grand_total
FROM monthly_spending
WHERE category_type = 'expense'
GROUP BY category
ORDER BY grand_total DESC;

What do these queries show?

  • The first query filters monthly_spending to a single category. You can swap 'Food & Groceries' for any category name to see different spending.
  • The second query sorts monthly_balance by net savings descending and takes only the top row with LIMIT 1.
  • The third query aggregates across all months to rank your expense categories by total spending. It uses SUM(total_amount) on top of the View's own totals.

The first query should show Food & Groceries totals for April, May, and June. The second query should return a single row with the month you saved the most. The third should list all expense categories ranked by total spending across all three months.

Getting empty results from a filtered query?

Category names are case-sensitive. Make sure your WHERE clause uses the exact category name from your data (e.g. 'Food & Groceries' not 'food & groceries').

Run SELECT DISTINCT category FROM monthly_spending; to see the exact category names available.

Need help? Help me figure out why my View query returns no rows.

✔️ Awesome, I've got everything!

Great. Both Views are created and queryable. You can run SELECT * FROM monthly_spending; or SELECT * FROM monthly_balance; anytime to get fresh results.

ⓧ I'd like to double check the full code

Here is the complete 03_create_views.sql with both Views:

-- Monthly spending breakdown by category
CREATE VIEW monthly_spending AS
SELECT
    DATE_TRUNC('month', t.transaction_date)::DATE AS month,
    c.name AS category,
    c.category_type,
    COUNT(*) AS transaction_count,
    SUM(t.amount) AS total_amount
FROM transactions t
JOIN categories c ON t.category_id = c.category_id
GROUP BY DATE_TRUNC('month', t.transaction_date), c.name, c.category_type
ORDER BY month, c.category_type, total_amount DESC;

-- Monthly income vs expenses summary
CREATE VIEW monthly_balance AS
SELECT
    DATE_TRUNC('month', transaction_date)::DATE AS month,
    SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) AS total_income,
    SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) AS total_expenses,
    SUM(CASE WHEN transaction_type = 'income' THEN amount ELSE 0 END) -
    SUM(CASE WHEN transaction_type = 'expense' THEN amount ELSE 0 END) AS net_savings
FROM transactions
GROUP BY DATE_TRUNC('month', transaction_date)
ORDER BY month;

Why Views instead of saving queries in a file?

A saved SQL file requires you to run it manually every time you want results. A View lives inside the database itself. Any user or application connected to the database can query it without knowing the underlying logic.

If you add new transactions next month, your Views automatically include them. No file to re-run.

Your Views are now your personal reporting dashboard. Next up, you will use window functions to calculate running balances and compare spending trends across months.

Analyze Trends with Window Functions

Your Views are giving you monthly summaries, but they collapse individual transactions into grouped totals.

Window functions let you run calculations across rows without collapsing them. They are a key differentiator between beginner and intermediate SQL skills, and they show up frequently in job interviews and production reporting queries.

In this step, get ready to:
  • Calculate a running balance that grows and shrinks with each transaction.
  • Rank your biggest expenses within each month.
  • Compare month-over-month spending to spot trends.
Calculate a running balance with SUM OVER

A running balance shows you the cumulative effect of every transaction over time. Regular SUM with GROUP BY collapses rows into totals. Adding an OVER clause turns SUM into a window function that calculates across rows while keeping every row visible.

  • In the Query Tool (connected to finance_tracker), clear any previous SQL and run this query:
-- Running balance over time
SELECT
    transaction_date,
    description,
    transaction_type,
    amount,
    SUM(
        CASE WHEN transaction_type = 'income' THEN amount
             ELSE -amount
        END
    ) OVER (ORDER BY transaction_date, transaction_id) AS running_balance
FROM transactions
ORDER BY transaction_date, transaction_id;

What does this query do?

The CASE expression converts each transaction into a positive (income) or negative (expense) value.

The SUM(...) OVER (ORDER BY transaction_date, transaction_id) clause tells PostgreSQL to add up those values row by row in date order. Each row shows the cumulative total up to that point, giving you a running balance that grows with income and shrinks with expenses.

You should see 30 rows in the Data Output panel, each with a running_balance column. The balance starts at 3500.00 after your first salary and fluctuates with each transaction.

Seeing an error about transaction_id?

Make sure you are connected to the finance_tracker database, not the default postgres database. Check the database name shown in the Query Tool tab title.

If the query runs but running_balance values seem wrong, double-check that your CASE expression uses ELSE -amount (negative) for expenses.

Help me debug my running balance query

Rank expenses within each month using RANK()

Knowing which expenses are the biggest each month helps you spot where your money actually goes. The RANK() window function assigns a rank number to each row within a partition (group) based on an ordering you define.

  • Run this query to rank your expenses from largest to smallest within each month:
-- Rank expenses within each month
SELECT
    DATE_TRUNC('month', transaction_date)::DATE AS month,
    description,
    amount,
    RANK() OVER (
        PARTITION BY DATE_TRUNC('month', transaction_date)
        ORDER BY amount DESC
    ) AS expense_rank
FROM transactions
WHERE transaction_type = 'expense'
ORDER BY month, expense_rank;

What does PARTITION BY do here?

PARTITION BY DATE_TRUNC('month', transaction_date) splits transactions into groups by month. The ranking restarts at 1 for each new month.

ORDER BY amount DESC means the biggest expense gets rank 1. Without PARTITION BY, PostgreSQL would rank all expenses across all months in a single list.

You should see expenses listed by month, each with an expense_rank column. Rent payment (1200.00) is rank 1 in every month.

Results not grouped by month?

Make sure the ORDER BY month, expense_rank clause is at the end of the query (outside the OVER clause). The ORDER BY inside OVER controls ranking order. The ORDER BY at the end controls how results display.

Help me fix my RANK query results

Compare month-over-month spending with LAG()

The LAG() function reaches back to a previous row and pulls a value from it. This lets you compare each month's spending to the month before it without writing a self-join.

  • Run this query to see how your spending changes month to month:
-- Month-over-month spending comparison
SELECT
    month,
    total_expenses,
    LAG(total_expenses) OVER (ORDER BY month) AS previous_month_expenses,
    total_expenses - LAG(total_expenses) OVER (ORDER BY month) AS change_from_previous
FROM monthly_balance;

What does LAG do here?

LAG(total_expenses) OVER (ORDER BY month) grabs the total_expenses value from the previous row (ordered by month).

The first row has no previous month, so previous_month_expenses is NULL for April. The change_from_previous column subtracts last month from this month. A positive number means you spent more than last month.

Notice this query reads from monthly_balance (the View you created in the previous step). Views compose nicely with window functions because the View handles the grouping and the window function handles the row-to-row comparison.

You should see 3 rows (one per month). April shows NULL for previous_month_expenses because there is no month before it in the data.

Seeing an error about monthly_balance?

The monthly_balance View must exist before this query can run. If you see an error that the relation does not exist, go back and create the View from the previous step first.

Help me fix my LAG query

Why window functions instead of subqueries?

You could achieve similar results with self-joins or correlated subqueries, but window functions are more readable and often faster. They express the intent clearly: "give me this calculation relative to each row's position in a set."

In real-world analytics and reporting roles, window functions are expected knowledge. Interviewers frequently test RANK(), LAG(), and running totals with SUM OVER.

✔️ Awesome, I've got everything!

Great. Make sure you have kept a copy of all three queries for your records.

ⓧ I'd like to double check the full code

Here is the complete 04_window_functions.sql with all three window function queries:

-- Running balance over time
SELECT
    transaction_date,
    description,
    transaction_type,
    amount,
    SUM(
        CASE WHEN transaction_type = 'income' THEN amount
             ELSE -amount
        END
    ) OVER (ORDER BY transaction_date, transaction_id) AS running_balance
FROM transactions
ORDER BY transaction_date, transaction_id;

-- Rank expenses within each month
SELECT
    DATE_TRUNC('month', transaction_date)::DATE AS month,
    description,
    amount,
    RANK() OVER (
        PARTITION BY DATE_TRUNC('month', transaction_date)
        ORDER BY amount DESC
    ) AS expense_rank
FROM transactions
WHERE transaction_type = 'expense'
ORDER BY month, expense_rank;

-- Month-over-month spending comparison
SELECT
    month,
    total_expenses,
    LAG(total_expenses) OVER (ORDER BY month) AS previous_month_expenses,
    total_expenses - LAG(total_expenses) OVER (ORDER BY month) AS change_from_previous
FROM monthly_balance;

Secret mission

Add Budget Tracking

You've built a database that tracks what you spend. But how do you know if you're spending too much? In this secret mission, add a budget system that compares your actual spending against monthly limits and flags categories that are over budget.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally, so there are no ongoing costs.

Resources you used:

  • PostgreSQL 17 server running as a Windows service (uses minimal system resources).
  • finance_tracker database stored in C:\Program Files\PostgreSQL\17\data.

Keep everything running

No action needed. Choose this if you're still actively building or plan to add more transactions to your finance tracker.

  • PostgreSQL runs as a background service and uses minimal memory.
  • Your finance_tracker database persists across reboots automatically.
  • pgAdmin 4 only runs when you open it. It does not consume resources in the background.

Pause - I'll come back to this later

Stop the PostgreSQL service to free up memory. Your data is preserved and ready when you restart the service.

  • Press the Windows key and search for Services.
  • Open the Services app.
  • Scroll down to find postgresql-x64-17 in the list.
  • Right-click it and select Stop.
  • To resume later, right-click the same service and select Start.

Delete - I don't want to use this again

Remove all project resources and start fresh. This permanently deletes your database and uninstalls PostgreSQL.

  • Stop the PostgreSQL service first. Press the Windows key, search for Services, find postgresql-x64-17, right-click it and select Stop.
  • Open Settings and navigate to Apps.
  • Find PostgreSQL 17 in the apps list, click it, and select Uninstall. Follow the prompts to complete the removal.
  • If anything remains, delete the installation directory by running this in Command Prompt:
rmdir /s /q "C:\Program Files\PostgreSQL\17"
  • Remove the PATH entry you added. Press the Windows key, search for Environment Variables, open Edit the system environment variables, click Environment Variables, find Path under System variables, click Edit, and delete the C:\Program Files\PostgreSQL\17\bin entry.

Nice Work!

Nice Work!

Nice work! You have built a production-grade personal finance database on PostgreSQL, complete with reporting Views and trend analysis queries that answer real financial questions.

You've learned how to:

  • Designed a relational schema with NUMERIC for exact monetary values, CHECK constraints that actively reject invalid data, and foreign keys that enforce relationships between tables.
  • Created Views (monthly_spending and monthly_balance) that act as reusable reports, automatically recalculating whenever you query them.
  • Used window functions (SUM OVER, RANK, LAG) to calculate running balances, rank expenses, and compare month-over-month spending trends.
  • Secret Mission: Built a budget tracking system with a budgets table and a budget_report View that flags over-budget categories.

Ready to quiz yourself?