Leases Ending Within the Next 30 Days — SQL Dates Problem
On 2025-03-01 a property manager starts renewal calls for every lease that ends between 2025-03-01 and 30 days later (2025-03-31), both inclusive.
- Difficulty: Easy
- Topics: Dates, Basics
- Dialect: MySQL
- Problem: #355
Problem statement
On 2025-03-01 a property manager starts renewal calls for every lease that ends between 2025-03-01 and 30 days later (2025-03-31), both inclusive.
Return lease_id, flat_no, end_date and days_left (days from 2025-03-01 to end_date, 0 for a lease ending that day) for those leases, ordered by end_date, ties by lease_id. Leases that already ended before 2025-03-01 are not called.
Tables
Table: Lease
| Column | Type |
|---|---|
| lease_id | int |
| flat_no | varchar |
| tenant_name | varchar |
| start_date | date |
| end_date | date |
| monthly_rent | int |
Primary key: lease_id.
One row per rental agreement; end_date is the last day of the lease.
Examples
Example 1
Lease
| lease_id | flat_no | tenant_name | start_date | end_date | monthly_rent |
|---|---|---|---|---|---|
| 1 | A-302 | Kavya Menon | 2024-03-01 | 2025-02-28 | 28000 |
| 2 | B-1204 | Rahul Verma | 2024-03-01 | 2025-03-01 | 35000 |
| 3 | C-701 | Ira Joshi | 2024-04-15 | 2025-03-14 | 22000 |
| 4 | A-105 | Dev Patel | 2024-04-01 | 2025-03-31 | 41000 |
| 5 | B-801 | Simran Bose | 2024-05-01 | 2025-04-01 | 26500 |
| 6 | C-202 | Karan Reddy | 2024-04-15 | 2025-03-14 | 24000 |
Output
| lease_id | flat_no | end_date | days_left |
|---|---|---|---|
| 2 | B-1204 | 2025-03-01 | 0 |
| 3 | C-701 | 2025-03-14 | 13 |
| 6 | C-202 | 2025-03-14 | 13 |
| 4 | A-105 | 2025-03-31 | 30 |
How to solve Leases Ending Within the Next 30 Days
The window is fixed: from the call date to 30 days after it, both ends included. end_date BETWEEN '2025-03-01' AND DATE_ADD('2025-03-01', INTERVAL 30 DAY) expresses it directly — the dates are stored as fixed-format text, so the comparison orders them correctly, and DATE_ADD saves you from counting March's days by hand.
days_left is DATEDIFF(end_date, '2025-03-01'), which is 0 for lease 2 ending on the call date itself and 30 for lease 4 ending on the 31st. Lease 1 ended the day before and lease 5 one day too late; both are outside.
Filtering on the computed difference (BETWEEN 0 AND 30) gives the same rows but cannot use an index on end_date; a half-open range >= '2025-03-01' AND < '2025-04-01' can, and is the most robust form when the column might hold times. Two leases ending on the same day are ordered by id.
Reference solution (MySQL)
SELECT lease_id, flat_no, end_date,
DATEDIFF(end_date, '2025-03-01') AS days_left
FROM Lease
WHERE end_date BETWEEN '2025-03-01' AND DATE_ADD('2025-03-01', INTERVAL 30 DAY)
ORDER BY end_date, lease_idAnother way
SELECT lease_id, flat_no, end_date, DATEDIFF(end_date, '2025-03-01') AS days_left FROM Lease WHERE DATEDIFF(end_date, '2025-03-01') BETWEEN 0 AND 30 ORDER BY end_date, lease_idAnother way
SELECT lease_id, flat_no, end_date, DATEDIFF(end_date, '2025-03-01') AS days_left FROM Lease WHERE end_date >= '2025-03-01' AND end_date < '2025-04-01' ORDER BY 3, 1← Society Maintenance Dues Collected per Wing · Costliest Rental Listing in Each City →