ASAmol Shukla

17. Real life: retail bank

Step 1 of 4 · Lesson

Lesson 17 of 21

Real life database practice: retail banking (50 SQL problems)

Practise 50 SQL problems on a 2,000-transaction bank database — customer segments, balances, channels and monthly reporting, checked in your browser.

200 min 3 min read 50 problems · 0 solved Retail Bank (2,000 transactions)

In short

What is in the retail bank practice database?

400 customers, around 600 accounts and 2,000 transactions in four branches and six cities. Customers have a segment and credit score, accounts have a branch, type and balance, and transactions carry a type, channel and amount. The database is already selected in every practice problem.

By the end of this lesson you can

  • Answer branch and segment questions from 2,000 transactions
  • Aggregate money without being fooled by zero rows or NULLs
  • Report per month and per channel with GROUP BY
  • Use window functions for rankings, running totals and month-over-month change

The database you are practising on

Retail Bank is a fictional bank with 400 customers, about 600 accounts and 2,000 transactions. Each customer belongs to a retail, premium or business segment; each account sits in a branch; each transaction belongs to one account and carries a channel such as atm, app, netbanking or branch.

The whole set is fictional but shaped like the real thing: keys, foreign keys, dates, statuses and 2,000 rows in the busiest table. You do not import anything to start — practice mode loads the same rows into your browser. When you want the same data in MySQL Workbench, download the script (3 tables, 20 columns) and run it there.

What one row means

Read the grain before you write a join. Joining two tables without knowing what one row of each means is the single most common cause of doubled totals.

Table One row is Columns you will use
customers one customer customer_id, customer_name, city, segment, join_date, credit_score, email
accounts one bank account account_id, customer_id, branch, account_type, open_date, balance
transactions one money movement txn_id, account_id, txn_date, txn_type, channel, amount, balance_after

Your database is already selected

Every problem below runs against this database only. The code arena preselects it and does not show a database dropdown, so you never see MySQL's ERROR 1046: No database selected here.

You write one query, press Check answer, and it is compared row by row with the reference answer — same rows, same columns, any valid query wins. Use Hint first, Compare with the reference answer once you have tried, and Reset database if you changed rows while experimenting.

Common mistakes

Wrong

Joining transactions to accounts and then summing amount to get a branch total

Fix

State the grain first: one transaction row stays one row. Aggregate before joining when the other side has many rows.

Why: Every transaction already belongs to exactly one account, so this join is safe — but the same pattern on a one-to-many table doubles the sum.

Wrong

WHERE SUM(amount) > 100000

Fix

Use HAVING SUM(amount) > 100000 after GROUP BY.

Why: An aggregate cannot run in WHERE; MySQL raises error 1111.

Wrong

Filtering dates with BETWEEN '2025-01-01' AND '2025-12-31'

Fix

Use >= '2025-01-01' AND < '2026-01-01', as the reference answers do.

Why: It breaks the moment a DATETIME with a time part is used; the last day is partly missed.

Check yourself

  1. Can you say in one sentence what one row of customers means?
  2. Can you write a query that returns exactly one number from a 2,000-row table without scrolling the result?
  3. Can you explain why two of your joins could double a total, and how you avoided it?

Answer out loud or in your notebook. If one is hard, reread that section before practising.

Frequently asked questions

Do I need to install MySQL and import this data?

No. The code arena loads the bank database in your browser and preselects it for every problem. Download the .sql script when you want to run the same queries in Workbench.

Are the answers checked automatically?

Yes. Your query and the reference answer run on fresh copies of the same rows and the results are compared row by row, so any correct query is accepted.

Is the bank data real?

No. It is fictional and generated from a fixed seed so every learner sees the same rows and the same expected answers.

Quick quiz — 3 questions

Quick Check: Real life: retail bank

1 / 3
Customers and accounts are one-to-many. What must you do before summing account balances per customer?

Answers — Real life: retail bank

  1. Easy. List customers in Pune with a credit score above 750. Target: 8 rows · customer_id, customer_name, credit_score · (85, Ishaan Bhat, 828); (168, Ishaan Gupta, 851) …

    SELECT customer_id, customer_name, credit_score FROM customers WHERE city = 'Pune' AND credit_score > 750 ORDER BY customer_id LIMIT 20;

  2. Easy. Show the 20 accounts with the highest balance. Target: 20 rows · account_id, account_type, balance · (539, savings, 799500.97); (471, savings, 798300.66) …

    SELECT account_id, account_type, balance FROM accounts ORDER BY balance DESC, account_id LIMIT 20;

  3. Easy. Show the 20 most recent deposit transactions. Target: 20 rows · txn_id, txn_date, amount · (1783, 2025-12-30, 26885.47); (1737, 2025-12-29, 2157.67) …

    SELECT txn_id, txn_date, amount FROM transactions WHERE txn_type = 'deposit' ORDER BY txn_date DESC, txn_id DESC LIMIT 20;

  4. Easy. List each transaction channel once. Target: 4 rows · channel · (app); (atm) …

    SELECT DISTINCT channel FROM transactions ORDER BY channel;

  5. Easy. List savings accounts opened during 2024. Target: 20 rows · account_id, customer_id, open_date · (34, 25, 2024-11-13); (58, 40, 2024-02-02) …

    SELECT account_id, customer_id, open_date FROM accounts WHERE account_type = 'savings' AND open_date >= '2024-01-01' AND open_date < '2025-01-01' ORDER BY account_id LIMIT 20;

  6. Easy. List premium customers, alphabetically by name. Target: 98 rows · customer_id, customer_name, city · (70, Aarav Joshi, Pune); (151, Aarav Reddy, Ludhiana) …

    SELECT customer_id, customer_name, city FROM customers WHERE segment = 'premium' ORDER BY customer_name, customer_id;

  7. Easy. Show the 20 largest transactions. Target: 20 rows · txn_id, txn_date, amount · (30, 2024-03-27, 40000); (709, 2024-11-08, 39981.46) …

    SELECT txn_id, txn_date, amount FROM transactions ORDER BY amount DESC, txn_id LIMIT 20;

  8. Easy. List the accounts held at the Pune Camp branch. Target: 20 rows · account_id, customer_id, account_type · (1, 1, current); (5, 3, current) …

    SELECT account_id, customer_id, account_type FROM accounts WHERE branch = 'Pune Camp' ORDER BY account_id LIMIT 20;

  9. Easy. Count all customers. Target: 1 row · customers · (400)

    SELECT COUNT(*) AS customers FROM customers;

  10. Easy. Show type and balance for the first 20 accounts. Target: 20 rows · account_id, account_type, balance · (1, current, 220500.93); (2, savings, 171000.18) …

    SELECT account_id, account_type, balance FROM accounts ORDER BY account_id LIMIT 20;

  11. Medium. List customers with a credit score between 600 and 700. Target: 20 rows · customer_id, customer_name, credit_score · (5, Pranav Nair, 680); (9, Zara Nair, 663) …

    SELECT customer_id, customer_name, credit_score FROM customers WHERE credit_score BETWEEN 600 AND 700 ORDER BY customer_id LIMIT 20;

  12. Medium. List current and credit accounts. Target: 20 rows · account_id, account_type, balance · (1, current, 220500.93); (5, current, 425300.98) …

    SELECT account_id, account_type, balance FROM accounts WHERE account_type IN ('current', 'credit') ORDER BY account_id LIMIT 20;

  13. Medium. List customers whose name starts with S. Target: 20 rows · customer_id, customer_name · (34, Saanvi Das); (40, Simran Gill) …

    SELECT customer_id, customer_name FROM customers WHERE customer_name LIKE 'S%' ORDER BY customer_id LIMIT 20;

  14. Medium. List transactions from October to December 2024. Target: 20 rows · txn_id, txn_date, amount · (643, 2024-10-01, 37512.01); (1935, 2024-10-01, 36124.83) …

    SELECT txn_id, txn_date, amount FROM transactions WHERE txn_date >= '2024-10-01' AND txn_date < '2025-01-01' ORDER BY txn_date, txn_id LIMIT 20;

  15. Medium. Show each transaction's month in YYYY-MM form, first 20 rows. Target: 20 rows · txn_id, txn_date, month · (1, 2025-12-04, 2025-12); (2, 2024-12-21, 2024-12) …

    SELECT txn_id, txn_date, DATE_FORMAT(txn_date, '%Y-%m') AS month FROM transactions ORDER BY txn_id LIMIT 20;

  16. Medium. Label every customer's credit score band. Target: 20 rows · customer_id, customer_name, credit_score, band · (1, Diya Kaur, 754, Excellent); (2, Varun Rao, 467, Poor) …

    SELECT customer_id, customer_name, credit_score, CASE WHEN credit_score >= 750 THEN 'Excellent' WHEN credit_score >= 650 THEN 'Good' WHEN credit_score >= 550 THEN 'Fair' ELSE 'Poor' END AS band FROM customers ORDER BY customer_id LIMIT 20;

  17. Medium. List customers who joined before 2018. Target: 20 rows · customer_id, customer_name, join_date · (326, Diya Kaur, 2016-01-01); (386, Aarav Kaur, 2016-01-02) …

    SELECT customer_id, customer_name, join_date FROM customers WHERE join_date < '2018-01-01' ORDER BY join_date, customer_id LIMIT 20;

  18. Medium. Show each transaction amount in thousands, rounded to one decimal. Target: 20 rows · txn_id, amount_thousands · (1, 22.5); (2, 21.8) …

    SELECT txn_id, ROUND(amount / 1000, 1) AS amount_thousands FROM transactions ORDER BY txn_id LIMIT 20;

  19. Medium. Show how many days each account has been open at 2026-01-01. Target: 20 rows · account_id, open_date, days_open · (1, 2022-08-07, 1243); (2, 2016-12-25, 3294) …

    SELECT account_id, open_date, DATEDIFF('2026-01-01', open_date) AS days_open FROM accounts ORDER BY account_id LIMIT 20;

  20. Medium. Count how many different branches hold accounts. Target: 1 row · branches · (6)

    SELECT COUNT(DISTINCT branch) AS branches FROM accounts;

  21. Medium. Show each city in capitals with the customer's name. Target: 20 rows · customer_name, city · (Diya Kaur, HYDERABAD); (Varun Rao, HYDERABAD) …

    SELECT customer_name, UPPER(city) AS city FROM customers ORDER BY customer_id LIMIT 20;

  22. Medium. List transfer transactions made through the app. Target: 20 rows · txn_id, txn_type, channel, amount · (4, transfer, app, 38649.16); (16, transfer, app, 33705.25) …

    SELECT txn_id, txn_type, channel, amount FROM transactions WHERE txn_type = 'transfer' AND channel = 'app' ORDER BY txn_id LIMIT 20;

  23. Hard. Count accounts per branch. Target: 6 rows · branch, accounts · (Delhi CP, 107); (Jaipur MI Road, 104) …

    SELECT branch, COUNT(*) AS accounts FROM accounts GROUP BY branch ORDER BY accounts DESC, branch;

  24. Hard. Show the average balance per account type. Target: 3 rows · account_type, avg_balance · (savings, 418565.2); (current, 380161.77) …

    SELECT account_type, ROUND(AVG(balance), 2) AS avg_balance FROM accounts GROUP BY account_type ORDER BY avg_balance DESC, account_type;

  25. Hard. Count customers per segment. Target: 3 rows · segment, customers · (retail, 270); (premium, 98) …

    SELECT segment, COUNT(*) AS customers FROM customers GROUP BY segment ORDER BY customers DESC, segment;

  26. Hard. Show total transaction amount per channel. Target: 4 rows · channel, total_amount · (app, 12580888.32); (atm, 11863169.76) …

    SELECT channel, SUM(amount) AS total_amount FROM transactions GROUP BY channel ORDER BY total_amount DESC, channel;

  27. Hard. Show branches with more than 90 accounts. Target: 4 rows · branch, accounts · (Delhi CP, 107); (Jaipur MI Road, 104) …

    SELECT branch, COUNT(*) AS accounts FROM accounts GROUP BY branch HAVING COUNT(*) > 90 ORDER BY accounts DESC, branch;

  28. Hard. Show the 5 cities with the most customers. Target: 5 rows · city, customers · (Kolkata, 50); (Ludhiana, 44) …

    SELECT city, COUNT(*) AS customers FROM customers GROUP BY city ORDER BY customers DESC, city LIMIT 5;

  29. Hard. Show the average credit score per segment. Target: 3 rows · segment, avg_score · (premium, 600.8); (retail, 584.8) …

    SELECT segment, ROUND(AVG(credit_score), 1) AS avg_score FROM customers GROUP BY segment ORDER BY avg_score DESC, segment;

  30. Hard. Show total transaction amount per month of 2025. Target: 12 rows · month, total_amount · (2025-01, 1510921.84); (2025-02, 1769414.09) …

    SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month, SUM(amount) AS total_amount FROM transactions WHERE txn_date >= '2025-01-01' AND txn_date < '2026-01-01' GROUP BY month ORDER BY month;

  31. Hard. List accounts with no transactions. Target: 20 rows · account_id, account_type · (39, credit); (59, savings) …

    SELECT a.account_id, a.account_type FROM accounts a LEFT JOIN transactions t ON t.account_id = a.account_id WHERE t.txn_id IS NULL ORDER BY a.account_id LIMIT 20;

  32. Hard. Show the average transaction amount per transaction type. Target: 4 rows · txn_type, avg_amount · (payment, 20656.96); (transfer, 19849.13) …

    SELECT txn_type, ROUND(AVG(amount), 2) AS avg_amount FROM transactions GROUP BY txn_type ORDER BY avg_amount DESC, txn_type;

  33. Hard. Count transactions per month of 2024. Target: 12 rows · month, transactions · (2024-01, 81); (2024-02, 73) …

    SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month, COUNT(*) AS transactions FROM transactions WHERE txn_date >= '2024-01-01' AND txn_date < '2025-01-01' GROUP BY month ORDER BY month;

  34. Hard. List customers who hold two accounts. Target: 20 rows · customer_id, customer_name, accounts · (2, Varun Rao, 2); (3, Diya Reddy, 2) …

    SELECT c.customer_id, c.customer_name, COUNT(*) AS accounts FROM customers c JOIN accounts a ON a.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name HAVING COUNT(*) >= 2 ORDER BY accounts DESC, c.customer_id LIMIT 20;

  35. Hard. Show the 5 branches holding the most money. Target: 5 rows · branch, total_balance · (Delhi CP, 43260456.27); (Bengaluru HSR, 41457748.25) …

    SELECT branch, SUM(balance) AS total_balance FROM accounts GROUP BY branch ORDER BY total_balance DESC, branch LIMIT 5;

  36. Hard. Show account types whose average deposit is above 20,000. Target: 1 row · account_type, avg_deposit · (current, 20028.5)

    SELECT a.account_type, ROUND(AVG(t.amount), 2) AS avg_deposit FROM accounts a JOIN transactions t ON t.account_id = a.account_id WHERE t.txn_type = 'deposit' GROUP BY a.account_type HAVING AVG(t.amount) > 20000 ORDER BY avg_deposit DESC, a.account_type;

  37. Professional. Show the 10 customers with the most money across their accounts. Target: 10 rows · customer_id, customer_name, total_balance · (321, Sneha Mehta, 1464600.84); (203, Yash Chopra, 1454301.07) …

    SELECT c.customer_id, c.customer_name, SUM(a.balance) AS total_balance FROM customers c JOIN accounts a ON a.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name ORDER BY total_balance DESC, c.customer_id LIMIT 10;

  38. Professional. Show each branch's share of total deposits as a percentage. Target: 6 rows · branch, total_balance, pct_of_total · (Delhi CP, 43260456.27, 18.1); (Bengaluru HSR, 41457748.25, 17.3) …

    SELECT branch, SUM(balance) AS total_balance, ROUND(100 * SUM(balance) / (SELECT SUM(balance) FROM accounts), 1) AS pct_of_total FROM accounts GROUP BY branch ORDER BY total_balance DESC, branch;

  39. Professional. Rank customers by credit score inside their city. Target: 20 rows · city, customer_name, credit_score, city_rank · (Ahmedabad, Varun Reddy, 844, 1); (Ahmedabad, Harpreet Nair, 808, 2) …

    SELECT city, customer_name, credit_score, RANK() OVER (PARTITION BY city ORDER BY credit_score DESC, customer_id) AS city_rank FROM customers ORDER BY city, city_rank LIMIT 20;

  40. Professional. Show monthly deposit totals for 2025 with a running total. Target: 12 rows · month, deposits, running_total · (2025-01, 465028.48, 465028.48); (2025-02, 674407.05, 1139435.53) …

    WITH m AS (SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month, SUM(amount) AS deposits FROM transactions WHERE txn_type = 'deposit' AND txn_date >= '2025-01-01' AND txn_date < '2026-01-01' GROUP BY month) SELECT month, deposits, SUM(deposits) OVER (ORDER BY month) AS running_total FROM m ORDER BY month;

  41. Professional. Show the month-over-month change in transaction count for 2025. Target: 12 rows · month, transactions, prev_transactions, change · (2025-01, 79, NULL, NULL); (2025-02, 89, 79, 10) …

    WITH m AS (SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month, COUNT(*) AS transactions FROM transactions WHERE txn_date >= '2025-01-01' AND txn_date < '2026-01-01' GROUP BY month) SELECT month, transactions, LAG(transactions) OVER (ORDER BY month) AS prev_transactions, transactions - LAG(transactions) OVER (ORDER BY month) AS change FROM m ORDER BY month;

  42. Professional. List customers who have never transacted. Target: 11 rows · customer_id, customer_name · (51, Anaya Sharma); (109, Ishaan Das) …

    SELECT c.customer_id, c.customer_name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM accounts a JOIN transactions t ON t.account_id = a.account_id WHERE a.customer_id = c.customer_id) ORDER BY c.customer_id LIMIT 20;

  43. Professional. Show the largest transaction for every account. Target: 20 rows · account_id, txn_id, txn_date, amount · (1, 1487, 2024-08-10, 21176); (2, 68, 2025-03-15, 29931.5) …

    WITH r AS (SELECT account_id, txn_id, txn_date, amount, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC, txn_id) AS rn FROM transactions) SELECT account_id, txn_id, txn_date, amount FROM r WHERE rn = 1 ORDER BY account_id LIMIT 20;

  44. Professional. List accounts whose balance is above the overall average. Target: 20 rows · account_id, account_type, balance · (539, savings, 799500.97); (471, savings, 798300.66) …

    SELECT account_id, account_type, balance FROM accounts WHERE balance > (SELECT AVG(balance) FROM accounts) ORDER BY balance DESC, account_id LIMIT 20;

  45. Professional. Show each channel's share of 2025 transaction value. Target: 4 rows · channel, total_amount, pct · (app, 6216388.95, 31.9); (atm, 6111007.3, 31.3) …

    SELECT channel, SUM(amount) AS total_amount, ROUND(100 * SUM(amount) / (SELECT SUM(amount) FROM transactions WHERE txn_date >= '2025-01-01' AND txn_date < '2026-01-01'), 1) AS pct FROM transactions WHERE txn_date >= '2025-01-01' AND txn_date < '2026-01-01' GROUP BY channel ORDER BY total_amount DESC, channel;

  46. Professional. List customers whose total balance beats their city's average. Target: 20 rows · customer_name, city, total_balance, city_avg · (Sneha Mehta, Bengaluru, 1464600.84, 738910.41); (Yash Chopra, Kolkata, 1454301.07, 549904.72) …

    WITH per AS (SELECT c.customer_id, c.customer_name, c.city, SUM(a.balance) AS total_balance FROM customers c JOIN accounts a ON a.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name, c.city), avg_city AS (SELECT city, AVG(total_balance) AS city_avg FROM per GROUP BY city) SELECT p.customer_name, p.city, p.total_balance, ROUND(ac.city_avg, 2) AS city_avg FROM per p JOIN avg_city ac ON ac.city = p.city WHERE p.total_balance > ac.city_avg ORDER BY p.total_balance DESC, p.customer_id LIMIT 20;

  47. Professional. Show the first transaction of every account (one row each). Target: 20 rows · account_id, txn_id, txn_date, amount · (1, 1487, 2024-08-10, 21176); (2, 68, 2025-03-15, 29931.5) …

    WITH r AS (SELECT account_id, txn_id, txn_date, amount, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date, txn_id) AS rn FROM transactions) SELECT account_id, txn_id, txn_date, amount FROM r WHERE rn = 1 ORDER BY account_id LIMIT 20;

  48. Professional. List customers whose accounts sit in more than one branch. Target: 20 rows · customer_id, customer_name, branches · (3, Diya Reddy, 2); (6, Anaya Nair, 2) …

    SELECT c.customer_id, c.customer_name, COUNT(DISTINCT a.branch) AS branches FROM customers c JOIN accounts a ON a.customer_id = c.customer_id GROUP BY c.customer_id, c.customer_name HAVING COUNT(DISTINCT a.branch) > 1 ORDER BY branches DESC, c.customer_id LIMIT 20;

  49. Professional. Build a segment dashboard: customers, accounts, balance and average score. Target: 3 rows · segment, customers, accounts, total_balance, avg_score · (retail, 270, 398, 160457992.21, 584.1); (premium, 98, 143, 59087278.32, 593.6) …

    SELECT c.segment, COUNT(DISTINCT c.customer_id) AS customers, COUNT(a.account_id) AS accounts, SUM(a.balance) AS total_balance, ROUND(AVG(c.credit_score), 1) AS avg_score FROM customers c JOIN accounts a ON a.customer_id = c.customer_id GROUP BY c.segment ORDER BY total_balance DESC, c.segment;

  50. Professional. Show 2025 transaction value per month and channel. Target: 48 rows · month, channel, total_amount · (2025-01, atm, 624801.37); (2025-01, netbanking, 400579.94) …

    SELECT DATE_FORMAT(txn_date, '%Y-%m') AS month, channel, SUM(amount) AS total_amount FROM transactions WHERE txn_date >= '2025-01-01' AND txn_date < '2026-01-01' GROUP BY month, channel ORDER BY month, total_amount DESC;

Up next · Lesson 18

Real life: e-commerce marketplace

200 min · 50 practice problems

Start next lesson
Real life: hospital records Real life: e-commerce marketplace