Consumers With Repeated Late Payment Fees

Medium
511.

Consumers With Repeated Late Payment Fees

MediumJoinsAggregation

MySQL

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

ColumnType
bill_idint
consumer_noint
due_datedate
amountint

Primary key: bill_id.

One electricity bill of one consumer.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint
late_feeint

Primary key: payment_id.

late_fee is 0 for a payment made on time. Every bill_id is in Bill.

Example

The rows must come back in the order shown.

Bill

bill_idconsumer_nodue_dateamount
1500112024-01-201200
2500112024-02-201300
3500112024-03-201100
4500272024-01-20900
5500272024-02-20950
6500342023-12-202000
7500342024-02-202100
8500422024-02-20700

Payment

payment_idbill_idpaid_onamount_paidlate_fee
112024-01-25120024
222024-02-28130026
332024-03-1811000
442024-01-2990025
552024-02-2695025
662023-12-28200040
772024-02-24210042
882024-02-2270014

Output

consumer_nolate_paymentstotal_late_fee
50011250
50027250
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.

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

ColumnType
bill_idint
consumer_noint
due_datedate
amountint

Primary key: bill_id.

One electricity bill of one consumer.

Table: Payment

ColumnType
payment_idint
bill_idint
paid_ondate
amount_paidint
late_feeint

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_idconsumer_nodue_dateamount
1500112024-01-201200
2500112024-02-201300
3500112024-03-201100
4500272024-01-20900
5500272024-02-20950
6500342023-12-202000
7500342024-02-202100
8500422024-02-20700

Payment

payment_idbill_idpaid_onamount_paidlate_fee
112024-01-25120024
222024-02-28130026
332024-03-1811000
442024-01-2990025
552024-02-2695025
662023-12-28200040
772024-02-24210042
882024-02-2270014

Output

consumer_nolate_paymentstotal_late_fee
50011250
50027250

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_no

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