Monthly Rent Collection: On Time, Late and Unpaid

Medium
359.

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

ColumnType
invoice_idint
lease_idint
due_datedate
amountint
paid_ondate

Primary key: invoice_id.

One invoice per lease per month; paid_on is NULL until the rent is received.

Example

The rows must come back in the order shown.

RentInvoice

invoice_idlease_iddue_dateamountpaid_on
1112025-01-05280002025-01-04
2122025-01-05350002025-01-10
3132025-01-01220002025-01-09
4112025-02-05280002025-02-05
5122025-02-0535000NULL
6132025-02-01220002025-02-07
7142025-03-0141000NULL

Output

due_monthinvoiceson_timelateunpaidcollected
2025-01321085000
2025-02311150000
2025-0310010
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.

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

ColumnType
invoice_idint
lease_idint
due_datedate
amountint
paid_ondate

Primary key: invoice_id.

One invoice per lease per month; paid_on is NULL until the rent is received.

Examples

Example 1

RentInvoice

invoice_idlease_iddue_dateamountpaid_on
1112025-01-05280002025-01-04
2122025-01-05350002025-01-10
3132025-01-01220002025-01-09
4112025-02-05280002025-02-05
5122025-02-0535000NULL
6132025-02-01220002025-02-07
7142025-03-0141000NULL

Output

due_monthinvoiceson_timelateunpaidcollected
2025-01321085000
2025-02311150000
2025-0310010

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_month

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

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