Consumers With Repeated Late Payment Fees — SQL Problem
When an electricity bill is paid after its due date, the payment carries a late_fee. The vigilance team follows up with consumers who keep paying late.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #511
Problem statement
When an electricity bill is paid after its due date, the payment carries a late_fee. The vigilance team follows up with consumers who keep paying late.
Considering only payments made in 2024 (by paid_on), return each consumer with at least two payments carrying a late fee greater than 0, with the columns consumer_no, late_payments (how many such payments) and total_late_fee (the sum of those fees in rupees). Order by total_late_fee from highest to lowest, then by consumer_no.
Tables
Table: Bill
| Column | Type |
|---|---|
| bill_id | int |
| consumer_no | int |
| due_date | date |
| amount | int |
Primary key: bill_id.
One electricity bill of one consumer.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| bill_id | int |
| paid_on | date |
| amount_paid | int |
| late_fee | int |
Primary key: payment_id.
late_fee is 0 for a payment made on time. Every bill_id is in Bill.
Examples
Example 1
Bill
| bill_id | consumer_no | due_date | amount |
|---|---|---|---|
| 1 | 50011 | 2024-01-20 | 1200 |
| 2 | 50011 | 2024-02-20 | 1300 |
| 3 | 50011 | 2024-03-20 | 1100 |
| 4 | 50027 | 2024-01-20 | 900 |
| 5 | 50027 | 2024-02-20 | 950 |
| 6 | 50034 | 2023-12-20 | 2000 |
| 7 | 50034 | 2024-02-20 | 2100 |
| 8 | 50042 | 2024-02-20 | 700 |
Payment
| payment_id | bill_id | paid_on | amount_paid | late_fee |
|---|---|---|---|---|
| 1 | 1 | 2024-01-25 | 1200 | 24 |
| 2 | 2 | 2024-02-28 | 1300 | 26 |
| 3 | 3 | 2024-03-18 | 1100 | 0 |
| 4 | 4 | 2024-01-29 | 900 | 25 |
| 5 | 5 | 2024-02-26 | 950 | 25 |
| 6 | 6 | 2023-12-28 | 2000 | 40 |
| 7 | 7 | 2024-02-24 | 2100 | 42 |
| 8 | 8 | 2024-02-22 | 700 | 14 |
Output
| consumer_no | late_payments | total_late_fee |
|---|---|---|
| 50011 | 2 | 50 |
| 50027 | 2 | 50 |
How to solve Consumers With Repeated Late Payment Fees
The consumer number lives on Bill and the fee on Payment, so the payments are joined to their bills on bill_id. WHERE then keeps the rows that matter: payments dated in 2024 that carry a fee. A payment from December 2023 does not count even though the consumer is the same, and an on-time payment (fee 0) is no late payment.
Grouping by consumer_no gives each consumer's late payments; COUNT(*) counts them and SUM(late_fee) totals them. The "at least two" condition is about a group, so it goes in HAVING COUNT(*) >= 2 — WHERE runs before grouping and cannot see counts.
The alternative keeps on-time 2024 payments in the groups and counts the late ones with a conditional SUM; the fee total is the same because on-time fees are 0. Equal totals are ordered by consumer number. The cost is one join over the payments plus grouping.
Reference solution (MySQL)
SELECT b.consumer_no, COUNT(*) AS late_payments, SUM(p.late_fee) AS total_late_fee
FROM Payment p
JOIN Bill b ON b.bill_id = p.bill_id
WHERE p.late_fee > 0
AND p.paid_on BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY b.consumer_no
HAVING COUNT(*) >= 2
ORDER BY total_late_fee DESC, b.consumer_noAnother way
SELECT b.consumer_no,
SUM(CASE WHEN p.late_fee > 0 THEN 1 ELSE 0 END) AS late_payments,
SUM(p.late_fee) AS total_late_fee
FROM Bill b JOIN Payment p ON p.bill_id = b.bill_id
WHERE YEAR(p.paid_on) = 2024
GROUP BY b.consumer_no
HAVING SUM(CASE WHEN p.late_fee > 0 THEN 1 ELSE 0 END) >= 2
ORDER BY SUM(p.late_fee) DESC, b.consumer_no← Monthly Recharge Revenue by Pack Type · Busiest Calling Hour per Cell Tower →