Finance · Intermediate
Join seven tables of bank data with SQL to find who transacts most, how cash is giving way to M-Pesa, and who to offer a card or loan.
Mlima Bank is a fictional Kenyan retail bank with branches in 15 counties. It wants to grow its card and lending products, but before it spends on marketing, the Head of Retail Banking wants to know how customers really use their accounts: who transacts most, how much moves as cash versus M-Pesa, which counties hold the most valuable customers, and who already has a card or a loan. You have a core-banking extract of seven tables: counties, customers, accounts, the link between customers and accounts, four years of transactions (2021 to 2024), loans and cards.
Your role
Data Analyst, Retail Banking Insights team, Mlima Bank
Customer data sits in separate tables and nobody has joined them into one view of the customer. The bank cannot say which customers are under-served by cards and loans, or where cross-selling would pay off. The marketing budget for next year depends on that answer.
Read the data dictionary and draw how the seven tables connect, marking the key column on each join. Then count the rows in every table so you know what you are working with.
Use GROUP BY on single tables and simple joins to describe the bank: accounts by type and statement frequency, customers by county, joint accounts, and the loan book by status.
Synthetic data. Mlima Bank and its customers are fictional; the data was generated to follow the structure of the PKDD'99 Czech bank dataset (a real, anonymised bank extract we cannot host), re-set in Kenya. County populations follow the 2019 census, rounded; the income and unemployment figures are illustrative. All money is in Kenyan shillings (KES), whole shillings. Seven CSV files: • bank-counties.csv: 15 rows × 7 columns, one row per county with region, population, average monthly household income, unemployment and urban share. • bank-customers.csv: 1,034 rows × 5 columns, one row per customer: name, gender, birth year and home county. • bank-accounts.csv: 900 rows × 5 columns, one row per account: branch county, opening date (2021 to mid-2024), account type (Current, Savings, Business) and statement frequency. • bank-account-holders.csv: 1,034 rows × 4 columns, the link table. One row per person on an account: every account has one OWNER, and some also have a JOINT holder. • bank-transactions.csv: 102,880 rows × 8 columns, one row per transaction from 1 Jan 2021 to 31 Dec 2024: date, CREDIT or DEBIT, channel (M-Pesa, ATM cash withdrawal, Branch, Card POS, Standing order, Bank transfer, Interest), purpose, amount and the balance after the transaction. • bank-loans.csv: 296 rows × 7 columns, one row per loan: date, amount, length in months, monthly payment and status (Repaid, Defaulted, Current, In arrears). • bank-cards.csv: 232 rows × 4 columns, one row per card (Classic, Gold, Youth) with its issue date. A card belongs to a holder, not to an account. How they connect: customers and accounts meet in bank-account-holders (customer_id, account_id). Cards join to holders on holder_id. Transactions and loans join to accounts on account_id. Customers and accounts both join to counties on county_id. The data is clean: no duplicates or broken keys. The only gaps are deliberate: purpose is blank for everyday transactions that have no special purpose.
Open in SQL workspace
How to do this project
The scenario
Objectives
Tasks and business questions
Dataset and data dictionarybank_counties15 Kenyan counties where Mlima Bank has branches, with population, income and unemployment. · 15 rows
15 exercises worth 255 points. Your query runs against this project’s dataset and is marked automatically.
Summarise transactions by channel and by year. Compare the share of money going out through ATM cash withdrawals with the share going out through M-Pesa, and name the ten busiest accounts.
Use a CTE with ROW_NUMBER to find each account's current balance (the balance on its latest transaction) and average it by county. Then compare bad loans (Defaulted or In arrears) for accounts with and without a salary credit.
Rank accounts by total transaction value with NTILE(10) and check which of the top 10% have no card or no loan. Use LAG to show the 2024 M-Pesa trend, then write your three recommendations.
| Column | Type | Description | Example |
|---|---|---|---|
county_id | integer | Unique county number. Joins to bank_customers.county_id and bank_accounts.county_id. | 1 |
county | text | County name. | Nairobi |
region | text | Former province the county belongs to, used for grouping. | Rift Valley |
population | integer | Population from the 2019 census, rounded. | 2417735 |
avg_monthly_income_kes | integer | Average monthly household income in shillings (illustrative). | 52300 |
unemployment_pct | decimal | Share of the labour force without work, in percent (illustrative). | 8.9 |
urban_pct | decimal | Share of the population living in towns, in percent. | 61.0 |
All graded SQL exercises passed in the SQL workspacebank_customers1,034 customers: name, gender, birth year and home county. · 1,034 rows
| Column | Type | Description | Example |
|---|---|---|---|
customer_id | integer | Unique customer number. Joins to bank_account_holders.customer_id. | 10001 |
full_name | text | Customer's name (fictional). | Mercy Muthoni |
gender | text | Female or Male. | Female |
birth_year | integer | Year of birth. Age at the end of 2024 is 2024 - birth_year. | 1986 |
county_id | integer | County where the customer lives. Joins to bank_counties.county_id. | 6 |
bank_accounts900 accounts with branch county, opening date, type and statement frequency. · 900 rows
| Column | Type | Description | Example |
|---|---|---|---|
account_id | integer | Unique account number. Joins to transactions, loans and account holders. | 2001 |
county_id | integer | County of the branch that holds the account. Joins to bank_counties.county_id. | 1 |
opened_date | date | Date the account was opened, between 2021-01-01 and 2024-06-28. | 2021-07-29 |
account_type | text | Current, Savings or Business. Savings accounts earn interest every quarter. | Current |
statement_frequency | text | How often the customer gets a statement: Monthly, Weekly or Per transaction. | Monthly |
bank_account_holders1,034 rows linking customers to accounts: one OWNER per account, plus a JOINT holder on shared accounts. · 1,034 rows
| Column | Type | Description | Example |
|---|---|---|---|
holder_id | integer | Unique number for this person-on-account link. Cards join here. | 30001 |
account_id | integer | The account. Joins to bank_accounts.account_id. | 2001 |
customer_id | integer | The person. Joins to bank_customers.customer_id. | 10001 |
holder_type | text | OWNER (the main holder; every account has exactly one) or JOINT (a second person who can use the account). | OWNER |
bank_transactions102,880 transactions from 2021 to 2024 with channel, purpose, amount and running balance. · 102,880 rows
| Column | Type | Description | Example |
|---|---|---|---|
transaction_id | integer | Unique transaction number. Numbers follow date order, and within one account and day they follow the order the transactions happened. | 1000001 |
account_id | integer | The account. Joins to bank_accounts.account_id. | 2087 |
txn_date | date | Date of the transaction. | 2024-03-25 |
txn_type | text | CREDIT (money in) or DEBIT (money out). | DEBIT |
channel | text | How the money moved: M-Pesa, ATM cash withdrawal, Branch, Card POS (paying by card in a shop), Standing order, Bank transfer or Interest. | M-Pesa |
purpose | text | What the money was for: Salary, Loan disbursement, Loan repayment, Insurance, Household bills, School fees, Interest or Opening deposit. Blank (NULL in SQL) for everyday transactions. | Salary |
amount_kes | decimal (KES) | Amount in shillings. Always positive; txn_type says which way it went. | 16930 |
balance_kes | decimal (KES) | Account balance in shillings straight after this transaction. | 248113 |
bank_loans296 loans with amount, length, monthly payment and status. · 296 rows
| Column | Type | Description | Example |
|---|---|---|---|
loan_id | integer | Unique loan number. | 7001 |
account_id | integer | The account the loan was paid into and is repaid from. Joins to bank_accounts.account_id. | 2001 |
loan_date | date | Date the loan was paid out. | 2023-11-08 |
amount_kes | decimal (KES) | Amount borrowed in shillings. | 445000 |
duration_months | integer | Length of the loan in months: 12, 24, 36, 48 or 60. | 12 |
monthly_payment_kes | decimal (KES) | Agreed monthly repayment in shillings. | 39955 |
status | text | Repaid (finished, paid in full), Defaulted (finished, not paid), Current (still running, up to date) or In arrears (still running, payments missed). | Current |
bank_cards232 debit cards (Classic, Gold, Youth), each issued to one account holder. · 232 rows
| Column | Type | Description | Example |
|---|---|---|---|
card_id | integer | Unique card number. | 50001 |
holder_id | integer | The person-on-account the card was issued to. Joins to bank_account_holders.holder_id. | 30004 |
card_type | text | Classic, Gold (for higher earners) or Youth (for holders under 25 when issued). | Classic |
issued_date | date | Date the card was issued. | 2022-12-29 |
The exercises are marked automatically. The summary is marked on whether every figure can be reproduced from a query, whether joins avoid double-counting joint accounts, and whether each recommendation follows from the numbers.
Recommended tools
Skills covered