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