Electricity Bills Paid After the Due Date

Easy
504.

Electricity Bills Paid After the Due Date

EasyJoinsDates

MySQL

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

ColumnType
bill_idint
consumer_noint
bill_monthdate
amountint
due_datedate

Primary key: bill_id.

One monthly electricity bill; bill_month is the first day of the month billed, amount is in rupees.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint

Primary key: payment_id.

A bill has at most one payment, and every bill_id here is in Bill.

Example

The rows must come back in the order shown.

Bill

bill_idconsumer_nobill_monthamountdue_date
1500112024-05-0112402024-05-20
2500112024-06-0113852024-06-20
3500272024-05-018602024-05-20
4500272024-06-019102024-06-20
5500342024-06-0122102024-06-20
6500342024-05-0119902024-05-20

Payment

payment_idbill_idpaid_onamount_paid
112024-05-181240
222024-06-251385
332024-05-20860
442024-06-27910
562024-06-011990

Output

bill_idconsumer_nodue_datepaid_ondays_late
6500342024-05-202024-06-0112
4500272024-06-202024-06-277
2500112024-06-202024-06-255
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 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

ColumnType
bill_idint
consumer_noint
bill_monthdate
amountint
due_datedate

Primary key: bill_id.

One monthly electricity bill; bill_month is the first day of the month billed, amount is in rupees.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint

Primary key: payment_id.

A bill has at most one payment, and every bill_id here is in Bill.

Examples

Example 1

Bill

bill_idconsumer_nobill_monthamountdue_date
1500112024-05-0112402024-05-20
2500112024-06-0113852024-06-20
3500272024-05-018602024-05-20
4500272024-06-019102024-06-20
5500342024-06-0122102024-06-20
6500342024-05-0119902024-05-20

Payment

payment_idbill_idpaid_onamount_paid
112024-05-181240
222024-06-251385
332024-05-20860
442024-06-27910
562024-06-011990

Output

bill_idconsumer_nodue_datepaid_ondays_late
6500342024-05-202024-06-0112
4500272024-06-202024-06-277
2500112024-06-202024-06-255

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_id

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