lettings · v1 · Head of Lettings
7
homes at Mon 20:00
An empty spell (unit_vacancies) that began at least vacancy_days before now and was not let before now; homes with status renovation are left out. Listed = a listing exists from before now.
lettings · changed 2026-11-01: First version.
Tests
- count_between min 1.0 max 60.0
- unique on unit_id
- detector_shape
SQL
SELECT 'unit' AS subject_type, v.unit_id AS subject_id, v.unit_id, v.building_id, v.id AS vacancy_id,
v.vacant_from + INTERVAL ({vacancy_days}) DAY AS started_at,
CASE WHEN v.vacant_from <= {now} - INTERVAL 90 DAY THEN 'medium' ELSE 'low' END AS severity,
rb.band_low, rb.band_mid, rb.band_high,
(v.listed_at IS NOT NULL AND v.listed_at <= {now}) AS listed,
json_object('vacant_from', v.vacant_from, 'days_vacant', date_diff('day', v.vacant_from, {now}),
'listed_at', CASE WHEN v.listed_at <= {now} THEN v.listed_at END,
'listed_rent', CASE WHEN v.listed_at <= {now} THEN v.listed_rent END,
'bedrooms', u.bedrooms, 'unit_label', u.label, 'building', b.name,
'rent_lost_so_far', round(rb.band_mid * date_diff('day', v.vacant_from, {now}) / 30.0, 0))
AS evidence
FROM unit_vacancies v
JOIN units u ON u.id = v.unit_id
JOIN buildings b ON b.id = v.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 v.vacant_from <= {now} - INTERVAL ({vacancy_days}) DAY
AND (v.let_at IS NULL OR v.let_at > {now})
AND u.status <> 'renovation'
ORDER BY started_at, v.unit_id