Largest Debit on Each Savings Account

Medium
59.

Largest Debit on Each Savings Account

MediumWindow Functions

MySQL

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

ColumnTypeValues
txn_idint
account_nobigint
txn_typeenumDEBIT, CREDIT
amountint
txn_datedate

Primary key: txn_id.

One row per transaction; account_no is the 12-digit account number.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

AccountTxn

txn_idaccount_notxn_typeamounttxn_date
1501002345671DEBIT45002024-02-01
2501002345671CREDIT900002024-02-01
3501002345671DEBIT120002024-02-07
4501002345688DEBIT30002024-02-03
5501002345688DEBIT30002024-02-15
6501002345695CREDIT250002024-02-05
7501002345688DEBIT8002024-02-20
8501002345671DEBIT119992024-02-21

Output

account_notxn_idamounttxn_date
5010023456713120002024-02-07
501002345688430002024-02-03
501002345688530002024-02-15
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
txn_idint
account_nobigint
txn_typeenum(DEBIT, CREDIT)
amountint
txn_datedate

Primary key: txn_id.

One row per transaction; account_no is the 12-digit account number.

Examples

Example 1

AccountTxn

txn_idaccount_notxn_typeamounttxn_date
1501002345671DEBIT45002024-02-01
2501002345671CREDIT900002024-02-01
3501002345671DEBIT120002024-02-07
4501002345688DEBIT30002024-02-03
5501002345688DEBIT30002024-02-15
6501002345695CREDIT250002024-02-05
7501002345688DEBIT8002024-02-20
8501002345671DEBIT119992024-02-21

Output

account_notxn_idamounttxn_date
5010023456713120002024-02-07
501002345688430002024-02-03
501002345688530002024-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 = 1

Another 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 →