Electricity Bill Settlement From Part Payments

Hard
522.

Electricity Bill Settlement From Part Payments

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

ColumnType
bill_idint
consumer_noint
amountint
due_datedate

Primary key: bill_id.

One monthly bill; amount is in rupees.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint

Primary key: payment_id.

A part payment towards one bill; amount_paid is always positive.

Example

The rows must come back in the order shown.

Bill

bill_idconsumer_noamountdue_date
15001112002024-05-20
25001115002024-06-20
3500279002024-05-20
45003420002024-05-20
5500427002024-05-20

Payment

payment_idbill_idpaid_onamount_paid
112024-05-10500
212024-05-20700
322024-06-151000
422024-06-25300
522024-07-02400
632024-05-251000
742024-05-151500

Output

bill_idconsumer_nosettled_onsettlement_status
1500112024-05-20on time
2500112024-07-02late
3500272024-05-25late
450034NULLunsettled
550042NULLunsettled
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.

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

ColumnType
bill_idint
consumer_noint
amountint
due_datedate

Primary key: bill_id.

One monthly bill; amount is in rupees.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint

Primary key: payment_id.

A part payment towards one bill; amount_paid is always positive.

Examples

Example 1

Bill

bill_idconsumer_noamountdue_date
15001112002024-05-20
25001115002024-06-20
3500279002024-05-20
45003420002024-05-20
5500427002024-05-20

Payment

payment_idbill_idpaid_onamount_paid
112024-05-10500
212024-05-20700
322024-06-151000
422024-06-25300
522024-07-02400
632024-05-251000
742024-05-151500

Output

bill_idconsumer_nosettled_onsettlement_status
1500112024-05-20on time
2500112024-07-02late
3500272024-05-25late
450034NULLunsettled
550042NULLunsettled

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_id

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