Credit Cards Over 30% Utilisation in September

Medium
60.

Credit Cards Over 30% Utilisation in September

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

ColumnType
card_idint
holder_namevarchar
credit_limitint

Primary key: card_id.

One row per credit card; credit_limit is in rupees.

Table: CardTxn

ColumnTypeValues
txn_idint
card_idint
txn_typeenumPURCHASE, REFUND
amountint
txn_datedate

Primary key: txn_id.

One row per card transaction; amount is positive for both types.

Example

The rows must come back in the order shown.

Card

card_idholder_namecredit_limit
1Priya Menon60000
2Arjun Rao150000
3Mia Verma30000
4Vivaan Joshi90000

CardTxn

txn_idcard_idtxn_typeamounttxn_date
11PURCHASE150002024-09-03
21PURCHASE62002024-09-18
32PURCHASE450002024-09-10
43PURCHASE120002024-09-30
53REFUND20002024-09-25
63PURCHASE90002024-10-01
74PURCHASE400002024-08-31
84PURCHASE300002024-09-12

Output

card_idholder_nameutilisation_pct
1Priya Menon35.33
3Mia Verma33.33
4Vivaan Joshi33.33
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.

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

ColumnType
card_idint
holder_namevarchar
credit_limitint

Primary key: card_id.

One row per credit card; credit_limit is in rupees.

Table: CardTxn

ColumnType
txn_idint
card_idint
txn_typeenum(PURCHASE, REFUND)
amountint
txn_datedate

Primary key: txn_id.

One row per card transaction; amount is positive for both types.

Examples

Example 1

Card

card_idholder_namecredit_limit
1Priya Menon60000
2Arjun Rao150000
3Mia Verma30000
4Vivaan Joshi90000

CardTxn

txn_idcard_idtxn_typeamounttxn_date
11PURCHASE150002024-09-03
21PURCHASE62002024-09-18
32PURCHASE450002024-09-10
43PURCHASE120002024-09-30
53REFUND20002024-09-25
63PURCHASE90002024-10-01
74PURCHASE400002024-08-31
84PURCHASE300002024-09-12

Output

card_idholder_nameutilisation_pct
1Priya Menon35.33
3Mia Verma33.33
4Vivaan Joshi33.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_id

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