Electricity Bill Settlement From Part Payments — SQL Problem
Consumers may pay an electricity bill in several parts. A bill is settled on the date its payments, added up in payment order (by paid_on, then payment_id),…
- Difficulty: Hard
- Topics: Window Functions, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #522
Problem statement
Consumers may pay an electricity bill in several parts. A bill is settled on the date its payments, added up in payment order (by paid_on, then payment_id), first reach or exceed the bill amount; overpayment is possible.
Return every bill with the columns bill_id, consumer_no, settled_on (NULL if the payments never reach the amount, or there are none) and settlement_status: 'on time' when settled on or before due_date, 'late' when settled after it, 'unsettled' otherwise. Order by bill_id.
Tables
Table: Bill
| Column | Type |
|---|---|
| bill_id | int |
| consumer_no | int |
| amount | int |
| due_date | date |
Primary key: bill_id.
One monthly bill; 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 part payment towards one bill; amount_paid is always positive.
Examples
Example 1
Bill
| bill_id | consumer_no | amount | due_date |
|---|---|---|---|
| 1 | 50011 | 1200 | 2024-05-20 |
| 2 | 50011 | 1500 | 2024-06-20 |
| 3 | 50027 | 900 | 2024-05-20 |
| 4 | 50034 | 2000 | 2024-05-20 |
| 5 | 50042 | 700 | 2024-05-20 |
Payment
| payment_id | bill_id | paid_on | amount_paid |
|---|---|---|---|
| 1 | 1 | 2024-05-10 | 500 |
| 2 | 1 | 2024-05-20 | 700 |
| 3 | 2 | 2024-06-15 | 1000 |
| 4 | 2 | 2024-06-25 | 300 |
| 5 | 2 | 2024-07-02 | 400 |
| 6 | 3 | 2024-05-25 | 1000 |
| 7 | 4 | 2024-05-15 | 1500 |
Output
| bill_id | consumer_no | settled_on | settlement_status |
|---|---|---|---|
| 1 | 50011 | 2024-05-20 | on time |
| 2 | 50011 | 2024-07-02 | late |
| 3 | 50027 | 2024-05-25 | late |
| 4 | 50034 | NULL | unsettled |
| 5 | 50042 | NULL | unsettled |
How to solve Electricity Bill Settlement From Part Payments
The question "when was it fully paid?" is a running total question. SUM(amount_paid) OVER (PARTITION BY bill_id ORDER BY paid_on, payment_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) gives, on each payment, the total paid towards that bill up to and including it. The explicit ROWS frame with a unique order matters: with RANGE, two payments on the same day would both see the day's combined total — harmless for the date here, but a habit worth keeping.
Because payments are positive the running total only grows, so the first payment where it reaches the bill amount is simply the earliest paid_on among the rows with paid_so_far >= amount — a MIN after a join to Bill for the amount. Overpayments are fine: the threshold is reached, not matched.
The final SELECT starts from Bill and LEFT JOINs the settlement dates, so a bill with too little or no money paid survives with settled_on NULL. The CASE grades it: NULL is unsettled, a date on or before the due date is on time, anything later is late. The correlated alternative recomputes the running total per payment with a nested SUM — quadratic, but window-free.
Reference solution (MySQL)
WITH running AS (
SELECT bill_id, paid_on,
SUM(amount_paid) OVER (PARTITION BY bill_id ORDER BY paid_on, payment_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS paid_so_far
FROM Payment
), settled AS (
SELECT r.bill_id, MIN(r.paid_on) AS settled_on
FROM running r
JOIN Bill b ON b.bill_id = r.bill_id
WHERE r.paid_so_far >= b.amount
GROUP BY r.bill_id
)
SELECT b.bill_id, b.consumer_no, s.settled_on,
CASE WHEN s.settled_on IS NULL THEN 'unsettled'
WHEN s.settled_on <= b.due_date THEN 'on time'
ELSE 'late' END AS settlement_status
FROM Bill b
LEFT JOIN settled s ON s.bill_id = b.bill_id
ORDER BY b.bill_idAnother way
SELECT bill_id, consumer_no, settled_on,
IF(settled_on IS NULL, 'unsettled', IF(settled_on > due_date, 'late', 'on time')) AS settlement_status
FROM (
SELECT b.bill_id, b.consumer_no, b.due_date,
(SELECT MIN(p.paid_on) FROM Payment p
WHERE p.bill_id = b.bill_id
AND (SELECT SUM(q.amount_paid) FROM Payment q
WHERE q.bill_id = p.bill_id
AND (q.paid_on < p.paid_on OR (q.paid_on = p.paid_on AND q.payment_id <= p.payment_id))) >= b.amount
) AS settled_on
FROM Bill b
) x
ORDER BY bill_id← Top Two Data Consumers per Circle, Ties Included · Premium New Year Stays in Goa →