Credit Cards Over 30% Utilisation in September — SQL Problem
Bureaus penalise cardholders who use more than 30% of their credit limit, so the card team warns them.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #60
Problem statement
Bureaus penalise cardholders who use more than 30% of their credit limit, so the card team warns them. A card's September 2024 utilisation is its net spend in that month — purchases minus refunds dated 1–30 September 2024 — as a percentage of its credit_limit, rounded to 2 decimals.
Return card_id, holder_name and utilisation_pct for every card whose utilisation is strictly greater than 30. Order the rows by utilisation_pct descending, then by card_id ascending.
Tables
Table: Card
| Column | Type |
|---|---|
| card_id | int |
| holder_name | varchar |
| credit_limit | int |
Primary key: card_id.
One row per credit card; credit_limit is in rupees.
Table: CardTxn
| Column | Type |
|---|---|
| txn_id | int |
| card_id | int |
| txn_type | enum(PURCHASE, REFUND) |
| amount | int |
| txn_date | date |
Primary key: txn_id.
One row per card transaction; amount is positive for both types.
Examples
Example 1
Card
| card_id | holder_name | credit_limit |
|---|---|---|
| 1 | Priya Menon | 60000 |
| 2 | Arjun Rao | 150000 |
| 3 | Mia Verma | 30000 |
| 4 | Vivaan Joshi | 90000 |
CardTxn
| txn_id | card_id | txn_type | amount | txn_date |
|---|---|---|---|---|
| 1 | 1 | PURCHASE | 15000 | 2024-09-03 |
| 2 | 1 | PURCHASE | 6200 | 2024-09-18 |
| 3 | 2 | PURCHASE | 45000 | 2024-09-10 |
| 4 | 3 | PURCHASE | 12000 | 2024-09-30 |
| 5 | 3 | REFUND | 2000 | 2024-09-25 |
| 6 | 3 | PURCHASE | 9000 | 2024-10-01 |
| 7 | 4 | PURCHASE | 40000 | 2024-08-31 |
| 8 | 4 | PURCHASE | 30000 | 2024-09-12 |
Output
| card_id | holder_name | utilisation_pct |
|---|---|---|
| 1 | Priya Menon | 35.33 |
| 3 | Mia Verma | 33.33 |
| 4 | Vivaan Joshi | 33.33 |
How to solve Credit Cards Over 30% Utilisation in September
Three steps: restrict to the statement month, net the money per card, and compare with the limit. The date filter BETWEEN '2024-09-01' AND '2024-09-30' keeps the month inclusive at both ends and drops the 31 August and 1 October rows. Inside each card's group, SUM(CASE WHEN txn_type = 'PURCHASE' THEN amount ELSE -amount END) gives purchases minus refunds.
The utilisation is 100 × net ÷ credit_limit, rounded to two decimals for display. For the filter, compare the unrounded quantity in integer form — net × 100 > 30 × credit_limit — so a card at exactly 30% is excluded with no floating-point doubt. Cards with no September transactions have no group at all, and their 0% would be excluded anyway.
Pre-aggregating the transactions in a derived table and joining it to Card is an equivalent shape, with YEAR/MONTH as another way to say "September 2024". Both are a scan of the month's transactions and a small join.
Reference solution (MySQL)
SELECT c.card_id, c.holder_name,
ROUND(100 * SUM(CASE WHEN t.txn_type = 'PURCHASE' THEN t.amount ELSE -t.amount END) / c.credit_limit, 2) AS utilisation_pct
FROM Card c
JOIN CardTxn t ON t.card_id = c.card_id
WHERE t.txn_date BETWEEN '2024-09-01' AND '2024-09-30'
GROUP BY c.card_id, c.holder_name, c.credit_limit
HAVING SUM(CASE WHEN t.txn_type = 'PURCHASE' THEN t.amount ELSE -t.amount END) * 100 > 30 * c.credit_limit
ORDER BY utilisation_pct DESC, c.card_idAnother way
SELECT card_id, holder_name, utilisation_pct FROM (
SELECT c.card_id, c.holder_name, c.credit_limit, ROUND(100 * s.net / c.credit_limit, 2) AS utilisation_pct, s.net
FROM Card c JOIN (
SELECT card_id, SUM(IF(txn_type = 'REFUND', -amount, amount)) AS net FROM CardTxn
WHERE YEAR(txn_date) = 2024 AND MONTH(txn_date) = 9 GROUP BY card_id
) s ON s.card_id = c.card_id
) u WHERE net * 100 > 30 * credit_limit ORDER BY utilisation_pct DESC, card_id← Largest Debit on Each Savings Account · Salary Credits by Employer From Statement Narrations →