Electricity Bills Paid After the Due Date — SQL Problem
The distribution company's collections desk reviews bills that were settled late.
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #504
Problem statement
The distribution company's collections desk reviews bills that were settled late. Each bill is paid in full by at most one payment; some bills are still unpaid.
Return every bill paid strictly after its due_date, with the columns bill_id, consumer_no, due_date, paid_on and days_late (the number of days between the due date and the payment). A payment on the due date is on time; unpaid bills are not in the answer. Order by days_late from highest to lowest, then by bill_id.
Tables
Table: Bill
| Column | Type |
|---|---|
| bill_id | int |
| consumer_no | int |
| bill_month | date |
| amount | int |
| due_date | date |
Primary key: bill_id.
One monthly electricity bill; bill_month is the first day of the month billed, amount is in rupees.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| bill_id | int |
| paid_on | date |
| amount_paid | int |
Primary key: payment_id.
A bill has at most one payment, and every bill_id here is in Bill.
Examples
Example 1
Bill
| bill_id | consumer_no | bill_month | amount | due_date |
|---|---|---|---|---|
| 1 | 50011 | 2024-05-01 | 1240 | 2024-05-20 |
| 2 | 50011 | 2024-06-01 | 1385 | 2024-06-20 |
| 3 | 50027 | 2024-05-01 | 860 | 2024-05-20 |
| 4 | 50027 | 2024-06-01 | 910 | 2024-06-20 |
| 5 | 50034 | 2024-06-01 | 2210 | 2024-06-20 |
| 6 | 50034 | 2024-05-01 | 1990 | 2024-05-20 |
Payment
| payment_id | bill_id | paid_on | amount_paid |
|---|---|---|---|
| 1 | 1 | 2024-05-18 | 1240 |
| 2 | 2 | 2024-06-25 | 1385 |
| 3 | 3 | 2024-05-20 | 860 |
| 4 | 4 | 2024-06-27 | 910 |
| 5 | 6 | 2024-06-01 | 1990 |
Output
| bill_id | consumer_no | due_date | paid_on | days_late |
|---|---|---|---|---|
| 6 | 50034 | 2024-05-20 | 2024-06-01 | 12 |
| 4 | 50027 | 2024-06-20 | 2024-06-27 | 7 |
| 2 | 50011 | 2024-06-20 | 2024-06-25 | 5 |
How to solve Electricity Bills Paid After the Due Date
The two dates that decide lateness are in different tables, so the first step is an inner join of Bill and Payment on bill_id. Each payment finds its one bill; a bill with no payment has no partner and drops out, which is what the statement wants for unpaid bills.
Lateness is then a strict comparison, paid_on > due_date: a payment on the due date itself is on time. DATEDIFF(paid_on, due_date) gives the delay in days — note the argument order, later date first, or the numbers come out negative. Filtering on DATEDIFF(…) > 0 is the same test.
The order is by the delay, longest first, with bill_id deciding between bills that are equally late. With an index on Payment.bill_id the join is one lookup per payment, and the sort is over the late bills only.
Reference solution (MySQL)
SELECT b.bill_id, b.consumer_no, b.due_date, p.paid_on,
DATEDIFF(p.paid_on, b.due_date) AS days_late
FROM Bill b
JOIN Payment p ON p.bill_id = b.bill_id
WHERE p.paid_on > b.due_date
ORDER BY days_late DESC, b.bill_idAnother way
SELECT b.bill_id, b.consumer_no, b.due_date, p.paid_on, DATEDIFF(p.paid_on, b.due_date) AS days_late
FROM Bill b, Payment p
WHERE p.bill_id = b.bill_id AND DATEDIFF(p.paid_on, b.due_date) > 0
ORDER BY DATEDIFF(p.paid_on, b.due_date) DESC, b.bill_id← Completed Port-Outs by Recipient Operator · Power Outages Lasting Four Hours or More →