Leases Ending Within the Next 30 Days

Easy
355.

Leases Ending Within the Next 30 Days

EasyDatesBasics

MySQL

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

ColumnType
lease_idint
flat_novarchar
tenant_namevarchar
start_datedate
end_datedate
monthly_rentint

Primary key: lease_id.

One row per rental agreement; end_date is the last day of the lease.

Example

The rows must come back in the order shown.

Lease

lease_idflat_notenant_namestart_dateend_datemonthly_rent
1A-302Kavya Menon2024-03-012025-02-2828000
2B-1204Rahul Verma2024-03-012025-03-0135000
3C-701Ira Joshi2024-04-152025-03-1422000
4A-105Dev Patel2024-04-012025-03-3141000
5B-801Simran Bose2024-05-012025-04-0126500
6C-202Karan Reddy2024-04-152025-03-1424000

Output

lease_idflat_noend_datedays_left
2B-12042025-03-010
3C-7012025-03-1413
6C-2022025-03-1413
4A-1052025-03-3130
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.

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

ColumnType
lease_idint
flat_novarchar
tenant_namevarchar
start_datedate
end_datedate
monthly_rentint

Primary key: lease_id.

One row per rental agreement; end_date is the last day of the lease.

Examples

Example 1

Lease

lease_idflat_notenant_namestart_dateend_datemonthly_rent
1A-302Kavya Menon2024-03-012025-02-2828000
2B-1204Rahul Verma2024-03-012025-03-0135000
3C-701Ira Joshi2024-04-152025-03-1422000
4A-105Dev Patel2024-04-012025-03-3141000
5B-801Simran Bose2024-05-012025-04-0126500
6C-202Karan Reddy2024-04-152025-03-1424000

Output

lease_idflat_noend_datedays_left
2B-12042025-03-010
3C-7012025-03-1413
6C-2022025-03-1413
4A-1052025-03-3130

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_id

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

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