lettings · v1 · Head of Lettings
0
leases at Mon 20:00
Fixed term ends between now and deadline_days from now; no offer sent before now; no notice from the tenant before now; renewal_eligible. Band price = current rent times indexation, rounded to 5 euros and kept inside the band in force now (top floors may go up to floor_premium above the band's top).
lettings · changed 2026-11-01: First version.
Tests
- count_between min 1.0 max 120.0
- unique on lease_id
- detector_shape
SQL
SELECT 'lease' AS subject_type, l.id AS subject_id, l.id AS lease_id, l.unit_id, l.building_id, l.tenant_id,
l.end_date - INTERVAL ({deadline_days}) DAY AS started_at,
CASE WHEN l.end_date - {now} < INTERVAL 30 DAY THEN 'high' ELSE 'medium' END AS severity,
rb.band_low, rb.band_mid,
round(rb.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END, 2)
AS band_high,
least(greatest(round(l.monthly_rent * {indexation} / 5) * 5, ceil(rb.band_low / 5) * 5),
floor(rb.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END
/ 5) * 5) AS band_price,
json_object('end_date', l.end_date, 'days_to_end', date_diff('day', {now}, l.end_date),
'monthly_rent', l.monthly_rent, 'bedrooms', u.bedrooms, 'unit_label', u.label,
'building', b.name, 'band_version', rb.version, 'top_floor', u.floor >= b.floors - {top_floors},
'deadline_days', {deadline_days}) AS evidence
FROM leases l
JOIN units u ON u.id = l.unit_id
JOIN buildings b ON b.id = l.building_id
JOIN rent_bands rb ON rb.building_id = u.building_id AND rb.bedrooms = u.bedrooms
AND rb.valid_from <= {now} AND (rb.valid_to IS NULL OR rb.valid_to > {now})
WHERE l.start_date <= {now}
AND l.end_date + INTERVAL 1 DAY > {now}
AND l.end_date <= {now} + INTERVAL ({deadline_days}) DAY
AND l.renewal_eligible
AND (l.notice_given_at IS NULL OR l.notice_given_at > {now})
AND (l.moved_out_at IS NULL OR l.moved_out_at > {now})
AND NOT EXISTS (SELECT 1 FROM renewal_offers o WHERE o.lease_id = l.id AND o.sent_at <= {now}
AND o.status <> 'withdrawn')
ORDER BY started_at, l.id