Keysproperty operations Corbelstone Residential

Long vacancy

A home empty for longer than vacancy_days and not under renovation. The evidence says whether it is listed, at what rent, and the band it should be listed in.

Definition
long_vacancy

Params: vacancy_days = 45 · evaluated at Mon 20:00

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