Monthly Rent Collection: On Time, Late and Unpaid
A rental manager tracks every rent invoice. Tenants get a 5-day grace period: an invoice paid on or before due_date + 5 days is on time, one paid after that…
- Difficulty: Medium
- Topics: Dates, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #359
Problem statement
A rental manager tracks every rent invoice. Tenants get a 5-day grace period: an invoice paid on or before due_date + 5 days is on time, one paid after that is late, and one with paid_on NULL is unpaid.
For each month of due_date (as 'YYYY-MM'), return due_month, invoices, on_time, late, unpaid and collected (the total amount of invoices that were paid, on time or late — 0 if none). Order by due_month.
Tables
Table: RentInvoice
| Column | Type |
|---|---|
| invoice_id | int |
| lease_id | int |
| due_date | date |
| amount | int |
| paid_on | date |
Primary key: invoice_id.
One invoice per lease per month; paid_on is NULL until the rent is received.
Examples
Example 1
RentInvoice
| invoice_id | lease_id | due_date | amount | paid_on |
|---|---|---|---|---|
| 1 | 11 | 2025-01-05 | 28000 | 2025-01-04 |
| 2 | 12 | 2025-01-05 | 35000 | 2025-01-10 |
| 3 | 13 | 2025-01-01 | 22000 | 2025-01-09 |
| 4 | 11 | 2025-02-05 | 28000 | 2025-02-05 |
| 5 | 12 | 2025-02-05 | 35000 | NULL |
| 6 | 13 | 2025-02-01 | 22000 | 2025-02-07 |
| 7 | 14 | 2025-03-01 | 41000 | NULL |
Output
| due_month | invoices | on_time | late | unpaid | collected |
|---|---|---|---|---|---|
| 2025-01 | 3 | 2 | 1 | 0 | 85000 |
| 2025-02 | 3 | 1 | 1 | 1 | 50000 |
| 2025-03 | 1 | 0 | 0 | 1 | 0 |
How to solve Monthly Rent Collection: On Time, Late and Unpaid
Bucket the invoices by the month they fall due — DATE_FORMAT(due_date, '%Y-%m') gives a sortable '2025-01' label — and classify each invoice inside the aggregates with conditional aggregation: one SUM(CASE …) per category. The grace period is DATE_ADD(due_date, INTERVAL 5 DAY); paid on or before it is on time, after it is late.
NULLs do the right thing without help: for an unpaid invoice, paid_on <= … and paid_on > … are both unknown, so it lands in neither, and the explicit paid_on IS NULL count picks it up. The boundary counts too: invoice 4 was paid on the due date and invoice 3 on day 8 after a due date of the 1st — late. collected sums only paid invoices and COALESCE turns a month with nothing received into 0.
DATEDIFF(paid_on, due_date) <= 5 is the same test, and COUNT(*) - COUNT(paid_on) counts NULLs. Classifying in a derived table first, then counting statuses, reads well for many categories. One pass and one sort.
Reference solution (MySQL)
SELECT DATE_FORMAT(due_date, '%Y-%m') AS due_month,
COUNT(*) AS invoices,
SUM(CASE WHEN paid_on <= DATE_ADD(due_date, INTERVAL 5 DAY) THEN 1 ELSE 0 END) AS on_time,
SUM(CASE WHEN paid_on > DATE_ADD(due_date, INTERVAL 5 DAY) THEN 1 ELSE 0 END) AS late,
SUM(CASE WHEN paid_on IS NULL THEN 1 ELSE 0 END) AS unpaid,
COALESCE(SUM(CASE WHEN paid_on IS NOT NULL THEN amount END), 0) AS collected
FROM RentInvoice
GROUP BY DATE_FORMAT(due_date, '%Y-%m')
ORDER BY due_monthAnother way
SELECT LEFT(due_date, 7) AS due_month, COUNT(*) AS invoices, SUM(IF(DATEDIFF(paid_on, due_date) <= 5, 1, 0)) AS on_time, SUM(IF(DATEDIFF(paid_on, due_date) > 5, 1, 0)) AS late, COUNT(*) - COUNT(paid_on) AS unpaid, IFNULL(SUM(IF(paid_on IS NULL, 0, amount)), 0) AS collected FROM RentInvoice GROUP BY LEFT(due_date, 7) ORDER BY 1Another way
SELECT due_month, COUNT(*) AS invoices, SUM(CASE WHEN status = 'on_time' THEN 1 ELSE 0 END) AS on_time, SUM(CASE WHEN status = 'late' THEN 1 ELSE 0 END) AS late, SUM(CASE WHEN status = 'unpaid' THEN 1 ELSE 0 END) AS unpaid, SUM(CASE WHEN status = 'unpaid' THEN 0 ELSE amount END) AS collected FROM (SELECT SUBSTRING(due_date, 1, 7) AS due_month, amount, CASE WHEN paid_on IS NULL THEN 'unpaid' WHEN DATEDIFF(paid_on, due_date) > 5 THEN 'late' ELSE 'on_time' END AS status FROM RentInvoice) t GROUP BY due_month ORDER BY due_month← Vacancy Gaps Between Tenants of a Flat · Flats Owing More Than Their Wing's Average →