Largest Debit on Each Savings Account — SQL Problem
For a spending-pattern check, the risk team wants each account's largest debit. If several debits share the largest amount, all of them are reported.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #59
Problem statement
For a spending-pattern check, the risk team wants each account's largest debit. If several debits share the largest amount, all of them are reported. Credits are ignored, and an account with no debits does not appear.
Return account_no, txn_id, amount and txn_date for those debits, in any order.
Tables
Table: AccountTxn
| Column | Type |
|---|---|
| txn_id | int |
| account_no | bigint |
| txn_type | enum(DEBIT, CREDIT) |
| amount | int |
| txn_date | date |
Primary key: txn_id.
One row per transaction; account_no is the 12-digit account number.
Examples
Example 1
AccountTxn
| txn_id | account_no | txn_type | amount | txn_date |
|---|---|---|---|---|
| 1 | 501002345671 | DEBIT | 4500 | 2024-02-01 |
| 2 | 501002345671 | CREDIT | 90000 | 2024-02-01 |
| 3 | 501002345671 | DEBIT | 12000 | 2024-02-07 |
| 4 | 501002345688 | DEBIT | 3000 | 2024-02-03 |
| 5 | 501002345688 | DEBIT | 3000 | 2024-02-15 |
| 6 | 501002345695 | CREDIT | 25000 | 2024-02-05 |
| 7 | 501002345688 | DEBIT | 800 | 2024-02-20 |
| 8 | 501002345671 | DEBIT | 11999 | 2024-02-21 |
Output
| account_no | txn_id | amount | txn_date |
|---|---|---|---|
| 501002345671 | 3 | 12000 | 2024-02-07 |
| 501002345688 | 4 | 3000 | 2024-02-03 |
| 501002345688 | 5 | 3000 | 2024-02-15 |
How to solve Largest Debit on Each Savings Account
This is top-1 per group with ties. Filter to debits, then rank them inside each account by amount, largest first: RANK() OVER (PARTITION BY account_no ORDER BY amount DESC). Every debit that ties for the largest amount gets rank 1, so keeping rnk = 1 returns all of them. DENSE_RANK would work equally well here; ROW_NUMBER would pick one of the tied rows arbitrarily, making the answer depend on storage order.
Window functions are computed after WHERE, so the rank is filtered in an outer query. The credit filter must sit inside, before ranking — otherwise a large credit would take rank 1 and the account's debits would disappear.
The classic alternatives compare each debit with its account's maximum: either a correlated MAX subquery, or a join to a grouped derived table of maxima. Both include the txn_type = 'DEBIT' test on both sides. The window version sorts the debits once per account; the join version is a group-by and a hash join.
Reference solution (MySQL)
SELECT account_no, txn_id, amount, txn_date
FROM (
SELECT account_no, txn_id, amount, txn_date,
RANK() OVER (PARTITION BY account_no ORDER BY amount DESC) AS rnk
FROM AccountTxn
WHERE txn_type = 'DEBIT'
) d
WHERE rnk = 1Another way
SELECT account_no, txn_id, amount, txn_date FROM AccountTxn t WHERE txn_type = 'DEBIT' AND amount = (SELECT MAX(amount) FROM AccountTxn u WHERE u.account_no = t.account_no AND u.txn_type = 'DEBIT')Another way
SELECT t.account_no, t.txn_id, t.amount, t.txn_date FROM AccountTxn t
JOIN (SELECT account_no, MAX(amount) AS top_amount FROM AccountTxn WHERE txn_type = 'DEBIT' GROUP BY account_no) m
ON m.account_no = t.account_no AND m.top_amount = t.amount
WHERE t.txn_type = 'DEBIT'← Borrowers With Repeatedly Overdue EMIs · Credit Cards Over 30% Utilisation in September →