Keysproperty operations Corbelstone Residential

Renewal offer outside the band

A renewal offer still waiting for the tenant's answer whose rent is outside the band that was in force when it was sent. Homes on the top floors may be offered up to floor_premium above the band's top. Bands moved up on 1 October, so an offer is judged against the band of its own sending date.

Definition
renewal_out_of_band

Params: floor_premium = 0.05, top_floors = 3, indexation = 1.03 · evaluated at Mon 20:00

lettings · v2 · Head of Lettings

0

offers at Mon 20:00

Offer sent before now, no answer before now, offered rent below the band's bottom or above its top (with the top-floor premium) in the band version valid at the offer's sent_at. The evidence carries today's band price for a re-issued offer: band_low and band_high are today's band (the one a re-issue must sit in); offer_band_low and offer_band_high are the band the offer was judged against.

lettings · changed 2026-11-02: band_low and band_high now carry today's band, which the controller checks a re-issued rent against; the band of the offer's sending date moves to offer_band_low and offer_band_high. Which offers are flagged is unchanged.

Tests

  • count_between min 0.0 max 80.0
  • unique on offer_id
  • detector_shape

SQL

WITH offers AS (
  SELECT o.id AS offer_id, o.lease_id, o.unit_id, o.sent_at, o.offered_rent, o.sent_by, l.monthly_rent,
         l.building_id, u.bedrooms, u.floor >= b.floors - {top_floors} AS top_floor,
         then_band.band_low, then_band.band_high AS band_top, then_band.version AS band_version,
         then_band.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END
           AS band_high,
         now_band.band_low AS now_low,
         now_band.band_high * CASE WHEN u.floor >= b.floors - {top_floors} THEN 1 + {floor_premium} ELSE 1 END
           AS now_high, b.name AS building, u.label AS unit_label
  FROM renewal_offers o
  JOIN leases l ON l.id = o.lease_id
  JOIN units u ON u.id = o.unit_id
  JOIN buildings b ON b.id = l.building_id
  JOIN rent_bands then_band ON then_band.building_id = u.building_id AND then_band.bedrooms = u.bedrooms
   AND then_band.valid_from <= o.sent_at AND (then_band.valid_to IS NULL OR then_band.valid_to > o.sent_at)
  JOIN rent_bands now_band ON now_band.building_id = u.building_id AND now_band.bedrooms = u.bedrooms
   AND now_band.valid_from <= {now} AND (now_band.valid_to IS NULL OR now_band.valid_to > {now})
  WHERE o.sent_at <= {now} AND (o.responded_at IS NULL OR o.responded_at > {now}) AND o.status <> 'withdrawn'
)
SELECT 'offer' AS subject_type, offer_id AS subject_id, offer_id, lease_id, unit_id, building_id,
       sent_at AS started_at, 'medium' AS severity, offered_rent,
       now_low AS band_low, round(now_high, 2) AS band_high,
       least(greatest(round(monthly_rent * {indexation} / 5) * 5, ceil(now_low / 5) * 5),
             floor(now_high / 5) * 5) AS band_price,
       json_object('offer_band_low', band_low, 'offer_band_high', round(band_high, 2),
                   'sent_at', sent_at, 'sent_by', sent_by, 'monthly_rent', monthly_rent, 'bedrooms', bedrooms,
                   'top_floor', top_floor, 'band_version', band_version, 'band_top_before_premium', band_top,
                   'deviation_pct', round(100 * CASE WHEN offered_rent > band_high
                                                     THEN offered_rent / band_high - 1
                                                     ELSE offered_rent / band_low - 1 END, 1),
                   'building', building, 'unit_label', unit_label) AS evidence
FROM offers
WHERE offered_rent < band_low - 0.005 OR offered_rent > band_high + 0.005
ORDER BY sent_at, offer_id