SQL Tutorial › SQL Projects › Project: Banking Transactions

SQL Project: Banking Transactions

Q: How do you implement a money transfer in SQL?

In one transaction: lock both account rows (SELECT ... FOR UPDATE), check the balance, debit one account, credit the other, write both ledger entries, then COMMIT; on any error, ROLLBACK.

Q: How do you calculate a running balance in SQL?

Sum the signed amounts with a window: SUM(signed_amount) OVER (PARTITION BY account_id ORDER BY txn_time, txn_id).

ProjectProject 4 of 535 min read13 queries run on MySQL 8.4

A small bank keeps customers, accounts and every transaction in MySQL. Money is unforgiving: balances must always match the transaction history, a transfer must never debit one account without crediting the other, and unusual activity must be spotted. In this project you write the reports, the integrity checks, a transaction-safe transfer procedure and an alerting trigger, and you see ACID behaviour with real results.

In this lesson

Reconcile stored balances with the transaction ledger
Produce statements with running balances
Detect suspicious patterns with time-based window frames
Implement a safe transfer with a transaction, locking, a procedure and a trigger

The database

Table	One row per	Notes
`customers`	bank customer
`accounts`	account	`balance` has `CHECK (balance >= 0)`: no overdrafts
`transactions`	ledger entry	`txn_type`: DEPOSIT, WITHDRAWAL, TRANSFER_OUT, TRANSFER_IN, INTEREST; a transfer creates two entries that point at each other through `related_account`

customers · 6 rows

customer_id	full_name	city	since
1	Priya Sharma	Mumbai	2019-04-11
2	Rahul Verma	Delhi	2020-08-02
3	Fatima Sheikh	Hyderabad	2018-01-23
4	George Mathew	Kochi	2021-11-30
5	Lakshmi Rao	Chennai	2022-05-17
6	Arnav Gupta	Pune	2023-02-09

accounts · 9 rows

account_id	customer_id	account_type	opened_on	balance
101	1	Savings	2019-04-11	135000
102	1	Current	2021-06-01	280500
103	2	Savings	2020-08-02	129000
104	3	Savings	2018-01-23	7000
105	3	Fixed	2022-03-15	53875
106	4	Savings	2021-11-30	61000
107	5	Savings	2022-05-17	93000
108	6	Current	2023-02-09	144000
109	2	Current	2024-01-10	62000

transactions · first 12 of 102 rows

txn_id	account_id	txn_time	txn_type	amount	related_account
1	101	2025-01-01 10:00:00	DEPOSIT	50000	NULL
2	102	2025-01-01 11:00:00	DEPOSIT	200000	NULL
3	103	2025-01-01 12:00:00	DEPOSIT	50000	NULL
4	104	2025-01-01 13:00:00	DEPOSIT	50000	NULL
5	105	2025-01-01 14:00:00	DEPOSIT	50000	NULL
6	106	2025-01-01 15:00:00	DEPOSIT	120000	NULL
7	107	2025-01-01 16:00:00	DEPOSIT	75000	NULL
8	108	2025-01-01 17:00:00	DEPOSIT	200000	NULL
9	109	2025-01-01 18:00:00	DEPOSIT	50000	NULL
10	107	2025-01-03 13:47:00	TRANSFER_OUT	1000	101
11	101	2025-01-03 13:47:00	TRANSFER_IN	1000	107
12	106	2025-01-04 04:34:00	TRANSFER_OUT	3000	108

A transaction’s effect on the balance is +amount for DEPOSIT, TRANSFER_IN and INTEREST, and −amount for WITHDRAWAL and TRANSFER_OUT. Every query below uses that rule.

bank.sql

-- sqatools.in SQL project 4: banking transactions (generated by gen_projects.py)CREATE TABLE customers (  customer_id INT PRIMARY KEY,  full_name   VARCHAR(40) NOT NULL,  city        VARCHAR(30) NOT NULL,  since       DATE NOT NULL);CREATE TABLE accounts (  account_id   INT PRIMARY KEY,  customer_id  INT NOT NULL,  account_type VARCHAR(10) NOT NULL,  opened_on    DATE NOT NULL,  balance      INT NOT NULL CHECK (balance >= 0),  FOREIGN KEY (customer_id) REFERENCES customers (customer_id));CREATE TABLE transactions (  txn_id         INT PRIMARY KEY,  account_id     INT NOT NULL,  txn_time       DATETIME NOT NULL,  txn_type       VARCHAR(12) NOT NULL,  amount         INT NOT NULL CHECK (amount > 0),  related_account INT,  FOREIGN KEY (account_id) REFERENCES accounts (account_id));INSERT INTO customers VALUES(1, 'Priya Sharma', 'Mumbai', '2019-04-11'),(2, 'Rahul Verma', 'Delhi', '2020-08-02'),(3, 'Fatima Sheikh', 'Hyderabad', '2018-01-23'),(4, 'George Mathew', 'Kochi', '2021-11-30'),(5, 'Lakshmi Rao', 'Chennai', '2022-05-17'),(6, 'Arnav Gupta', 'Pune', '2023-02-09');INSERT INTO accounts VALUES(101, 1, 'Savings', '2019-04-11', 135000),(102, 1, 'Current', '2021-06-01', 280500),(103, 2, 'Savings', '2020-08-02', 129000),(104, 3, 'Savings', '2018-01-23', 7000),(105, 3, 'Fixed', '2022-03-15', 53875),(106, 4, 'Savings', '2021-11-30', 61000),(107, 5, 'Savings', '2022-05-17', 93000),(108, 6, 'Current', '2023-02-09', 144000),(109, 2, 'Current', '2024-01-10', 62000);INSERT INTO transactions VALUES(1, 101, '2025-01-01 10:00:00', 'DEPOSIT', 50000, NULL),(2, 102, '2025-01-01 11:00:00', 'DEPOSIT', 200000, NULL),(3, 103, '2025-01-01 12:00:00', 'DEPOSIT', 50000, NULL),(4, 104, '2025-01-01 13:00:00', 'DEPOSIT', 50000, NULL),(5, 105, '2025-01-01 14:00:00', 'DEPOSIT', 50000, NULL),(6, 106, '2025-01-01 15:00:00', 'DEPOSIT', 120000, NULL),(7, 107, '2025-01-01 16:00:00', 'DEPOSIT', 75000, NULL),(8, 108, '2025-01-01 17:00:00', 'DEPOSIT', 200000, NULL),(9, 109, '2025-01-01 18:00:00', 'DEPOSIT', 50000, NULL),(10, 107, '2025-01-03 13:47:00', 'TRANSFER_OUT', 1000, 101),(11, 101, '2025-01-03 13:47:00', 'TRANSFER_IN', 1000, 107),(12, 106, '2025-01-04 04:34:00', 'TRANSFER_OUT', 3000, 108),(13, 108, '2025-01-04 04:34:00', 'TRANSFER_IN', 3000, 106),(14, 106, '2025-01-04 19:39:00', 'WITHDRAWAL', 2000, NULL),(15, 103, '2025-01-06 01:26:00', 'WITHDRAWAL', 2000, NULL),(16, 106, '2025-01-07 02:06:00', 'DEPOSIT', 2000, NULL),(17, 104, '2025-01-07 22:06:00', 'TRANSFER_OUT', 12000, 109),(18, 109, '2025-01-07 22:06:00', 'TRANSFER_IN', 12000, 104),(19, 106, '2025-01-09 15:11:00', 'TRANSFER_OUT', 1000, 104),(20, 104, '2025-01-09 15:11:00', 'TRANSFER_IN', 1000, 106),(21, 108, '2025-01-11 02:16:00', 'WITHDRAWAL', 500, NULL),(22, 103, '2025-01-13 03:56:00', 'WITHDRAWAL', 2000, NULL),(23, 104, '2025-01-15 02:01:00', 'TRANSFER_OUT', 12000, 102),(24, 102, '2025-01-15 02:01:00', 'TRANSFER_IN', 12000, 104),(25, 103, '2025-01-15 20:26:00', 'DEPOSIT', 15000, NULL),(26, 101, '2025-01-16 12:06:00', 'DEPOSIT', 2000, NULL),(27, 104, '2025-01-17 20:06:00', 'TRANSFER_OUT', 1000, 101),(28, 101, '2025-01-17 20:06:00', 'TRANSFER_IN', 1000, 104),(29, 106, '2025-01-19 07:18:00', 'TRANSFER_OUT', 7500, 101),(30, 101, '2025-01-19 07:18:00', 'TRANSFER_IN', 7500, 106),(31, 106, '2025-01-20 21:30:00', 'WITHDRAWAL', 20000, NULL),(32, 101, '2025-01-21 12:55:00', 'WITHDRAWAL', 1000, NULL),(33, 106, '2025-01-23 03:55:00', 'WITHDRAWAL', 2000, NULL),(34, 103, '2025-01-24 09:55:00', 'DEPOSIT', 5000, NULL),(35, 104, '2025-01-25 03:00:00', 'TRANSFER_OUT', 7500, 102),(36, 102, '2025-01-25 03:00:00', 'TRANSFER_IN', 7500, 104),(37, 102, '2025-01-26 12:47:00', 'WITHDRAWAL', 500, NULL),(38, 102, '2025-01-26 21:47:00', 'WITHDRAWAL', 1000, NULL),(39, 108, '2025-01-29 01:27:00', 'TRANSFER_OUT', 12000, 106),(40, 106, '2025-01-29 01:27:00', 'TRANSFER_IN', 12000, 108),(41, 107, '2025-02-01 21:52:00', 'WITHDRAWAL', 5000, NULL),(42, 108, '2025-02-03 07:32:00', 'WITHDRAWAL', 10000, NULL),(43, 104, '2025-02-05 04:57:00', 'TRANSFER_OUT', 7500, 103),(44, 103, '2025-02-05 04:57:00', 'TRANSFER_IN', 7500, 104),(45, 103, '2025-02-05 13:22:00', 'WITHDRAWAL', 500, NULL),(46, 108, '2025-02-05 18:47:00', 'WITHDRAWAL', 2000, NULL),(47, 101, '2025-02-08 00:27:00', 'DEPOSIT', 2000, NULL),(48, 101, '2025-02-09 23:52:00', 'DEPOSIT', 60000, NULL),(49, 101, '2025-02-12 11:32:00', 'DEPOSIT', 60000, NULL),(50, 102, '2025-02-14 03:32:00', 'DEPOSIT', 2000, NULL),(51, 103, '2025-02-15 22:37:00', 'DEPOSIT', 25000, NULL),(52, 104, '2025-02-17 05:24:00', 'DEPOSIT', 2000, NULL),(53, 107, '2025-02-19 09:49:00', 'DEPOSIT', 5000, NULL),(54, 108, '2025-02-20 03:14:00', 'WITHDRAWAL', 500, NULL),(55, 106, '2025-02-20 22:19:00', 'TRANSFER_OUT', 7500, 104),(56, 104, '2025-02-20 22:19:00', 'TRANSFER_IN', 7500, 106),(57, 104, '2025-02-21 07:06:00', 'WITHDRAWAL', 10000, NULL),(58, 103, '2025-02-23 09:18:00', 'WITHDRAWAL', 20000, NULL),(59, 106, '2025-02-25 09:43:00', 'WITHDRAWAL', 500, NULL),(60, 104, '2025-02-26 14:48:00', 'TRANSFER_OUT', 7500, 102),(61, 102, '2025-02-26 14:48:00', 'TRANSFER_IN', 7500, 104),(62, 108, '2025-02-28 18:13:00', 'WITHDRAWAL', 20000, NULL),(63, 106, '2025-03-02 20:38:00', 'WITHDRAWAL', 1000, NULL),(64, 102, '2025-03-05 01:03:00', 'WITHDRAWAL', 2000, NULL),(65, 104, '2025-03-07 02:03:00', 'WITHDRAWAL', 1000, NULL),(66, 103, '2025-03-08 06:43:00', 'TRANSFER_OUT', 12000, 102),(67, 102, '2025-03-08 06:43:00', 'TRANSFER_IN', 12000, 103),(68, 106, '2025-03-08 21:55:00', 'TRANSFER_OUT', 3000, 108),(69, 108, '2025-03-08 21:55:00', 'TRANSFER_IN', 3000, 106),(70, 106, '2025-03-09 01:00:00', 'WITHDRAWAL', 10000, NULL),(71, 102, '2025-03-09 23:05:00', 'DEPOSIT', 60000, NULL),(72, 104, '2025-03-11 12:10:00', 'DEPOSIT', 2000, NULL),(73, 106, '2025-03-14 22:05:00', 'WITHDRAWAL', 10000, NULL),(74, 106, '2025-03-14 22:16:00', 'WITHDRAWAL', 10000, NULL),(75, 106, '2025-03-14 22:27:00', 'WITHDRAWAL', 9500, NULL),(76, 106, '2025-03-14 22:38:00', 'WITHDRAWAL', 10000, NULL),(77, 102, '2025-03-16 03:37:00', 'WITHDRAWAL', 20000, NULL),(78, 107, '2025-03-17 04:02:00', 'WITHDRAWAL', 1000, NULL),(79, 106, '2025-03-19 00:14:00', 'DEPOSIT', 25000, NULL),(80, 103, '2025-03-20 05:54:00', 'DEPOSIT', 60000, NULL),(81, 107, '2025-03-22 00:06:00', 'TRANSFER_OUT', 3000, 104),(82, 104, '2025-03-22 00:06:00', 'TRANSFER_IN', 3000, 107),(83, 102, '2025-03-22 10:31:00', 'WITHDRAWAL', 500, NULL),(84, 101, '2025-03-24 08:36:00', 'WITHDRAWAL', 20000, NULL),(85, 108, '2025-03-26 03:23:00', 'WITHDRAWAL', 10000, NULL),(86, 101, '2025-03-26 23:03:00', 'TRANSFER_OUT', 7500, 107),(87, 107, '2025-03-26 23:03:00', 'TRANSFER_IN', 7500, 101),(88, 107, '2025-03-29 00:50:00', 'TRANSFER_OUT', 7500, 102),(89, 102, '2025-03-29 00:50:00', 'TRANSFER_IN', 7500, 107),(90, 102, '2025-03-29 19:02:00', 'TRANSFER_OUT', 3000, 101),(91, 101, '2025-03-29 19:02:00', 'TRANSFER_IN', 3000, 102),(92, 102, '2025-03-31 05:27:00', 'TRANSFER_OUT', 1000, 108),(93, 108, '2025-03-31 05:27:00', 'TRANSFER_IN', 1000, 102),(94, 107, '2025-03-31 15:27:00', 'WITHDRAWAL', 2000, NULL),(95, 108, '2025-03-31 20:27:00', 'WITHDRAWAL', 10000, NULL),(96, 105, '2025-03-31 23:59:00', 'INTEREST', 3875, NULL),(97, 101, '2025-04-02 16:07:00', 'WITHDRAWAL', 20000, NULL),(98, 106, '2025-04-03 17:12:00', 'WITHDRAWAL', 1000, NULL),(99, 108, '2025-04-05 09:24:00', 'DEPOSIT', 2000, NULL),(100, 107, '2025-04-06 07:24:00', 'DEPOSIT', 25000, NULL),(101, 101, '2025-04-07 01:49:00', 'TRANSFER_OUT', 3000, 103),(102, 103, '2025-04-07 01:49:00', 'TRANSFER_IN', 3000, 101);

— sqatools.in SQL project 4: banking transactions (generated by gen_projects.py)
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  full_name   VARCHAR(40) NOT NULL,
  city        VARCHAR(30) NOT NULL,
  since       DATE NOT NULL
);
CREATE TABLE accounts (
  account_id   INT PRIMARY KEY,
  customer_id  INT NOT NULL,
  account_type VARCHAR(10) NOT NULL,
  opened_on    DATE NOT NULL,
  balance      INT NOT NULL CHECK (balance >= 0),
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
CREATE TABLE transactions (
  txn_id         INT PRIMARY KEY,
  account_id     INT NOT NULL,
  txn_time       DATETIME NOT NULL,
  txn_type       VARCHAR(12) NOT NULL,
  amount         INT NOT NULL CHECK (amount > 0),
  related_account INT,
  FOREIGN KEY (account_id) REFERENCES accounts (account_id)
);
INSERT INTO customers VALUES
(1, 'Priya Sharma', 'Mumbai', '2019-04-11'),
(2, 'Rahul Verma', 'Delhi', '2020-08-02'),
(3, 'Fatima Sheikh', 'Hyderabad', '2018-01-23'),
(4, 'George Mathew', 'Kochi', '2021-11-30'),
(5, 'Lakshmi Rao', 'Chennai', '2022-05-17'),
(6, 'Arnav Gupta', 'Pune', '2023-02-09');
INSERT INTO accounts VALUES
(101, 1, 'Savings', '2019-04-11', 135000),
(102, 1, 'Current', '2021-06-01', 280500),
(103, 2, 'Savings', '2020-08-02', 129000),
(104, 3, 'Savings', '2018-01-23', 7000),
(105, 3, 'Fixed', '2022-03-15', 53875),
(106, 4, 'Savings', '2021-11-30', 61000),
(107, 5, 'Savings', '2022-05-17', 93000),
(108, 6, 'Current', '2023-02-09', 144000),
(109, 2, 'Current', '2024-01-10', 62000);
INSERT INTO transactions VALUES
(1, 101, '2025-01-01 10:00:00', 'DEPOSIT', 50000, NULL),
(2, 102, '2025-01-01 11:00:00', 'DEPOSIT', 200000, NULL),
(3, 103, '2025-01-01 12:00:00', 'DEPOSIT', 50000, NULL),
(4, 104, '2025-01-01 13:00:00', 'DEPOSIT', 50000, NULL),
(5, 105, '2025-01-01 14:00:00', 'DEPOSIT', 50000, NULL),
(6, 106, '2025-01-01 15:00:00', 'DEPOSIT', 120000, NULL),
(7, 107, '2025-01-01 16:00:00', 'DEPOSIT', 75000, NULL),
(8, 108, '2025-01-01 17:00:00', 'DEPOSIT', 200000, NULL),
(9, 109, '2025-01-01 18:00:00', 'DEPOSIT', 50000, NULL),
(10, 107, '2025-01-03 13:47:00', 'TRANSFER_OUT', 1000, 101),
(11, 101, '2025-01-03 13:47:00', 'TRANSFER_IN', 1000, 107),
(12, 106, '2025-01-04 04:34:00', 'TRANSFER_OUT', 3000, 108),
(13, 108, '2025-01-04 04:34:00', 'TRANSFER_IN', 3000, 106),
(14, 106, '2025-01-04 19:39:00', 'WITHDRAWAL', 2000, NULL),
(15, 103, '2025-01-06 01:26:00', 'WITHDRAWAL', 2000, NULL),
(16, 106, '2025-01-07 02:06:00', 'DEPOSIT', 2000, NULL),
(17, 104, '2025-01-07 22:06:00', 'TRANSFER_OUT', 12000, 109),
(18, 109, '2025-01-07 22:06:00', 'TRANSFER_IN', 12000, 104),
(19, 106, '2025-01-09 15:11:00', 'TRANSFER_OUT', 1000, 104),
(20, 104, '2025-01-09 15:11:00', 'TRANSFER_IN', 1000, 106),
(21, 108, '2025-01-11 02:16:00', 'WITHDRAWAL', 500, NULL),
(22, 103, '2025-01-13 03:56:00', 'WITHDRAWAL', 2000, NULL),
(23, 104, '2025-01-15 02:01:00', 'TRANSFER_OUT', 12000, 102),
(24, 102, '2025-01-15 02:01:00', 'TRANSFER_IN', 12000, 104),
(25, 103, '2025-01-15 20:26:00', 'DEPOSIT', 15000, NULL),
(26, 101, '2025-01-16 12:06:00', 'DEPOSIT', 2000, NULL),
(27, 104, '2025-01-17 20:06:00', 'TRANSFER_OUT', 1000, 101),
(28, 101, '2025-01-17 20:06:00', 'TRANSFER_IN', 1000, 104),
(29, 106, '2025-01-19 07:18:00', 'TRANSFER_OUT', 7500, 101),
(30, 101, '2025-01-19 07:18:00', 'TRANSFER_IN', 7500, 106),
(31, 106, '2025-01-20 21:30:00', 'WITHDRAWAL', 20000, NULL),
(32, 101, '2025-01-21 12:55:00', 'WITHDRAWAL', 1000, NULL),
(33, 106, '2025-01-23 03:55:00', 'WITHDRAWAL', 2000, NULL),
(34, 103, '2025-01-24 09:55:00', 'DEPOSIT', 5000, NULL),
(35, 104, '2025-01-25 03:00:00', 'TRANSFER_OUT', 7500, 102),
(36, 102, '2025-01-25 03:00:00', 'TRANSFER_IN', 7500, 104),
(37, 102, '2025-01-26 12:47:00', 'WITHDRAWAL', 500, NULL),
(38, 102, '2025-01-26 21:47:00', 'WITHDRAWAL', 1000, NULL),
(39, 108, '2025-01-29 01:27:00', 'TRANSFER_OUT', 12000, 106),
(40, 106, '2025-01-29 01:27:00', 'TRANSFER_IN', 12000, 108),
(41, 107, '2025-02-01 21:52:00', 'WITHDRAWAL', 5000, NULL),
(42, 108, '2025-02-03 07:32:00', 'WITHDRAWAL', 10000, NULL),
(43, 104, '2025-02-05 04:57:00', 'TRANSFER_OUT', 7500, 103),
(44, 103, '2025-02-05 04:57:00', 'TRANSFER_IN', 7500, 104),
(45, 103, '2025-02-05 13:22:00', 'WITHDRAWAL', 500, NULL),
(46, 108, '2025-02-05 18:47:00', 'WITHDRAWAL', 2000, NULL),
(47, 101, '2025-02-08 00:27:00', 'DEPOSIT', 2000, NULL),
(48, 101, '2025-02-09 23:52:00', 'DEPOSIT', 60000, NULL),
(49, 101, '2025-02-12 11:32:00', 'DEPOSIT', 60000, NULL),
(50, 102, '2025-02-14 03:32:00', 'DEPOSIT', 2000, NULL),
(51, 103, '2025-02-15 22:37:00', 'DEPOSIT', 25000, NULL),
(52, 104, '2025-02-17 05:24:00', 'DEPOSIT', 2000, NULL),
(53, 107, '2025-02-19 09:49:00', 'DEPOSIT', 5000, NULL),
(54, 108, '2025-02-20 03:14:00', 'WITHDRAWAL', 500, NULL),
(55, 106, '2025-02-20 22:19:00', 'TRANSFER_OUT', 7500, 104),
(56, 104, '2025-02-20 22:19:00', 'TRANSFER_IN', 7500, 106),
(57, 104, '2025-02-21 07:06:00', 'WITHDRAWAL', 10000, NULL),
(58, 103, '2025-02-23 09:18:00', 'WITHDRAWAL', 20000, NULL),
(59, 106, '2025-02-25 09:43:00', 'WITHDRAWAL', 500, NULL),
(60, 104, '2025-02-26 14:48:00', 'TRANSFER_OUT', 7500, 102),
(61, 102, '2025-02-26 14:48:00', 'TRANSFER_IN', 7500, 104),
(62, 108, '2025-02-28 18:13:00', 'WITHDRAWAL', 20000, NULL),
(63, 106, '2025-03-02 20:38:00', 'WITHDRAWAL', 1000, NULL),
(64, 102, '2025-03-05 01:03:00', 'WITHDRAWAL', 2000, NULL),
(65, 104, '2025-03-07 02:03:00', 'WITHDRAWAL', 1000, NULL),
(66, 103, '2025-03-08 06:43:00', 'TRANSFER_OUT', 12000, 102),
(67, 102, '2025-03-08 06:43:00', 'TRANSFER_IN', 12000, 103),
(68, 106, '2025-03-08 21:55:00', 'TRANSFER_OUT', 3000, 108),
(69, 108, '2025-03-08 21:55:00', 'TRANSFER_IN', 3000, 106),
(70, 106, '2025-03-09 01:00:00', 'WITHDRAWAL', 10000, NULL),
(71, 102, '2025-03-09 23:05:00', 'DEPOSIT', 60000, NULL),
(72, 104, '2025-03-11 12:10:00', 'DEPOSIT', 2000, NULL),
(73, 106, '2025-03-14 22:05:00', 'WITHDRAWAL', 10000, NULL),
(74, 106, '2025-03-14 22:16:00', 'WITHDRAWAL', 10000, NULL),
(75, 106, '2025-03-14 22:27:00', 'WITHDRAWAL', 9500, NULL),
(76, 106, '2025-03-14 22:38:00', 'WITHDRAWAL', 10000, NULL),
(77, 102, '2025-03-16 03:37:00', 'WITHDRAWAL', 20000, NULL),
(78, 107, '2025-03-17 04:02:00', 'WITHDRAWAL', 1000, NULL),
(79, 106, '2025-03-19 00:14:00', 'DEPOSIT', 25000, NULL),
(80, 103, '2025-03-20 05:54:00', 'DEPOSIT', 60000, NULL),
(81, 107, '2025-03-22 00:06:00', 'TRANSFER_OUT', 3000, 104),
(82, 104, '2025-03-22 00:06:00', 'TRANSFER_IN', 3000, 107),
(83, 102, '2025-03-22 10:31:00', 'WITHDRAWAL', 500, NULL),
(84, 101, '2025-03-24 08:36:00', 'WITHDRAWAL', 20000, NULL),
(85, 108, '2025-03-26 03:23:00', 'WITHDRAWAL', 10000, NULL),
(86, 101, '2025-03-26 23:03:00', 'TRANSFER_OUT', 7500, 107),
(87, 107, '2025-03-26 23:03:00', 'TRANSFER_IN', 7500, 101),
(88, 107, '2025-03-29 00:50:00', 'TRANSFER_OUT', 7500, 102),
(89, 102, '2025-03-29 00:50:00', 'TRANSFER_IN', 7500, 107),
(90, 102, '2025-03-29 19:02:00', 'TRANSFER_OUT', 3000, 101),
(91, 101, '2025-03-29 19:02:00', 'TRANSFER_IN', 3000, 102),
(92, 102, '2025-03-31 05:27:00', 'TRANSFER_OUT', 1000, 108),
(93, 108, '2025-03-31 05:27:00', 'TRANSFER_IN', 1000, 102),
(94, 107, '2025-03-31 15:27:00', 'WITHDRAWAL', 2000, NULL),
(95, 108, '2025-03-31 20:27:00', 'WITHDRAWAL', 10000, NULL),
(96, 105, '2025-03-31 23:59:00', 'INTEREST', 3875, NULL),
(97, 101, '2025-04-02 16:07:00', 'WITHDRAWAL', 20000, NULL),
(98, 106, '2025-04-03 17:12:00', 'WITHDRAWAL', 1000, NULL),
(99, 108, '2025-04-05 09:24:00', 'DEPOSIT', 2000, NULL),
(100, 107, '2025-04-06 07:24:00', 'DEPOSIT', 25000, NULL),
(101, 101, '2025-04-07 01:49:00', 'TRANSFER_OUT', 3000, 103),
(102, 103, '2025-04-07 01:49:00', 'TRANSFER_IN', 3000, 101);

Task 1: Customer holdings

SQL

SELECT c.customer_id, c.full_name,       COUNT(a.account_id) AS accounts,       GROUP_CONCAT(a.account_type ORDER BY a.account_id) AS account_types,       SUM(a.balance) AS total_balanceFROM customers cJOIN accounts a ON a.customer_id = c.customer_idGROUP BY c.customer_id, c.full_nameORDER BY total_balance DESC;

ResultMySQL 8.4

customer_id	full_name	accounts	account_types	total_balance
1	Priya Sharma	2	Savings,Current	415500
2	Rahul Verma	2	Savings,Current	191000
6	Arnav Gupta	1	Current	144000
5	Lakshmi Rao	1	Savings	93000
4	George Mathew	1	Savings	61000
3	Fatima Sheikh	2	Savings,Fixed	60875

6 rows in set

Task 2: Reconcile balances with the ledger

An integrity check that auditors and testers run: does each stored balance equal the sum of its transactions?

SQL

SELECT a.account_id, a.balance AS stored_balance,       SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE -t.amount END) AS ledger_balance,       a.balance - SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE -t.amount END) AS differenceFROM accounts aJOIN transactions t ON t.account_id = a.account_idGROUP BY a.account_id, a.balanceORDER BY a.account_id;

ResultMySQL 8.4

account_id	stored_balance	ledger_balance
101	135000	135000
102	280500	280500
103	129000	129000
104	7000	7000
105	53875	53875
106	61000	61000
107	93000	93000
108	144000	144000
109	62000	62000

9 rows in set

How this query works, step by step

The CASE turns each entry into a signed amount.
SUM per account rebuilds the balance from the ledger.
difference must be 0 for every account. Any other value means a bug or tampering, so this query belongs in an automated data-quality test.

Both legs of every transfer must exist and match:

SQL

SELECT o.txn_id AS out_txn, o.account_id AS from_acc, o.related_account AS to_acc, o.amount, i.txn_id AS in_txnFROM transactions oLEFT JOIN transactions i       ON i.txn_type = 'TRANSFER_IN' AND i.account_id = o.related_account      AND i.related_account = o.account_id AND i.amount = o.amount AND i.txn_time = o.txn_timeWHERE o.txn_type = 'TRANSFER_OUT' AND i.txn_id IS NULL;

ResultMySQL 8.4

Empty set

An Empty set means every outgoing transfer has its matching incoming entry.

Task 3: Account statement with running balance

SQL

SELECT txn_time, txn_type, amount,       SUM(CASE WHEN txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN amount ELSE -amount END)         OVER (ORDER BY txn_time, txn_id) AS running_balanceFROM transactionsWHERE account_id = 103ORDER BY txn_time, txn_id;

ResultMySQL 8.4

txn_time	txn_type	amount	running_balance
2025-01-01 12:00:00	DEPOSIT	50000	50000
2025-01-06 01:26:00	WITHDRAWAL	2000	48000
2025-01-13 03:56:00	WITHDRAWAL	2000	46000
2025-01-15 20:26:00	DEPOSIT	15000	61000
2025-01-24 09:55:00	DEPOSIT	5000	66000
2025-02-05 04:57:00	TRANSFER_IN	7500	73500
2025-02-05 13:22:00	WITHDRAWAL	500	73000
2025-02-15 22:37:00	DEPOSIT	25000	98000
2025-02-23 09:18:00	WITHDRAWAL	20000	78000
2025-03-08 06:43:00	TRANSFER_OUT	12000	66000
2025-03-20 05:54:00	DEPOSIT	60000	126000
2025-04-07 01:49:00	TRANSFER_IN	3000	129000

12 rows in set

How this query works, step by step

The signed amount is summed cumulatively in time order (Running Totals).
txn_id breaks ties between entries with the same timestamp, so the order is deterministic.
The last running balance equals the account’s stored balance.

Task 4: Monthly flows by account type

MySQL

SELECT DATE_FORMAT(t.txn_time, '%Y-%m') AS month, a.account_type,       SUM(CASE WHEN t.txn_type IN ('DEPOSIT', 'TRANSFER_IN', 'INTEREST') THEN t.amount ELSE 0 END) AS money_in,       SUM(CASE WHEN t.txn_type IN ('WITHDRAWAL', 'TRANSFER_OUT') THEN t.amount ELSE 0 END) AS money_outFROM transactions tJOIN accounts a ON a.account_id = t.account_idGROUP BY month, a.account_typeORDER BY month, a.account_type;

ResultMySQL 8.4

month	account_type	money_in	money_out
2025-01	Current	484500	14000
2025-01	Fixed	50000	0
2025-01	Savings	391500	74000
2025-02	Current	9500	32500
2025-02	Savings	169000	58500
2025-03	Current	83500	46500
2025-03	Fixed	3875	0
2025-03	Savings	100500	107500
2025-04	Current	2000	0
2025-04	Savings	28000	24000

10 rows in set

Task 5: Large transactions

SQL

SELECT t.txn_id, t.txn_time, c.full_name, t.account_id, t.txn_type, t.amountFROM transactions tJOIN accounts a  ON a.account_id = t.account_idJOIN customers c ON c.customer_id = a.customer_idWHERE t.amount >= 50000 AND t.txn_type <> 'TRANSFER_IN'ORDER BY t.amount DESC, t.txn_time;

ResultMySQL 8.4

txn_id	txn_time	full_name	account_id	txn_type	amount
2	2025-01-01 11:00:00	Priya Sharma	102	DEPOSIT	200000
8	2025-01-01 17:00:00	Arnav Gupta	108	DEPOSIT	200000
6	2025-01-01 15:00:00	George Mathew	106	DEPOSIT	120000
7	2025-01-01 16:00:00	Lakshmi Rao	107	DEPOSIT	75000
48	2025-02-09 23:52:00	Priya Sharma	101	DEPOSIT	60000
49	2025-02-12 11:32:00	Priya Sharma	101	DEPOSIT	60000
71	2025-03-09 23:05:00	Priya Sharma	102	DEPOSIT	60000
80	2025-03-20 05:54:00	Rahul Verma	103	DEPOSIT	60000
1	2025-01-01 10:00:00	Priya Sharma	101	DEPOSIT	50000
3	2025-01-01 12:00:00	Rahul Verma	103	DEPOSIT	50000
4	2025-01-01 13:00:00	Fatima Sheikh	104	DEPOSIT	50000
5	2025-01-01 14:00:00	Fatima Sheikh	105	DEPOSIT	50000
9	2025-01-01 18:00:00	Rahul Verma	109	DEPOSIT	50000

13 rows in set

Task 6: Fraud pattern: many withdrawals in one hour

Flag every withdrawal that is the 3rd (or later) withdrawal on the same account within 60 minutes. A time-based window frame counts the rows in the preceding hour:

MySQL

WITH w AS (  SELECT txn_id, account_id, txn_time, amount,         COUNT(*) OVER (PARTITION BY account_id ORDER BY txn_time                        RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW) AS withdrawals_last_hour,         SUM(amount) OVER (PARTITION BY account_id ORDER BY txn_time                        RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW) AS amount_last_hour  FROM transactions  WHERE txn_type = 'WITHDRAWAL')SELECT * FROM wWHERE withdrawals_last_hour >= 3ORDER BY account_id, txn_time;

ResultMySQL 8.4

txn_id	account_id	txn_time	amount	withdrawals_last_hour	amount_last_hour
75	106	2025-03-14 22:27:00	9500	3	29500
76	106	2025-03-14 22:38:00	10000	4	39500

2 rows in set

How this query works, step by step

Only withdrawals are considered, per account, in time order.
RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW includes every withdrawal from the hour up to the current one, based on the timestamp value, not on a number of rows.
Rows with 3 or more withdrawals in that hour are flagged. Account 106 shows a burst of ATM withdrawals late one night.

Task 7: Dormant accounts

Accounts with no activity in the 60 days before 30 April 2025:

MySQL

SELECT a.account_id, c.full_name, a.account_type, a.balance,       MAX(t.txn_time) AS last_activityFROM accounts aJOIN customers c ON c.customer_id = a.customer_idLEFT JOIN transactions t ON t.account_id = a.account_idGROUP BY a.account_id, c.full_name, a.account_type, a.balanceHAVING MAX(t.txn_time) < '2025-04-30' - INTERVAL 60 DAY OR MAX(t.txn_time) IS NULLORDER BY last_activity;

ResultMySQL 8.4

account_id	full_name	account_type	balance	last_activity
109	Rahul Verma	Current	62000	2025-01-07 22:06:00

1 row in set

Task 8: Who sends money to whom

SQL

SELECT cf.full_name AS sender, ct.full_name AS receiver,       COUNT(*) AS transfers, SUM(t.amount) AS total_sentFROM transactions tJOIN accounts af  ON af.account_id = t.account_idJOIN customers cf ON cf.customer_id = af.customer_idJOIN accounts at2 ON at2.account_id = t.related_accountJOIN customers ct ON ct.customer_id = at2.customer_idWHERE t.txn_type = 'TRANSFER_OUT'GROUP BY sender, receiverORDER BY total_sent DESCLIMIT 6;

ResultMySQL 8.4

sender	receiver	transfers	total_sent
Fatima Sheikh	Priya Sharma	4	28000
Fatima Sheikh	Rahul Verma	2	19500
Arnav Gupta	George Mathew	1	12000
Rahul Verma	Priya Sharma	1	12000
George Mathew	Fatima Sheikh	2	8500
Lakshmi Rao	Priya Sharma	2	8500

6 rows in set

Transfers between a customer’s own accounts appear with the same name on both sides.

Task 9: A safe money transfer (ACID)

A transfer touches two balances and writes two ledger entries. It must be atomic: all four changes or none. The procedure below uses a transaction, row locks and an error handler:

MySQL

DELIMITER //CREATE PROCEDURE transfer_funds(IN p_from INT, IN p_to INT, IN p_amount INT, IN p_time DATETIME)BEGIN  DECLARE v_balance INT;  DECLARE v_found INT;  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;   IF p_amount <= 0 OR p_from = p_to THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transfer';  END IF;   START TRANSACTION;  -- lock both account rows; InnoDB takes them in primary-key order, so opposite transfers cannot deadlock  SELECT COUNT(*) INTO v_found FROM accounts WHERE account_id IN (p_from, p_to) FOR UPDATE;  IF v_found <> 2 THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown account';  END IF;  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_from;  IF v_balance < p_amount THEN    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';  END IF;   UPDATE accounts SET balance = balance - p_amount WHERE account_id = p_from;  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)  SELECT MAX(txn_id) + 1, p_from, p_time, 'TRANSFER_OUT', p_amount, p_to FROM transactions;  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)  SELECT MAX(txn_id) + 1, p_to, p_time, 'TRANSFER_IN', p_amount, p_from FROM transactions;  COMMIT;END //DELIMITER ; SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);CALL transfer_funds(101, 107, 25000, '2025-04-20 11:00:00');SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);

DELIMITER //
CREATE PROCEDURE transfer_funds(IN p_from INT, IN p_to INT, IN p_amount INT, IN p_time DATETIME)
BEGIN
  DECLARE v_balance INT;
  DECLARE v_found INT;
  DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

IF p_amount <= 0 OR p_from = p_to THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transfer';
  END IF;

START TRANSACTION;
  — lock both account rows; InnoDB takes them in primary-key order, so opposite transfers cannot deadlock
  SELECT COUNT(*) INTO v_found FROM accounts WHERE account_id IN (p_from, p_to) FOR UPDATE;
  IF v_found <> 2 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Unknown account';
  END IF;
  SELECT balance INTO v_balance FROM accounts WHERE account_id = p_from;
  IF v_balance < p_amount THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
  END IF;

UPDATE accounts SET balance = balance – p_amount WHERE account_id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE account_id = p_to;
  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)
  SELECT MAX(txn_id) + 1, p_from, p_time, 'TRANSFER_OUT', p_amount, p_to FROM transactions;
  INSERT INTO transactions (txn_id, account_id, txn_time, txn_type, amount, related_account)
  SELECT MAX(txn_id) + 1, p_to, p_time, 'TRANSFER_IN', p_amount, p_from FROM transactions;
  COMMIT;
END //
DELIMITER ;

SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);
CALL transfer_funds(101, 107, 25000, '2025-04-20 11:00:00');
SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107);

ResultMySQL 8.4

mysql> CREATE PROCEDURE transfer_funds(IN p_from INT, IN p_to INT, IN p_amoun…

Query OK, 0 rows affected

mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107)

account_id	balance
101	135000
107	93000

2 rows in set

mysql> CALL transfer_funds(101, 107, 25000, '2025-04-20 11:00:00')

Query OK, 0 rows affected

mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 107)

account_id	balance
101	110000
107	118000

2 rows in set

How this query works, step by step

Atomicity: both UPDATEs and both INSERTs run inside one transaction and are committed together.
Consistency: the balance check and the table’s CHECK (balance >= 0) keep every balance valid.
Isolation: FOR UPDATE locks the rows, so a parallel transfer from the same account waits instead of spending the same money twice.
Durability: after COMMIT, InnoDB guarantees the transfer survives a crash.
Account 101 loses 25,000 and 107 gains 25,000; the total money in the bank is unchanged.

A transfer larger than the balance fails, and nothing changes:

MySQL

CALL transfer_funds(108, 101, 9999999, '2025-04-20 11:05:00');

ResultMySQL 8.4

ERROR 1644 (45000): Insufficient funds

MySQL

SELECT account_id, balance FROM accounts WHERE account_id IN (101, 108);SELECT COUNT(*) AS ledger_entries_at_1105 FROM transactions WHERE txn_time = '2025-04-20 11:05:00';

ResultMySQL 8.4

mysql> SELECT account_id, balance FROM accounts WHERE account_id IN (101, 108)

account_id	balance
101	110000
108	144000

2 rows in set

mysql> SELECT COUNT(*) AS ledger_entries_at_1105 FROM transactions WHERE txn_…

ledger_entries_at_1105
0

1 row in set

Task 10: An alerting trigger

Write every withdrawal of 20,000 or more to an alerts table automatically, whichever application makes it:

MySQL

CREATE TABLE alerts (  alert_id   INT AUTO_INCREMENT PRIMARY KEY,  txn_id     INT NOT NULL,  account_id INT NOT NULL,  amount     INT NOT NULL,  reason     VARCHAR(40) NOT NULL); CREATE TRIGGER trg_large_withdrawalAFTER INSERT ON transactionsFOR EACH ROWINSERT INTO alerts (txn_id, account_id, amount, reason)SELECT NEW.txn_id, NEW.account_id, NEW.amount, 'Large withdrawal'FROM DUALWHERE NEW.txn_type = 'WITHDRAWAL' AND NEW.amount >= 20000; INSERT INTO transactions VALUES (500, 102, '2025-04-21 09:15:00', 'WITHDRAWAL', 30000, NULL);INSERT INTO transactions VALUES (501, 102, '2025-04-21 09:20:00', 'WITHDRAWAL', 2000, NULL);SELECT * FROM alerts;

ResultMySQL 8.4

mysql> CREATE TABLE alerts (

Query OK, 0 rows affected

mysql> CREATE TRIGGER trg_large_withdrawal

Query OK, 0 rows affected

mysql> INSERT INTO transactions VALUES (500, 102, '2025-04-21 09:15:00', 'WIT…

Query OK, 1 row affected

mysql> INSERT INTO transactions VALUES (501, 102, '2025-04-21 09:20:00', 'WIT…

Query OK, 1 row affected

mysql> SELECT * FROM alerts

alert_id	txn_id	account_id	amount	reason
1	500	102	30000	Large withdrawal

1 row in set

How this query works, step by step

The trigger runs after every inserted transaction row (Triggers).
The INSERT ... SELECT ... WHERE writes an alert only when the condition holds, without needing IF and BEGIN ... END.
The 30,000 withdrawal creates an alert; the 2,000 one does not.

Your turn

Exercise: Biggest single deposit per customer

For each customer, show their largest single deposit.

Expected resultMySQL 8.4

full_name	largest_deposit
Priya Sharma	200000
Arnav Gupta	200000
George Mathew	120000
Lakshmi Rao	75000
Rahul Verma	60000
Fatima Sheikh	50000

6 rows in set

Hint 1

Join transactions to accounts and customers, filter txn_type = 'DEPOSIT', MAX(amount) per customer.

SQL

SELECT c.full_name, MAX(t.amount) AS largest_depositFROM transactions tJOIN accounts a  ON a.account_id = t.account_idJOIN customers c ON c.customer_id = a.customer_idWHERE t.txn_type = 'DEPOSIT'GROUP BY c.customer_id, c.full_nameORDER BY largest_deposit DESC;

ResultMySQL 8.4

full_name	largest_deposit
Priya Sharma	200000
Arnav Gupta	200000
George Mathew	120000
Lakshmi Rao	75000
Rahul Verma	60000
Fatima Sheikh	50000

6 rows in set

Exercise: Daily withdrawal totals above 20,000

List each account and day where withdrawals added up to more than 20,000.

Expected resultMySQL 8.4

account_id	day	withdrawn	withdrawals
106	2025-03-14	39500	4

1 row in set

Hint 1

Group by account_id and DATE(txn_time), filter with HAVING.

SQL

SELECT account_id, DATE(txn_time) AS day, SUM(amount) AS withdrawn, COUNT(*) AS withdrawalsFROM transactionsWHERE txn_type = 'WITHDRAWAL'GROUP BY account_id, dayHAVING SUM(amount) > 20000ORDER BY withdrawn DESC;

ResultMySQL 8.4

account_id	day	withdrawn	withdrawals
106	2025-03-14	39500	4

1 row in set

What you practised

Task	SQL skills
Reconciliation, transfer legs	signed `CASE`, `SUM`, anti-join
Statement, monthly flows	running `SUM() OVER`, conditional aggregation
Fraud pattern	`RANGE BETWEEN INTERVAL ... PRECEDING` window frame
Dormant accounts	`LEFT JOIN`, `HAVING MAX(...)`, date arithmetic
Transfer, alerts	transaction, `FOR UPDATE`, `SIGNAL`, procedure, trigger, ACID

Project FAQ

How do you implement a money transfer in SQL?

In one transaction: lock both account rows (SELECT ... FOR UPDATE), check the balance, debit one account, credit the other, write both ledger entries, then COMMIT; on any error, ROLLBACK.

How do you calculate a running balance in SQL?

Sum the signed amounts with a window: SUM(signed_amount) OVER (PARTITION BY account_id ORDER BY txn_time, txn_id).

How can SQL detect suspicious transactions?

With rules expressed as queries, such as amounts above a threshold, or several withdrawals in a short time using a window frame like RANGE BETWEEN INTERVAL 60 MINUTE PRECEDING AND CURRENT ROW.