Banking Risk and Performance Report

Introduction

30 Second Summary

A large deposit can reflect regular income or money that leaves the account hours later. Those two stories demand very different decisions.

In this project, you will build a local relational database in PostgreSQL. Its reusable SQL report runs in pgAdmin to reveal financial performance plus behavior-based risk.

What You'll Build

Picture opening your finished analysis in pgAdmin to rank six customers, compare three branches, expose missed EMI payments, and isolate exactly two rapid cash-movement cases.

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

• A connected banking model you can query to trace how each synthetic customer links to accounts, transactions, loans, and EMI payments.
• A reusable performance report that shows monthly cash flow, ranked customer activity, branch comparisons, and EMI repayment results.
• A behavior-based risk result that isolates Vikram Shah and Arjun Rao while leaving recurring legitimate deposits unflagged.
• Secret Mission: Build a transparent customer value score from balances, deposits, and missed-EMI penalties.

Are there any prerequisites?

You need a Windows computer plus confidence with SQL joins and GROUP BY. The project guides you through installing PostgreSQL 18.6 and pgAdmin 4 v9.18 from scratch.

Before We Start

A useful banking report begins with a clear audience and a decision it needs to support.

Frame the decision

Banking analysts or branch managers could use this report to compare customer value or branch performance.

Risk or loan teams could use the same report to examine repayment risk or suspicious activity. Choose one audience and connect it to one specific decision.

Install PostgreSQL and Create the Database

Your banking report needs a database server before any query can return a result. PostgreSQL provides the local engine that stores the banking data.

pgAdmin 4 gives you a visual workspace for creating the database. It also lets you run each report query and inspect its results.

In this step, get ready to:

• Install PostgreSQL 18.6 with its server components.
• Install pgAdmin 4 v9.18.
• Create the banking_analytics database.

Install PostgreSQL 18.6

PostgreSQL runs as a local server on your Windows computer. The installer also provides the command-line tools required to manage that server.

• Visit the official PostgreSQL Windows download page.
• Download the Windows installer for PostgreSQL 18.6.
• Find the downloaded installer in your Downloads folder.
• Right-click the downloaded installer.
• Select Run as administrator.

What is this installer setting up?

The installer creates a PostgreSQL server on your computer. It also prepares the tools that pgAdmin uses to communicate with that server.

• Continue through the installer until you reach Select Components.
• Keep PostgreSQL Server selected.
• Keep pgAdmin 4 selected.
• Keep Command Line Tools selected.

What do the selected components provide?

• PostgreSQL Server stores the banking database and processes its queries.
• Command Line Tools provide local utilities for working with PostgreSQL.
• pgAdmin 4 provides the graphical database workspace used throughout this project.

Creating a database password can feel sensitive. This password protects only your local PostgreSQL server.

• Enter a local password for the postgres user when the installer requests one.
• Store the local password in your password manager.
• Keep the server port set to 5432.
• Complete the installation.

Choose the tab that matches the result of the PostgreSQL installation.

✔️ I installed the pinned version

The installer completed with PostgreSQL 18.6. Your local database server and command-line tools are ready.

ⓧ I installed an older version

This project uses PostgreSQL 18.6 so every query matches the pinned database version.

• Return to the official PostgreSQL Windows download page.
• Download the installer for PostgreSQL 18.6.
• Run the correct installer as an administrator.
• Keep PostgreSQL Server selected.
• Keep Command Line Tools selected.
• Complete the installation before continuing.

ⓧ PostgreSQL did not install

• Confirm that the downloaded installer is for PostgreSQL 18.6.
• Right-click the installer.
• Select Run as administrator.
• Keep PostgreSQL Server selected.
• Keep Command Line Tools selected.

Installer still not completing?

Close any earlier copy of the installer before trying again. Help me troubleshoot my PostgreSQL 18.6 Windows installation.

Install pgAdmin 4 v9.18

The PostgreSQL installer includes pgAdmin 4. Its bundled minor version is not pinned, so a separate pgAdmin installation gives you the verified v9.18 interface used in this project.

Why pin the pgAdmin version?

Database tools can move buttons or rename menu options between releases. Pinning pgAdmin keeps the instructions aligned with the interface on your screen.

• Visit the official pgAdmin 4 Windows download page.
• Download the Windows installer for pgAdmin 4 v9.18.
• Run the downloaded installer.
• Complete the pgAdmin 4 installation.

Choose the tab that matches the pgAdmin installation result.

✔️ I installed the pinned version

pgAdmin 4 v9.18 is installed. Your SQL workspace now matches the project instructions.

ⓧ I installed an older version

• Return to the official pgAdmin 4 Windows download page.
• Download the installer for pgAdmin 4 v9.18.
• Run the new installer.
• Complete the installation before continuing.

ⓧ pgAdmin will not open

• Confirm that the pgAdmin 4 v9.18 installer completed.
• Run the downloaded installer again if the installation stopped early.

Still unable to launch pgAdmin?

A partial installation can leave the desktop application unavailable. Help me troubleshoot my pgAdmin 4 v9.18 Windows installation.

• Press the Windows key to open Windows search.
• Type pgAdmin 4 and press Enter to open it.
• Select the bundled local PostgreSQL server in pgAdmin's left pane.
• Enter the local postgres password when prompted.

The local server unlocks and its database tree becomes available. That connection confirms pgAdmin can reach PostgreSQL on your computer.

Local server not unlocking?

• Use the same local password that you created during the PostgreSQL installation.
• Confirm that the server connection uses port 5432.
• Still stuck? Help me connect pgAdmin to my local PostgreSQL server.

Create and verify the database

A PostgreSQL server can hold several databases. Creating banking_analytics gives every table and report query in this project one dedicated home.

• Expand the connected local PostgreSQL server in pgAdmin's left pane.
• Right-click the databases node.
• Use the context menu to open the Database dialog.
• Enter banking_analytics in the Database field.
• Click Save.

You should see banking_analytics in the local server's database tree. The empty analytics database now exists on port 5432.

• Select banking_analytics in the database tree.
• Open the Tools menu.
• Select Query Tool.

Before you run the verification query, predict which database name PostgreSQL returns.

• Paste the database verification query into the Query Tool editor:

SELECT current_database();

What does this query prove?

• The current_database() function returns the name of the database used by the current connection.
• A result of banking_analytics proves that this Query Tool tab targets the correct database.

• Click Execute script to run the query.
• Inspect the Data Output panel below the editor.

You should see one row containing banking_analytics. That result confirms the database exists and the Query Tool is connected to it.

✔️ Awesome, I've got everything!

Your local banking_analytics database is created and accessible through pgAdmin 4 Query Tool.

ⓧ I'd like to double check the full code

• Compare the Query Tool editor with this complete verification query:

SELECT current_database();

Seeing a different database name?

• Select banking_analytics in the left database tree before opening Query Tool.
• Open a fresh Query Tool from the Tools menu after selecting the correct database.
• Still stuck? Help me connect Query Tool to banking_analytics.

That first database check is complete. Your pgAdmin workspace now points to banking_analytics.

Next, you'll create the six-table banking model. Then you'll load the synthetic banking data.

Build and Populate the Banking Model

Your PostgreSQL database is open in pgAdmin. It still needs a structure that connects each customer to their accounts, transactions, loans, and EMI payments.

This step creates the six-table relational model and loads its synthetic records. The final row counts prove that every part of the banking dataset loaded successfully.

In this step, get ready to:

• Create six related banking tables with enforced data integrity.
• Load synthetic branch, customer, account, transaction, loan, and EMI records.
• Verify the number of records in every table.

Create the relational schema

A primary key gives each record a unique identity. A foreign key links that record to another table.

The schema also uses NOT NULL requirements and CHECK constraints to reject incomplete or invalid values. These rules protect the report before any analysis begins.

The first statement removes matching tables before rebuilding them. Your project database is still empty, so this reset does not remove any records you need.

• Add the reset statement and the branches table to Query Tool by pasting this code:

DROP TABLE IF EXISTS emi_payments, transactions, loans, accounts, customers, branches CASCADE;

CREATE TABLE branches (
    branch_id INTEGER PRIMARY KEY,
    branch_name VARCHAR(80) NOT NULL,
    city VARCHAR(80) NOT NULL
);

What does this schema section do?

• The opening statement clears any earlier copy of the six-table model so the schema can be rebuilt cleanly.
• The branches table stores a unique ID for each branch.
• The required name and city fields prevent incomplete branch records.

• Select the SQL you just added.
• Click Execute script in the Query Tool toolbar.

You should see successful command details in the Messages panel. This confirms that the first table exists.

Seeing an execution error?

• Confirm that Query Tool is connected to banking_analytics.
• Check that the closing parenthesis is followed by a semicolon.
• Still stuck? Help me troubleshoot the first section of my PostgreSQL banking schema.

• Add the customers table below the existing SQL by pasting this code:

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    join_date DATE NOT NULL,
    risk_level VARCHAR(10) NOT NULL
        CHECK (risk_level IN ('LOW', 'MEDIUM', 'HIGH')),
    branch_id INTEGER NOT NULL REFERENCES branches(branch_id)
);

How are customers protected?

• Each customer receives a unique customer_id.
• The risk check limits risk_level to the three values used by this project.
• The branch reference requires every customer to belong to an existing branch.

• Select the customers table statement.
• Click Execute script.

You should see another successful command in Messages. The branch relationship is now enforced.

Customers table not created?

• Confirm that the branches table statement ran successfully first.
• Compare the three allowed risk values with the supplied code.
• Still stuck? Help me fix the customers table creation error.

• Add the accounts table below the customer definition by pasting this code:

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    account_type VARCHAR(20) NOT NULL
        CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    current_balance NUMERIC(12, 2) NOT NULL
        CHECK (current_balance >= 0),
    opened_date DATE NOT NULL,
    account_status VARCHAR(10) NOT NULL
        CHECK (account_status IN ('ACTIVE', 'FROZEN', 'CLOSED'))
);

How does the account model work?

• The customer reference connects each account to its owner.
• The account type check accepts savings or current accounts.
• The balance check prevents negative stored balances.
• The status check limits accounts to the three states used by the report.

• Select the accounts table statement.
• Click Execute script.

The Messages panel should confirm another successful command. Accounts can now be tied to valid customers.

Accounts table not created?

• Confirm that the customers table already exists.
• Check the parentheses around each allowed-value list.
• Still stuck? Help me diagnose the accounts table statement.

• Add the transactions table below the account definition by pasting this code:

CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    transaction_time TIMESTAMP NOT NULL,
    transaction_type VARCHAR(10) NOT NULL
        CHECK (transaction_type IN ('DEPOSIT', 'WITHDRAWAL')),
    amount NUMERIC(12, 2) NOT NULL
        CHECK (amount > 0),
    channel VARCHAR(20) NOT NULL
        CHECK (channel IN ('BRANCH', 'ONLINE', 'ATM', 'CARD', 'CASH'))
);

How are transactions validated?

• The account reference assigns every transaction to a valid account.
• The type check separates deposits from withdrawals.
• The amount check rejects zero or negative transaction values.
• The channel check keeps transaction sources consistent for later analysis.

• Select the transactions table statement.
• Click Execute script.

You should see a successful command in Messages. The model can now store timestamped cash movement.

Transactions table not created?

• Confirm that the accounts table statement completed successfully.
• Check that every text value inside the allowed-value lists has matching quotes.
• Still stuck? Help me troubleshoot the transactions table definition.

• Add the loans table below the transaction definition by pasting this code:

CREATE TABLE loans (
    loan_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    loan_type VARCHAR(20) NOT NULL,
    principal_amount NUMERIC(12, 2) NOT NULL
        CHECK (principal_amount > 0),
    outstanding_balance NUMERIC(12, 2) NOT NULL
        CHECK (outstanding_balance >= 0),
    start_date DATE NOT NULL,
    loan_status VARCHAR(10) NOT NULL
        CHECK (loan_status IN ('ACTIVE', 'CLOSED', 'DEFAULTED'))
);

How does the loan model work?

• The customer reference connects each loan to one borrower.
• The principal check requires a positive original loan amount.
• The outstanding balance check allows zero for a fully repaid loan.
• The status check supports active, closed, or defaulted loans.

• Select the loans table statement.
• Click Execute script.

The Messages panel should show a successful command. Borrowing records now connect to customers.

Loans table not created?

• Confirm that the customers table exists.
• Check the commas between the column definitions.
• Still stuck? Help me fix the loans table definition.

• Add the emi_payments table below the loan definition by pasting this code:

CREATE TABLE emi_payments (
    emi_payment_id INTEGER PRIMARY KEY,
    loan_id INTEGER NOT NULL REFERENCES loans(loan_id),
    due_date DATE NOT NULL,
    paid_date DATE,
    amount_due NUMERIC(12, 2) NOT NULL
        CHECK (amount_due > 0),
    amount_paid NUMERIC(12, 2) NOT NULL
        CHECK (amount_paid >= 0),
    payment_status VARCHAR(10) NOT NULL
        CHECK (payment_status IN ('PAID', 'LATE', 'MISSED'))
);

How are EMI outcomes recorded?

• The loan reference assigns every EMI record to a valid loan.
• The nullable paid_date field supports missed payments.
• The amount checks keep due values positive while allowing zero to be paid.
• The payment status check provides consistent paid, late, and missed categories.

• Select the emi_payments table statement.
• Click Execute script.

You should see the sixth successful table command in Messages. The complete relationship chain is now available.

EMI table not created?

• Confirm that the loans table exists.
• Check that paid_date has no NOT NULL requirement.
• Still stuck? Help me troubleshoot the EMI payment table.

• Click Save As in the Query Tool toolbar.
• Save the completed file as schema.sql.
• Click Execute script to rebuild the complete schema from the saved file.

You should see successful command details in Messages for the reset and all six table definitions.

Complete schema execution failed?

• Check that every table definition ends with a semicolon.
• Confirm that the tables appear in branches-first dependency order.
• Still stuck? Help me compare my complete schema.sql file with the required banking schema.

✔️ Awesome, I've got everything!

Your six table definitions are saved in schema.sql. The complete script executes successfully.

ⓧ I'd like to double check the full code

Compare your saved schema.sql file with this complete version.

DROP TABLE IF EXISTS emi_payments, transactions, loans, accounts, customers, branches CASCADE;

CREATE TABLE branches (
    branch_id INTEGER PRIMARY KEY,
    branch_name VARCHAR(80) NOT NULL,
    city VARCHAR(80) NOT NULL
);

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    join_date DATE NOT NULL,
    risk_level VARCHAR(10) NOT NULL
        CHECK (risk_level IN ('LOW', 'MEDIUM', 'HIGH')),
    branch_id INTEGER NOT NULL REFERENCES branches(branch_id)
);

CREATE TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    account_type VARCHAR(20) NOT NULL
        CHECK (account_type IN ('SAVINGS', 'CURRENT')),
    current_balance NUMERIC(12, 2) NOT NULL
        CHECK (current_balance >= 0),
    opened_date DATE NOT NULL,
    account_status VARCHAR(10) NOT NULL
        CHECK (account_status IN ('ACTIVE', 'FROZEN', 'CLOSED'))
);

CREATE TABLE transactions (
    transaction_id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id),
    transaction_time TIMESTAMP NOT NULL,
    transaction_type VARCHAR(10) NOT NULL
        CHECK (transaction_type IN ('DEPOSIT', 'WITHDRAWAL')),
    amount NUMERIC(12, 2) NOT NULL
        CHECK (amount > 0),
    channel VARCHAR(20) NOT NULL
        CHECK (channel IN ('BRANCH', 'ONLINE', 'ATM', 'CARD', 'CASH'))
);

CREATE TABLE loans (
    loan_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    loan_type VARCHAR(20) NOT NULL,
    principal_amount NUMERIC(12, 2) NOT NULL
        CHECK (principal_amount > 0),
    outstanding_balance NUMERIC(12, 2) NOT NULL
        CHECK (outstanding_balance >= 0),
    start_date DATE NOT NULL,
    loan_status VARCHAR(10) NOT NULL
        CHECK (loan_status IN ('ACTIVE', 'CLOSED', 'DEFAULTED'))
);

CREATE TABLE emi_payments (
    emi_payment_id INTEGER PRIMARY KEY,
    loan_id INTEGER NOT NULL REFERENCES loans(loan_id),
    due_date DATE NOT NULL,
    paid_date DATE,
    amount_due NUMERIC(12, 2) NOT NULL
        CHECK (amount_due > 0),
    amount_paid NUMERIC(12, 2) NOT NULL
        CHECK (amount_paid >= 0),
    payment_status VARCHAR(10) NOT NULL
        CHECK (payment_status IN ('PAID', 'LATE', 'MISSED'))
);

What should match?

The table order preserves every foreign-key dependency. The reset order reverses those dependencies so the model can be rebuilt safely.

Load the synthetic banking data

A schema defines valid shapes and relationships. The report also needs records that exercise normal activity, repayment problems, and rapid cash movement.

The inserts follow the same dependency order as the tables. Parent records are loaded before any child record refers to them.

• Open a new Query Tool tab for banking_analytics.
• Add the branch records by pasting this code:

INSERT INTO branches (branch_id, branch_name, city) VALUES
    (1, 'Central Branch', 'Mumbai'),
    (2, 'North Branch', 'Delhi'),
    (3, 'South Branch', 'Bengaluru');

What data does this add?

These rows create one branch in Mumbai, one in Delhi, and one in Bengaluru. Their IDs become the parent values used by customer records.

• Select the branch insert.
• Click Execute script.

You should see a successful insert in Messages. Three branches are now stored.

Branch records not inserted?

• Confirm that the branches table exists.
• Avoid executing the same insert twice because the branch IDs must remain unique.
• Still stuck? Help me troubleshoot the branch seed data.

• Add the customer records below the branch insert by pasting this code:

INSERT INTO customers (customer_id, full_name, join_date, risk_level, branch_id) VALUES
    (101, 'Asha Mehta', '2023-01-15', 'LOW', 1),
    (102, 'Rohan Kapoor', '2023-03-10', 'MEDIUM', 1),
    (103, 'Neha Iyer', '2023-02-20', 'LOW', 2),
    (104, 'Vikram Shah', '2023-07-01', 'HIGH', 2),
    (105, 'Priya Nair', '2023-04-18', 'LOW', 3),
    (106, 'Arjun Rao', '2023-08-22', 'MEDIUM', 3);

How are customers connected?

The six customer rows use branch IDs from the previous insert. This gives each customer a valid home branch.

• Select the customer insert.
• Click Execute script.

The Messages panel should confirm a successful insert. Six customers are now connected to three branches.

Customer records not inserted?

• Confirm that the branch insert ran first.
• Check that every branch_id is one of the three inserted branch IDs.
• Still stuck? Help me diagnose the customer seed insert.

• Add the account records below the customer insert by pasting this code:

INSERT INTO accounts (
    account_id, customer_id, account_type, current_balance, opened_date, account_status
) VALUES
    (1001, 101, 'SAVINGS', 12500.00, '2023-01-16', 'ACTIVE'),
    (1002, 102, 'CURRENT', 8200.00, '2023-03-11', 'ACTIVE'),
    (1003, 103, 'SAVINGS', 15600.00, '2023-02-21', 'ACTIVE'),
    (1004, 104, 'CURRENT', 2400.00, '2023-07-02', 'ACTIVE'),
    (1005, 105, 'SAVINGS', 9700.00, '2023-04-19', 'ACTIVE'),
    (1006, 106, 'SAVINGS', 5400.00, '2023-08-23', 'ACTIVE');

What do the account rows provide?

Each customer receives one account with a type, balance, opening date, and status. The account IDs become the bridge between customers and transactions.

• Select the account insert.
• Click Execute script.

You should see a successful insert in Messages. Six accounts are now linked to six customers.

Account records not inserted?

• Confirm that all six customer records were inserted first.
• Check that each account type and status matches an allowed schema value.
• Still stuck? Help me troubleshoot the account seed records.

The transaction insert is longer than the other inserts, so it is shown in two connected parts. Keep the cursor after the final comma in the first part.

• Start the transaction insert below the account records by pasting this first part:

INSERT INTO transactions (
    transaction_id, account_id, transaction_time, transaction_type, amount, channel
) VALUES
    (1, 1001, '2026-01-05 09:00:00', 'DEPOSIT', 7000.00, 'BRANCH'),
    (2, 1001, '2026-01-10 12:00:00', 'WITHDRAWAL', 1200.00, 'ATM'),
    (3, 1001, '2026-02-05 09:00:00', 'DEPOSIT', 7200.00, 'ONLINE'),
    (4, 1001, '2026-02-12 18:00:00', 'WITHDRAWAL', 1500.00, 'CARD'),
    (5, 1001, '2026-03-05 09:00:00', 'DEPOSIT', 7100.00, 'ONLINE'),
    (6, 1001, '2026-03-15 11:00:00', 'WITHDRAWAL', 900.00, 'ATM'),
    (7, 1002, '2026-01-08 10:00:00', 'DEPOSIT', 4500.00, 'ONLINE'),
    (8, 1002, '2026-01-20 17:00:00', 'WITHDRAWAL', 2200.00, 'CARD'),
    (9, 1002, '2026-02-08 10:00:00', 'DEPOSIT', 4800.00, 'ONLINE'),
    (10, 1002, '2026-02-21 16:00:00', 'WITHDRAWAL', 2500.00, 'CARD'),
    (11, 1002, '2026-03-08 10:00:00', 'DEPOSIT', 5000.00, 'ONLINE'),
    (12, 1002, '2026-03-25 15:00:00', 'WITHDRAWAL', 1800.00, 'ATM'),
    (13, 1003, '2026-01-03 11:00:00', 'DEPOSIT', 6200.00, 'BRANCH'),
    (14, 1003, '2026-01-16 14:00:00', 'WITHDRAWAL', 800.00, 'ATM'),
    (15, 1003, '2026-02-03 11:00:00', 'DEPOSIT', 6300.00, 'BRANCH'),

What does the first transaction group cover?

This first group records recurring deposits and ordinary withdrawals for the first three accounts. The final comma keeps the multirow insert open for the remaining records.

• Complete the transaction insert by pasting this second part directly after the final comma:

    (16, 1003, '2026-03-03 11:00:00', 'DEPOSIT', 6400.00, 'BRANCH'),
    (17, 1003, '2026-03-18 19:00:00', 'WITHDRAWAL', 1100.00, 'CARD'),
    (18, 1004, '2026-02-14 09:00:00', 'DEPOSIT', 9000.00, 'CASH'),
    (19, 1004, '2026-02-14 13:30:00', 'WITHDRAWAL', 8500.00, 'ATM'),
    (20, 1004, '2026-02-15 10:00:00', 'WITHDRAWAL', 900.00, 'ATM'),
    (21, 1004, '2026-03-10 10:00:00', 'DEPOSIT', 1200.00, 'CASH'),
    (22, 1005, '2026-01-12 09:30:00', 'DEPOSIT', 5200.00, 'ONLINE'),
    (23, 1005, '2026-02-12 09:30:00', 'DEPOSIT', 5300.00, 'ONLINE'),
    (24, 1005, '2026-02-20 17:30:00', 'WITHDRAWAL', 1300.00, 'CARD'),
    (25, 1005, '2026-03-12 09:30:00', 'DEPOSIT', 5400.00, 'ONLINE'),
    (26, 1005, '2026-03-22 18:00:00', 'WITHDRAWAL', 1700.00, 'CARD'),
    (27, 1006, '2026-01-11 10:00:00', 'DEPOSIT', 3000.00, 'CASH'),
    (28, 1006, '2026-01-19 13:00:00', 'WITHDRAWAL', 1000.00, 'ATM'),
    (29, 1006, '2026-03-02 10:00:00', 'DEPOSIT', 6000.00, 'CASH'),
    (30, 1006, '2026-03-02 16:00:00', 'WITHDRAWAL', 5100.00, 'ATM'),
    (31, 1006, '2026-03-03 09:00:00', 'WITHDRAWAL', 500.00, 'ATM');

What does the second transaction group add?

This group completes the three-month activity fixture. It includes ordinary recurring deposits plus the rapid deposit-to-withdrawal patterns used later in the report.

• Select the complete transaction insert from its opening line through its final semicolon.
• Click Execute script.

The Messages panel should confirm a successful insert. All 31 transactions are now available.

Transaction records not inserted?

• Check that the first transaction part ends with a comma.
• Check that the second transaction part ends with a semicolon.
• Still stuck? Help me repair the two-part transaction insert.

• Add the loan records below the transaction insert by pasting this code:

INSERT INTO loans (
    loan_id, customer_id, loan_type, principal_amount, outstanding_balance, start_date, loan_status
) VALUES
    (201, 102, 'HOME', 50000.00, 42000.00, '2025-06-01', 'ACTIVE'),
    (202, 104, 'PERSONAL', 12000.00, 9000.00, '2025-11-15', 'ACTIVE'),
    (203, 105, 'EDUCATION', 20000.00, 15000.00, '2025-08-10', 'ACTIVE'),
    (204, 106, 'VEHICLE', 18000.00, 16500.00, '2025-12-01', 'ACTIVE');

What do the loan rows represent?

Four customers receive loans with different principal values and outstanding balances. Their loan IDs become the parent values for EMI records.

• Select the loan insert.
• Click Execute script.

You should see a successful insert in Messages. Four loan records are now connected to borrowers.

Loan records not inserted?

• Confirm that customer IDs 102, 104, 105, and 106 already exist.
• Check that every balance satisfies the schema checks.
• Still stuck? Help me troubleshoot the loan seed records.

• Add the EMI payment records below the loan insert by pasting this code:

INSERT INTO emi_payments (
    emi_payment_id, loan_id, due_date, paid_date, amount_due, amount_paid, payment_status
) VALUES
    (301, 201, '2026-01-05', '2026-01-05', 2000.00, 2000.00, 'PAID'),
    (302, 201, '2026-02-05', '2026-02-08', 2000.00, 2000.00, 'LATE'),
    (303, 201, '2026-03-05', '2026-03-05', 2000.00, 2000.00, 'PAID'),
    (304, 202, '2026-01-10', NULL, 1200.00, 0.00, 'MISSED'),
    (305, 202, '2026-02-10', '2026-02-20', 1200.00, 1200.00, 'LATE'),
    (306, 202, '2026-03-10', NULL, 1200.00, 0.00, 'MISSED'),
    (307, 203, '2026-01-15', '2026-01-15', 1500.00, 1500.00, 'PAID'),
    (308, 203, '2026-02-15', '2026-02-15', 1500.00, 1500.00, 'PAID'),
    (309, 203, '2026-03-15', '2026-03-16', 1500.00, 1500.00, 'LATE'),
    (310, 204, '2026-01-20', NULL, 1000.00, 0.00, 'MISSED'),
    (311, 204, '2026-02-20', '2026-02-20', 1000.00, 1000.00, 'PAID'),
    (312, 204, '2026-03-20', '2026-03-22', 1000.00, 1000.00, 'LATE');

What repayment behavior is included?

The EMI records include paid, late, and missed outcomes. Missed payments use a null paid date with zero paid amount.

• Select the EMI payment insert.
• Click Execute script.

The Messages panel should confirm a successful insert. Twelve EMI outcomes are now ready for repayment analysis.

EMI records not inserted?

• Confirm that all four loan records were inserted first.
• Keep NULL unquoted for missed-payment dates.
• Still stuck? Help me diagnose the EMI payment insert.

Verify all six table counts

A successful insert message confirms that one statement ran. A single validation row gives you a complete inventory of the banking model.

• Add the validation query at the bottom of seed.sql by pasting this code:

SELECT
    (SELECT COUNT(*) FROM branches) AS branches,
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM accounts) AS accounts,
    (SELECT COUNT(*) FROM transactions) AS transactions,
    (SELECT COUNT(*) FROM loans) AS loans,
    (SELECT COUNT(*) FROM emi_payments) AS emi_payments;

What does the validation query prove?

Each nested count reads one table independently. The six results appear in one row so missing or duplicated records stand out immediately.

• Press Ctrl+S to open the save dialog.
• Save the completed file as seed.sql.

Before you run the validation query, do you expect every table to contain the intended number of records?

• Select the validation query.
• Click Execute script.

What should I see?

• The branches column shows 3.
• The customers column shows 6.
• The accounts column shows 6.
• The transactions column shows 31.
• The loans column shows 4.
• The emi_payments column shows 12.

Do any counts differ?

• Use the mismatched column to identify which insert needs attention.
• Check Messages for an earlier failed insert affecting that table.
• Still stuck? Help me reconcile my banking table counts.

✔️ Awesome, I've got everything!

Your seed.sql file is saved. Its validation query confirms the complete six-table dataset.

ⓧ I'd like to double check the full code

Compare your saved seed.sql file with this complete version. Keep the statements in this order.

INSERT INTO branches (branch_id, branch_name, city) VALUES
    (1, 'Central Branch', 'Mumbai'),
    (2, 'North Branch', 'Delhi'),
    (3, 'South Branch', 'Bengaluru');

INSERT INTO customers (customer_id, full_name, join_date, risk_level, branch_id) VALUES
    (101, 'Asha Mehta', '2023-01-15', 'LOW', 1),
    (102, 'Rohan Kapoor', '2023-03-10', 'MEDIUM', 1),
    (103, 'Neha Iyer', '2023-02-20', 'LOW', 2),
    (104, 'Vikram Shah', '2023-07-01', 'HIGH', 2),
    (105, 'Priya Nair', '2023-04-18', 'LOW', 3),
    (106, 'Arjun Rao', '2023-08-22', 'MEDIUM', 3);

INSERT INTO accounts (
    account_id, customer_id, account_type, current_balance, opened_date, account_status
) VALUES
    (1001, 101, 'SAVINGS', 12500.00, '2023-01-16', 'ACTIVE'),
    (1002, 102, 'CURRENT', 8200.00, '2023-03-11', 'ACTIVE'),
    (1003, 103, 'SAVINGS', 15600.00, '2023-02-21', 'ACTIVE'),
    (1004, 104, 'CURRENT', 2400.00, '2023-07-02', 'ACTIVE'),
    (1005, 105, 'SAVINGS', 9700.00, '2023-04-19', 'ACTIVE'),
    (1006, 106, 'SAVINGS', 5400.00, '2023-08-23', 'ACTIVE');

INSERT INTO transactions (
    transaction_id, account_id, transaction_time, transaction_type, amount, channel
) VALUES
    (1, 1001, '2026-01-05 09:00:00', 'DEPOSIT', 7000.00, 'BRANCH'),
    (2, 1001, '2026-01-10 12:00:00', 'WITHDRAWAL', 1200.00, 'ATM'),
    (3, 1001, '2026-02-05 09:00:00', 'DEPOSIT', 7200.00, 'ONLINE'),
    (4, 1001, '2026-02-12 18:00:00', 'WITHDRAWAL', 1500.00, 'CARD'),
    (5, 1001, '2026-03-05 09:00:00', 'DEPOSIT', 7100.00, 'ONLINE'),
    (6, 1001, '2026-03-15 11:00:00', 'WITHDRAWAL', 900.00, 'ATM'),
    (7, 1002, '2026-01-08 10:00:00', 'DEPOSIT', 4500.00, 'ONLINE'),
    (8, 1002, '2026-01-20 17:00:00', 'WITHDRAWAL', 2200.00, 'CARD'),
    (9, 1002, '2026-02-08 10:00:00', 'DEPOSIT', 4800.00, 'ONLINE'),
    (10, 1002, '2026-02-21 16:00:00', 'WITHDRAWAL', 2500.00, 'CARD'),
    (11, 1002, '2026-03-08 10:00:00', 'DEPOSIT', 5000.00, 'ONLINE'),
    (12, 1002, '2026-03-25 15:00:00', 'WITHDRAWAL', 1800.00, 'ATM'),
    (13, 1003, '2026-01-03 11:00:00', 'DEPOSIT', 6200.00, 'BRANCH'),
    (14, 1003, '2026-01-16 14:00:00', 'WITHDRAWAL', 800.00, 'ATM'),
    (15, 1003, '2026-02-03 11:00:00', 'DEPOSIT', 6300.00, 'BRANCH'),
    (16, 1003, '2026-03-03 11:00:00', 'DEPOSIT', 6400.00, 'BRANCH'),
    (17, 1003, '2026-03-18 19:00:00', 'WITHDRAWAL', 1100.00, 'CARD'),
    (18, 1004, '2026-02-14 09:00:00', 'DEPOSIT', 9000.00, 'CASH'),
    (19, 1004, '2026-02-14 13:30:00', 'WITHDRAWAL', 8500.00, 'ATM'),
    (20, 1004, '2026-02-15 10:00:00', 'WITHDRAWAL', 900.00, 'ATM'),
    (21, 1004, '2026-03-10 10:00:00', 'DEPOSIT', 1200.00, 'CASH'),
    (22, 1005, '2026-01-12 09:30:00', 'DEPOSIT', 5200.00, 'ONLINE'),
    (23, 1005, '2026-02-12 09:30:00', 'DEPOSIT', 5300.00, 'ONLINE'),
    (24, 1005, '2026-02-20 17:30:00', 'WITHDRAWAL', 1300.00, 'CARD'),
    (25, 1005, '2026-03-12 09:30:00', 'DEPOSIT', 5400.00, 'ONLINE'),
    (26, 1005, '2026-03-22 18:00:00', 'WITHDRAWAL', 1700.00, 'CARD'),
    (27, 1006, '2026-01-11 10:00:00', 'DEPOSIT', 3000.00, 'CASH'),
    (28, 1006, '2026-01-19 13:00:00', 'WITHDRAWAL', 1000.00, 'ATM'),
    (29, 1006, '2026-03-02 10:00:00', 'DEPOSIT', 6000.00, 'CASH'),
    (30, 1006, '2026-03-02 16:00:00', 'WITHDRAWAL', 5100.00, 'ATM'),
    (31, 1006, '2026-03-03 09:00:00', 'WITHDRAWAL', 500.00, 'ATM');

INSERT INTO loans (
    loan_id, customer_id, loan_type, principal_amount, outstanding_balance, start_date, loan_status
) VALUES
    (201, 102, 'HOME', 50000.00, 42000.00, '2025-06-01', 'ACTIVE'),
    (202, 104, 'PERSONAL', 12000.00, 9000.00, '2025-11-15', 'ACTIVE'),
    (203, 105, 'EDUCATION', 20000.00, 15000.00, '2025-08-10', 'ACTIVE'),
    (204, 106, 'VEHICLE', 18000.00, 16500.00, '2025-12-01', 'ACTIVE');

INSERT INTO emi_payments (
    emi_payment_id, loan_id, due_date, paid_date, amount_due, amount_paid, payment_status
) VALUES
    (301, 201, '2026-01-05', '2026-01-05', 2000.00, 2000.00, 'PAID'),
    (302, 201, '2026-02-05', '2026-02-08', 2000.00, 2000.00, 'LATE'),
    (303, 201, '2026-03-05', '2026-03-05', 2000.00, 2000.00, 'PAID'),
    (304, 202, '2026-01-10', NULL, 1200.00, 0.00, 'MISSED'),
    (305, 202, '2026-02-10', '2026-02-20', 1200.00, 1200.00, 'LATE'),
    (306, 202, '2026-03-10', NULL, 1200.00, 0.00, 'MISSED'),
    (307, 203, '2026-01-15', '2026-01-15', 1500.00, 1500.00, 'PAID'),
    (308, 203, '2026-02-15', '2026-02-15', 1500.00, 1500.00, 'PAID'),
    (309, 203, '2026-03-15', '2026-03-16', 1500.00, 1500.00, 'LATE'),
    (310, 204, '2026-01-20', NULL, 1000.00, 0.00, 'MISSED'),
    (311, 204, '2026-02-20', '2026-02-20', 1000.00, 1000.00, 'PAID'),
    (312, 204, '2026-03-20', '2026-03-22', 1000.00, 1000.00, 'LATE');

SELECT
    (SELECT COUNT(*) FROM branches) AS branches,
    (SELECT COUNT(*) FROM customers) AS customers,
    (SELECT COUNT(*) FROM accounts) AS accounts,
    (SELECT COUNT(*) FROM transactions) AS transactions,
    (SELECT COUNT(*) FROM loans) AS loans,
    (SELECT COUNT(*) FROM emi_payments) AS emi_payments;

Why does the order matter?

Every parent row appears before a child row that references it. The final query then checks all six loaded tables in one result.

That is the data foundation locked in. Your six related tables now hold every record needed for the banking report.

Next, you'll turn these records into monthly cash-flow results and test a simple risk rule against real-looking customer behavior.

Expose the Naive Risk Rule

Your six-table banking model is loaded with synthetic activity. The next question is what those rows reveal when viewed as a report.

A screening rule can look useful before you inspect the customers it flags. This step puts an amount-only rule against the full dataset so its evidence can be judged.

In this step, get ready to:

• Build a monthly cash-flow baseline.
• Test an amount-only transaction rule.
• Document what the results prove.

Create the monthly cash-flow baseline

Monthly aggregation compresses individual transactions into a trend that is easier to compare. The report uses date_trunc to group timestamps by month.

• In the pgAdmin Query Tool from earlier, replace the editor contents with this query:

-- 1. Monthly transaction volume and cash flow
SELECT
    CAST(date_trunc('month', transaction_time) AS DATE) AS month,
    COUNT(*) AS transaction_count,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE 0 END) AS deposits,
    SUM(CASE WHEN transaction_type = 'WITHDRAWAL' THEN amount ELSE 0 END) AS withdrawals,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE -amount END) AS net_flow
FROM transactions
GROUP BY CAST(date_trunc('month', transaction_time) AS DATE)
ORDER BY month;

How does the monthly baseline work?

• The date_trunc expression groups transaction timestamps into calendar months.
• The CASE expressions separate deposits from withdrawals inside the same grouped result.
• The final SUM expression subtracts withdrawals to calculate the monthly net flow.

• Save the query as analysis.sql with Save As.

Before you execute the query, predict how many monthly rows the transactions from January through March will produce.

• Select Execute script in the Query Tool toolbar.

You will see three rows in the Data Output panel. The output includes transaction_count, deposits, withdrawals, and net_flow for each month.

Your first analysis result is live. The 31 raw transactions now read as a three-month cash-flow trend.

Monthly query not returning three rows?

• Confirm that banking_analytics is the selected database.
• Compare the table name transactions with the populated table from your earlier validation.
• Still stuck? Help me troubleshoot the monthly cash-flow query.

Run the amount-only rule

An amount threshold reduces the transaction list to records that deserve review. The next query connects every matching transaction to its customer so you can judge the flagged activity.

• Place your cursor below the monthly cash-flow query in analysis.sql.
• Paste this query on the next line:

-- 2. Designed obstacle: amount-only flags include recurring deposits
SELECT
    c.full_name,
    t.transaction_id,
    t.transaction_time,
    t.transaction_type,
    t.amount,
    t.channel
FROM transactions AS t
JOIN accounts AS a ON a.account_id = t.account_id
JOIN customers AS c ON c.customer_id = a.customer_id
WHERE t.amount >= 5000
ORDER BY t.amount DESC, t.transaction_time;

What does the rule examine?

• The aliases t, a, and c keep the transaction, account, and customer joins readable.
• The WHERE condition keeps transactions whose amount is at least 5000.
• The ORDER BY clause places the largest matches first.

Before you run this block, consider whether amount alone will isolate unusual cash movement or capture routine customer activity.

• Highlight the second query block in analysis.sql.
• Select Execute script.

The rule returns 14 transactions. Asha Mehta and Neha Iyer appear because their recurring deposits meet the threshold.

This is the designed shortfall. The rule treats amount as proof even when the wider behavior is routine.

Naive query not returning 14 rows?

• Confirm that the filter reads t.amount >= 5000.
• Confirm that the earlier validation still reports 31 rows in transactions.
• Still stuck? Help me troubleshoot the amount-only query.

Document the limitation

A report should preserve the interpretation behind its results. A SQL comment keeps the rule's limitation beside the query that exposed it.

• Place your cursor below the amount-only query in analysis.sql.
• Add these comments on the next line:

-- Limitation observed: high value alone does not distinguish recurring deposits
-- from rapid deposit-to-withdrawal behavior.

Why record the limitation?

The comment separates an observed result from a reliable risk conclusion. Anyone reopening the report can see why the threshold needs more behavioral evidence.

• Press Ctrl+S to save analysis.sql.

✔️ Awesome, I've got everything!

Your saved analysis.sql contains the monthly baseline, the amount-only rule, and the observed limitation.

ⓧ I'd like to double check the full code

• Compare your saved analysis.sql with this complete version.

-- 1. Monthly transaction volume and cash flow
SELECT
    CAST(date_trunc('month', transaction_time) AS DATE) AS month,
    COUNT(*) AS transaction_count,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE 0 END) AS deposits,
    SUM(CASE WHEN transaction_type = 'WITHDRAWAL' THEN amount ELSE 0 END) AS withdrawals,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE -amount END) AS net_flow
FROM transactions
GROUP BY CAST(date_trunc('month', transaction_time) AS DATE)
ORDER BY month;

-- 2. Designed obstacle: amount-only flags include recurring deposits
SELECT
    c.full_name,
    t.transaction_id,
    t.transaction_time,
    t.transaction_type,
    t.amount,
    t.channel
FROM transactions AS t
JOIN accounts AS a ON a.account_id = t.account_id
JOIN customers AS c ON c.customer_id = a.customer_id
WHERE t.amount >= 5000
ORDER BY t.amount DESC, t.transaction_time;

-- Limitation observed: high value alone does not distinguish recurring deposits
-- from rapid deposit-to-withdrawal behavior.

Before the final run, predict whether the saved report will reproduce both result counts.

• Select Execute script to run the completed file.
• Inspect both result sets in the Data Output panel.

The monthly report returns three rows. The amount-only rule returns 14 rows that include recurring deposits for Asha Mehta and Neha Iyer.

Completed report not reproducing both results?

• Run each SELECT block separately if the panel displays only one result set.
• Compare your file with the full-code reference for missing joins or filters.
• Still stuck? Help me verify the completed analysis file.

You have exposed the amount-only rule's blind spot with evidence from your own report. Next, you will turn the same banking data into customer, branch, and repayment comparisons.

Rank Customers, Branches, and Repayment

Your monthly report exposed cash-flow patterns. Your amount-only rule also showed how a simple total can misclassify legitimate activity.

Customer rankings reveal where activity is concentrated. Branch comparisons show which location holds the strongest balances.

Separate branch metrics protect balances from row multiplication. An EMI summary makes late or missed repayments visible.

In this step, get ready to:

• Rank all six customers by transaction activity.
• Compare branch balances without multiplying account values.
• Summarize paid, late, or missed EMI repayments.

Rank customer activity

A common table expression prepares a temporary result for the query that follows. The customer_activity CTE produces one row per customer before rank() OVER assigns positions.

• In analysis.sql, place your cursor after the comment documenting the amount-only rule's limitation.
• Add the customer activity ranking by pasting the query below:

-- 3. Customer activity ranking
WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(t.amount) AS total_activity_amount
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.customer_id, c.full_name
)
SELECT
    customer_id,
    full_name,
    transaction_count,
    total_activity_amount,
    rank() OVER (ORDER BY transaction_count DESC) AS activity_rank
FROM customer_activity
ORDER BY activity_rank, full_name;

How Does the Ranking Work?

• The customer_activity CTE groups the joined records into one row per customer.
• The transaction_count value measures how often each customer made a transaction.
• The total_activity_amount value measures the combined amount of that activity.
• The rank() OVER expression gives tied transaction counts the same rank.

• Save analysis.sql by pressing Ctrl+S.
• Select only the new customer activity query in the editor.
• Click Execute script in the Query Tool toolbar.

You'll see six customers in the Data Output panel. Each customer has a transaction count plus an activity rank.

That is your first decision-ready comparison complete. All six customers now have a visible activity position.

Missing Customers or Ranks?

• Confirm that the query starts with the complete customer_activity CTE.
• Check that the joins use a.customer_id = c.customer_id plus t.account_id = a.account_id.
• Still stuck? Help me troubleshoot my customer activity ranking query.

Compare branch performance

Joining account balances directly to transactions would repeat each balance for every matching transaction. Separate CTEs calculate balance metrics before the final branch comparison combines them.

• Place your cursor after the customer activity query in analysis.sql.
• Add the protected branch comparison by pasting the query below:

-- 4. Branch performance with balances and transactions aggregated separately
WITH balance_metrics AS (
    SELECT
        c.branch_id,
        COUNT(DISTINCT c.customer_id) AS customer_count,
        ROUND(AVG(a.current_balance), 2) AS average_account_balance,
        SUM(a.current_balance) AS total_balance
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    GROUP BY c.branch_id
),
transaction_metrics AS (
    SELECT
        c.branch_id,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(CASE WHEN t.transaction_type = 'DEPOSIT' THEN t.amount ELSE -t.amount END) AS net_flow
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.branch_id
)
SELECT
    b.branch_name, bm.customer_count, bm.average_account_balance,
    bm.total_balance, tm.transaction_count, tm.net_flow
FROM branches AS b
JOIN balance_metrics AS bm ON bm.branch_id = b.branch_id
JOIN transaction_metrics AS tm ON tm.branch_id = b.branch_id
ORDER BY bm.total_balance DESC;

Why Use Separate Metrics?

• The balance_metrics CTE calculates customer counts plus account balances once per branch.
• The transaction_metrics CTE calculates transaction counts plus net flow separately.
• The final joins combine each branch's prepared metrics through aliases b, bm, or tm.
• This structure prevents transaction rows from multiplying the account balances.

• Save analysis.sql by pressing Ctrl+S.
• Select only the new branch performance query in the editor.
• Click Execute script in the Query Tool toolbar.

You'll see three branches in the Data Output panel. Central Branch leads the results by total balance.

You have protected the branch comparison from duplicated balances. The strongest branch now stands out without inflated totals.

Seeing Inflated Branch Balances?

• Confirm that balance_metrics joins only customers with accounts.
• Confirm that transaction_metrics performs the join to transactions.
• Still stuck? Help me fix duplicated balances in my branch performance query.

Summarize EMI repayment

Repayment performance needs separate counts for paid, late, or missed EMIs. Comparing the total amount paid with the total amount due adds a repayment percentage for each borrower.

• Place your cursor after the branch performance query in analysis.sql.
• Add the EMI repayment summary by pasting the query below:

-- 5. EMI repayment behavior
SELECT
    c.full_name,
    COUNT(ep.emi_payment_id) AS emi_count,
    SUM(CASE WHEN ep.payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN ep.payment_status = 'LATE' THEN 1 ELSE 0 END) AS late_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN 1 ELSE 0 END) AS missed_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN ep.amount_due ELSE 0 END) AS missed_amount,
    ROUND(
        100.0 * SUM(ep.amount_paid) / NULLIF(SUM(ep.amount_due), 0),
        2
    ) AS repayment_percent
FROM customers AS c
JOIN loans AS l ON l.customer_id = c.customer_id
JOIN emi_payments AS ep ON ep.loan_id = l.loan_id
GROUP BY c.customer_id, c.full_name
ORDER BY missed_count DESC, repayment_percent, c.full_name;

How Is Repayment Measured?

• Each CASE expression counts one repayment status without hiding the other statuses.
• The missed_amount calculation exposes how much scheduled repayment remains unpaid.
• The NULLIF expression protects the percentage calculation from a zero amount due.
• The ROUND expression presents repayment_percent with two decimal places.

• Save analysis.sql by pressing Ctrl+S.

Before you run this final block, which borrower do you expect to have the highest missed count?

• Select only the new EMI repayment query in the editor.
• Click Execute script in the Query Tool toolbar.

You'll see four borrowers in the Data Output panel. Vikram Shah appears with 2 missed payments.

Your report now makes repayment risk visible at customer level. Vikram Shah's missed EMIs rise above the other borrower results.

Missing EMI Counts or Percentages?

• Confirm that alias l joins to c.customer_id through l.customer_id.
• Confirm that alias ep joins to l.loan_id through ep.loan_id.
• Still stuck? Help me troubleshoot my EMI repayment query.

✔️ Awesome, I've got everything!

• Confirm that analysis.sql is saved with all three new report blocks.

ⓧ I'd like to double check the full code

Use this complete cumulative file to compare every query through the EMI repayment report.

-- 1. Monthly transaction volume and cash flow
SELECT
    CAST(date_trunc('month', transaction_time) AS DATE) AS month,
    COUNT(*) AS transaction_count,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE 0 END) AS deposits,
    SUM(CASE WHEN transaction_type = 'WITHDRAWAL' THEN amount ELSE 0 END) AS withdrawals,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE -amount END) AS net_flow
FROM transactions
GROUP BY CAST(date_trunc('month', transaction_time) AS DATE)
ORDER BY month;

-- 2. Designed obstacle: amount-only flags include recurring deposits
SELECT
    c.full_name,
    t.transaction_id,
    t.transaction_time,
    t.transaction_type,
    t.amount,
    t.channel
FROM transactions AS t
JOIN accounts AS a ON a.account_id = t.account_id
JOIN customers AS c ON c.customer_id = a.customer_id
WHERE t.amount >= 5000
ORDER BY t.amount DESC, t.transaction_time;

-- Limitation observed: high value alone does not distinguish recurring deposits
-- from rapid deposit-to-withdrawal behavior.

-- 3. Customer activity ranking
WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(t.amount) AS total_activity_amount
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.customer_id, c.full_name
)
SELECT
    customer_id,
    full_name,
    transaction_count,
    total_activity_amount,
    rank() OVER (ORDER BY transaction_count DESC) AS activity_rank
FROM customer_activity
ORDER BY activity_rank, full_name;

-- 4. Branch performance with balances and transactions aggregated separately
WITH balance_metrics AS (
    SELECT
        c.branch_id,
        COUNT(DISTINCT c.customer_id) AS customer_count,
        ROUND(AVG(a.current_balance), 2) AS average_account_balance,
        SUM(a.current_balance) AS total_balance
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    GROUP BY c.branch_id
),
transaction_metrics AS (
    SELECT
        c.branch_id,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(CASE WHEN t.transaction_type = 'DEPOSIT' THEN t.amount ELSE -t.amount END) AS net_flow
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.branch_id
)
SELECT
    b.branch_name,
    bm.customer_count,
    bm.average_account_balance,
    bm.total_balance,
    tm.transaction_count,
    tm.net_flow
FROM branches AS b
JOIN balance_metrics AS bm ON bm.branch_id = b.branch_id
JOIN transaction_metrics AS tm ON tm.branch_id = b.branch_id
ORDER BY bm.total_balance DESC;

-- 5. EMI repayment behavior
SELECT
    c.full_name,
    COUNT(ep.emi_payment_id) AS emi_count,
    SUM(CASE WHEN ep.payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN ep.payment_status = 'LATE' THEN 1 ELSE 0 END) AS late_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN 1 ELSE 0 END) AS missed_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN ep.amount_due ELSE 0 END) AS missed_amount,
    ROUND(
        100.0 * SUM(ep.amount_paid) / NULLIF(SUM(ep.amount_due), 0),
        2
    ) AS repayment_percent
FROM customers AS c
JOIN loans AS l ON l.customer_id = c.customer_id
JOIN emi_payments AS ep ON ep.loan_id = l.loan_id
GROUP BY c.customer_id, c.full_name
ORDER BY missed_count DESC, repayment_percent, c.full_name;

Your report now ranks customer activity, protects branch totals, or exposes repayment problems. Next, you'll replace the broad amount flag with evidence of rapid cash movement.

Detect Rapid Cash Movement

Your report now exposes missed EMIs alongside customer activity. It also shows which branch holds the strongest balances.

The earlier amount-only rule still flags recurring legitimate deposits. This step replaces that broad signal with behavioral evidence.

A PostgreSQL self-join follows each large deposit through the next 24 hours. A ratio check keeps cases where at least 80 percent leaves the same account.

In this step, get ready to:

• Pair deposits with later withdrawals from the same account.
• Apply the 24-hour timing rule plus the 80 percent withdrawal rule.
• Confirm the final two behavioral matches.

Pair deposits with later withdrawals

A self-join gives the transactions table two roles inside one query. The d alias represents a candidate deposit while w represents a later withdrawal.

• Return to the analysis.sql tab in pgAdmin from earlier.
• Move to the end of the file.
• Add the behavior-based query by copying the code below:

-- 6. Behavior-based rule: large deposit followed by rapid withdrawal
WITH rapid_movement AS (
    SELECT
        c.customer_id,
        c.full_name,
        d.transaction_id AS deposit_transaction_id,
        w.transaction_id AS withdrawal_transaction_id,
        d.transaction_time AS deposit_time,
        w.transaction_time AS withdrawal_time,
        d.amount AS deposit_amount,
        w.amount AS withdrawal_amount,
        ROUND(100.0 * w.amount / d.amount, 2) AS withdrawal_percent
    FROM transactions AS d
    JOIN transactions AS w
        ON w.account_id = d.account_id
        AND w.transaction_time > d.transaction_time
        AND w.transaction_time <= d.transaction_time + INTERVAL '24 hours'
    JOIN accounts AS a ON a.account_id = d.account_id
    JOIN customers AS c ON c.customer_id = a.customer_id
    WHERE d.transaction_type = 'DEPOSIT'
      AND w.transaction_type = 'WITHDRAWAL'
      AND d.amount >= 5000
      AND w.amount >= d.amount * 0.80
)
SELECT
    withdrawal_percent
FROM rapid_movement
ORDER BY withdrawal_percent DESC, full_name;

How Does the Behavior Rule Work?

• The d alias represents each possible deposit.
• The w alias represents each possible withdrawal.
• The account equality keeps both transactions tied to one account.
• The timestamp checks require the withdrawal to happen later within INTERVAL '24 hours'.
• The transaction-type checks establish the direction of the cash movement.
• The deposit threshold keeps deposits of at least 5000.
• The final ratio requires the withdrawal to reach at least 80 percent of the deposit.

Before you run this check, how many percentage values do you expect to survive all three behavior conditions?

• Select only the behavior-based query in the Query Tool editor.
• Run the selection by clicking Execute script.

You will see two rows in the Data Output panel. That is the key signal working: two transaction pairs satisfy every behavior condition.

Not Seeing Two Percentages?

• Confirm that the selected query starts with WITH rapid_movement AS (.
• Check that w.account_id = d.account_id remains unchanged.
• Still stuck? Help me troubleshoot why my rapid_movement query does not return two rows.

Show the evidence behind each match

The two percentages prove that the filter works. The final projection adds customer identity plus the transaction evidence.

• In analysis.sql, locate the final SELECT at the bottom of the behavior-based query.
• Find this code:

SELECT
    withdrawal_percent
FROM rapid_movement
ORDER BY withdrawal_percent DESC, full_name;

Why Expand the Projection?

This narrow projection confirms that the percentage filter returns two matches. A wider projection makes each match traceable to its customer and transaction pair.

• Replace that final projection with the code below:

SELECT
    customer_id,
    full_name,
    deposit_transaction_id,
    withdrawal_transaction_id,
    deposit_time,
    withdrawal_time,
    deposit_amount,
    withdrawal_amount,
    withdrawal_percent
FROM rapid_movement
ORDER BY withdrawal_percent DESC, full_name;

What Does the Final Result Show?

• The customer columns identify who owns each matching account.
• The transaction IDs make each deposit-withdrawal pair traceable.
• The timestamps show how quickly the withdrawal followed the deposit.
• The amount columns show the cash entering and leaving the account.
• The percentage expresses the withdrawal as a share of its matched deposit.

Before you run the completed query, which two customers do you expect to remain after the behavior checks?

• Select the completed behavior-based query in Query Tool.
• Run the selection by clicking Execute script.

The Data Output panel shows exactly two rows. The customer names are Vikram Shah and Arjun Rao.

You have closed the false-positive gap: the report now demands a linked sequence before it raises a match.

Seeing More or Fewer Than Two Rows?

• Confirm that the time condition still ends with d.transaction_time + INTERVAL '24 hours'.
• Confirm that d.amount >= 5000 is unchanged.
• Confirm that w.amount >= d.amount * 0.80 is unchanged.

Need another pair of eyes? Help me compare my behavior-based query with the expected two-row result.

• Save analysis.sql by pressing Ctrl+S.

✔️ Awesome, I've got everything!

Your saved analysis.sql now contains all six report blocks. The final block isolates the two rapid cash-movement cases.

ⓧ I'd like to double check the full code

• Compare your saved analysis.sql with the complete report below:

-- 1. Monthly transaction volume and cash flow
SELECT
    CAST(date_trunc('month', transaction_time) AS DATE) AS month,
    COUNT(*) AS transaction_count,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE 0 END) AS deposits,
    SUM(CASE WHEN transaction_type = 'WITHDRAWAL' THEN amount ELSE 0 END) AS withdrawals,
    SUM(CASE WHEN transaction_type = 'DEPOSIT' THEN amount ELSE -amount END) AS net_flow
FROM transactions
GROUP BY CAST(date_trunc('month', transaction_time) AS DATE)
ORDER BY month;

-- 2. Designed obstacle: amount-only flags include recurring deposits
SELECT
    c.full_name,
    t.transaction_id,
    t.transaction_time,
    t.transaction_type,
    t.amount,
    t.channel
FROM transactions AS t
JOIN accounts AS a ON a.account_id = t.account_id
JOIN customers AS c ON c.customer_id = a.customer_id
WHERE t.amount >= 5000
ORDER BY t.amount DESC, t.transaction_time;

-- Limitation observed: high value alone does not distinguish recurring deposits
-- from rapid deposit-to-withdrawal behavior.

-- 3. Customer activity ranking
WITH customer_activity AS (
    SELECT
        c.customer_id,
        c.full_name,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(t.amount) AS total_activity_amount
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.customer_id, c.full_name
)
SELECT
    customer_id,
    full_name,
    transaction_count,
    total_activity_amount,
    rank() OVER (ORDER BY transaction_count DESC) AS activity_rank
FROM customer_activity
ORDER BY activity_rank, full_name;

-- 4. Branch performance with balances and transactions aggregated separately
WITH balance_metrics AS (
    SELECT
        c.branch_id,
        COUNT(DISTINCT c.customer_id) AS customer_count,
        ROUND(AVG(a.current_balance), 2) AS average_account_balance,
        SUM(a.current_balance) AS total_balance
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    GROUP BY c.branch_id
),
transaction_metrics AS (
    SELECT
        c.branch_id,
        COUNT(t.transaction_id) AS transaction_count,
        SUM(CASE WHEN t.transaction_type = 'DEPOSIT' THEN t.amount ELSE -t.amount END) AS net_flow
    FROM customers AS c
    JOIN accounts AS a ON a.customer_id = c.customer_id
    JOIN transactions AS t ON t.account_id = a.account_id
    GROUP BY c.branch_id
)
SELECT
    b.branch_name,
    bm.customer_count,
    bm.average_account_balance,
    bm.total_balance,
    tm.transaction_count,
    tm.net_flow
FROM branches AS b
JOIN balance_metrics AS bm ON bm.branch_id = b.branch_id
JOIN transaction_metrics AS tm ON tm.branch_id = b.branch_id
ORDER BY bm.total_balance DESC;

-- 5. EMI repayment behavior
SELECT
    c.full_name,
    COUNT(ep.emi_payment_id) AS emi_count,
    SUM(CASE WHEN ep.payment_status = 'PAID' THEN 1 ELSE 0 END) AS paid_count,
    SUM(CASE WHEN ep.payment_status = 'LATE' THEN 1 ELSE 0 END) AS late_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN 1 ELSE 0 END) AS missed_count,
    SUM(CASE WHEN ep.payment_status = 'MISSED' THEN ep.amount_due ELSE 0 END) AS missed_amount,
    ROUND(
        100.0 * SUM(ep.amount_paid) / NULLIF(SUM(ep.amount_due), 0),
        2
    ) AS repayment_percent
FROM customers AS c
JOIN loans AS l ON l.customer_id = c.customer_id
JOIN emi_payments AS ep ON ep.loan_id = l.loan_id
GROUP BY c.customer_id, c.full_name
ORDER BY missed_count DESC, repayment_percent, c.full_name;

-- 6. Behavior-based rule: large deposit followed by rapid withdrawal
WITH rapid_movement AS (
    SELECT
        c.customer_id,
        c.full_name,
        d.transaction_id AS deposit_transaction_id,
        w.transaction_id AS withdrawal_transaction_id,
        d.transaction_time AS deposit_time,
        w.transaction_time AS withdrawal_time,
        d.amount AS deposit_amount,
        w.amount AS withdrawal_amount,
        ROUND(100.0 * w.amount / d.amount, 2) AS withdrawal_percent
    FROM transactions AS d
    JOIN transactions AS w
        ON w.account_id = d.account_id
        AND w.transaction_time > d.transaction_time
        AND w.transaction_time <= d.transaction_time + INTERVAL '24 hours'
    JOIN accounts AS a ON a.account_id = d.account_id
    JOIN customers AS c ON c.customer_id = a.customer_id
    WHERE d.transaction_type = 'DEPOSIT'
      AND w.transaction_type = 'WITHDRAWAL'
      AND d.amount >= 5000
      AND w.amount >= d.amount * 0.80
)
SELECT
    customer_id,
    full_name,
    deposit_transaction_id,
    withdrawal_transaction_id,
    deposit_time,
    withdrawal_time,
    deposit_amount,
    withdrawal_amount,
    withdrawal_percent
FROM rapid_movement
ORDER BY withdrawal_percent DESC, full_name;

What Should Match?

• The file contains six numbered report blocks.
• The final block defines the rapid_movement CTE.
• The final projection contains the customer fields plus the paired transaction evidence.

Secret mission

Build a Customer Value Score

Turn separate balance, deposit, and repayment metrics into one transparent customer value score. You will rank all six customers while keeping every contribution visible for review.

Clean Up Your Resources

Clean Up Your Resources

Everything here stays on your Windows computer with no ongoing usage charges. Choose whether to keep the PostgreSQL database plus SQL files, close pgAdmin for now, or delete the project resources.

Resources you used:

• The local PostgreSQL database named banking_analytics.
• The saved schema.sql, seed.sql, analysis.sql, plus secret_mission.sql files.

Keep everything running

Keep this option if you want the finished banking report ready for another demonstration. Your local database plus SQL files stay in place.

• Keep banking_analytics with its six populated tables.
• Keep schema.sql, seed.sql, analysis.sql, plus secret_mission.sql for rebuilding or demonstrating the report.
• Use the saved queries in pgAdmin whenever you want to rerun the cash-flow, performance, repayment, risk, or customer-value results.

Pause - I'll come back to this later

Close pgAdmin to end the current session while preserving every project resource.

• Close the open Query Tool tabs.
• Close pgAdmin.
• Leave banking_analytics in place.
• Leave the four saved SQL files in their current locations.

Your local database plus SQL files remain available. This paused state has no usage charges.

Delete - I don't want to use this again

Use this option for a complete reset of the local database plus all four saved SQL files.

Database deletion is permanent. Stop here if you still want a demonstration copy.

• Close every Query Tool tab connected to banking_analytics.
• Close any other pgAdmin connection to banking_analytics.
• Select the postgres database in the pgAdmin object tree.
• Open Tools in the top menu.
• Select Query Tool.
• Remove the project database by running this command:

DROP DATABASE banking_analytics;

What Does This Command Do?

• This statement removes the local database named banking_analytics.
• Running it from postgres keeps the active session outside the target database.
• Every table inside banking_analytics disappears with the database.

• Check the Messages panel for successful command details.
• Return to the database list in pgAdmin.
• Confirm banking_analytics no longer appears.

Database Still in Use?

• Close any remaining Query Tool tab connected to banking_analytics.
• Retry the database deletion from the Query Tool connected to postgres.
• Ask for help with an active connection.

That clears the local database from your computer. The four saved SQL files remain until you remove them below.

The SQL files are stored separately from the database. Removing each file completes the local cleanup.

• Use the Windows file browser to visit each location where you saved the project SQL files.
• Remove schema.sql with the file browser's delete control.
• Remove seed.sql with the file browser's delete control.
• Remove analysis.sql with the file browser's delete control.
• Remove secret_mission.sql with the file browser's delete control.
• Search the saved locations for the four filenames.

You should find none of the project SQL files remaining. Your database plus project files are now cleared from local storage.

Nice Work!

Nice Work!

That is a wrap. You built a local banking analytics package in PostgreSQL that turns synthetic records into a reusable SQL report for customer value, branch performance, repayment risk, and suspicious behavior.

You've learned how to:

• Build a six-table relational banking model that enforces data integrity across branches, customers, accounts, transactions, loans, and EMI payments.
• Produce a reusable SQL analytics report covering monthly cash flow, ranked customer activity, branch performance, and EMI repayment.
• Replace an over-broad amount-only flag with a behavioral risk rule that isolates the two synthetic rapid cash-movement cases.
• Complete the Secret Mission by building a transparent customer value score that ranks all six customers using balances, deposits, and missed-EMI penalties.

Ready to quiz yourself?