Definitions / priority_customer_in_outage
Priority customer in an outage
detector, runs realtimeA premise on the medical priority register that is off supply in an unplanned outage: its own meter sent a last gasp and no restore, or an open fault ticket covers its transformer.
customer · Head of Customer Operations · version 1
Registered priority premises whose meter's latest last gasp (within lookback_hours) is at least sustained_minutes old with no restore after it, unless at least half of the meters on the same transformer that went off within two minutes of it have come back (a lost restore message, not a dark premise); plus registered premises on a transformer covered by an open unplanned ticket (the agent's own for agent_ticket_hold_hours) whose meter has not restored since the ticket opened. Planned work is left out: planning calls priority customers before the day.
Latest change: First version.
SQL
WITH reg AS (
SELECT m.id AS meter_id, m.premise_id, m.feeder_id, m.transformer_id, t.position, r.category
FROM priority_register r
JOIN meters m ON m.premise_id = r.premise_id
JOIN transformers t ON t.id = m.transformer_id
WHERE r.registered_at <= {now}
),
gasps AS (
SELECT e.meter_id, max(e."at") AS gasp_at FROM meter_events e
WHERE e.type = 'last_gasp' AND e."at" > {now} - INTERVAL ({lookback_hours}) HOUR AND e."at" <= {now}
GROUP BY e.meter_id
),
backs AS (
SELECT e.meter_id, max(e."at") AS back_at FROM meter_events e
WHERE e.type = 'power_restore' AND e."at" > {now} - INTERVAL ({lookback_hours}) HOUR AND e."at" <= {now}
GROUP BY e.meter_id
),
planned AS (
SELECT DISTINCT t.id AS transformer_id
FROM planned_outages p
JOIN transformers t ON t.feeder_id = p.feeder_id
AND (p.transformer_id = t.id OR (p.transformer_id IS NULL AND t.position >= p.from_position))
WHERE p.cancelled_at IS NULL AND p.start_at - INTERVAL 30 MINUTE <= {now} AND p.end_at + INTERVAL 60 MINUTE >= {now}
),
own AS (
SELECT r.meter_id, g.gasp_at
FROM reg r JOIN gasps g ON g.meter_id = r.meter_id
LEFT JOIN backs b ON b.meter_id = r.meter_id
WHERE (b.back_at IS NULL OR b.back_at < g.gasp_at)
AND g.gasp_at <= {now} - INTERVAL ({sustained_minutes}) MINUTE
AND r.transformer_id NOT IN (SELECT transformer_id FROM planned)
),
neighbours AS (
SELECT o.meter_id, count(*) AS went_off,
count(*) FILTER (WHERE b.back_at > g.gasp_at) AS came_back
FROM own o
JOIN reg r ON r.meter_id = o.meter_id
JOIN meters m ON m.transformer_id = r.transformer_id AND m.id <> o.meter_id
JOIN gasps g ON g.meter_id = m.id AND abs(date_diff('second', g.gasp_at, o.gasp_at)) <= 120
LEFT JOIN backs b ON b.meter_id = m.id
GROUP BY o.meter_id
),
tickets AS (
SELECT r.meter_id, min(o.opened_at) AS opened_at, min(o.id) AS outage_id
FROM reg r
JOIN outages o ON o.feeder_id = r.feeder_id
AND (o.transformer_id = r.transformer_id OR (o.transformer_id IS NULL AND r.position >= o.from_position))
LEFT JOIN backs b ON b.meter_id = r.meter_id
WHERE o.opened_at <= {now} AND coalesce(o.status, '') <> 'withdrawn' AND o.source <> 'planned'
AND (o.restored_at IS NULL OR o.restored_at > {now})
AND (o.source <> 'agent' OR o.opened_at > {now} - INTERVAL ({agent_ticket_hold_hours}) HOUR)
AND (b.back_at IS NULL OR b.back_at < o.opened_at)
GROUP BY r.meter_id
),
dark AS (
SELECT coalesce(o.meter_id, t.meter_id) AS meter_id, o.gasp_at, t.opened_at, t.outage_id,
coalesce(n.went_off, 0) AS went_off, coalesce(n.came_back, 0) AS came_back
FROM own o
FULL OUTER JOIN tickets t ON t.meter_id = o.meter_id
LEFT JOIN neighbours n ON n.meter_id = o.meter_id
)
SELECT 'meter' AS subject_type, CAST(d.meter_id AS TEXT) AS subject_id, d.meter_id, r.premise_id, r.feeder_id,
r.transformer_id, least(d.gasp_at, d.opened_at) AS started_at, 'critical' AS severity,
json_object(
'category', r.category,
'feeder_id', r.feeder_id,
'transformer_id', r.transformer_id,
'premise_id', r.premise_id,
'account_no', p.account_no,
'last_gasp_at', d.gasp_at,
'ticket_opened_at', d.opened_at,
'outage_id', d.outage_id,
'neighbours_off', d.went_off,
'neighbours_back', d.came_back,
'minutes_off', round(date_diff('second', least(d.gasp_at, d.opened_at), {now}) / 60.0, 1)
) AS evidence
FROM dark d
JOIN reg r ON r.meter_id = d.meter_id
JOIN premises p ON p.id = r.premise_id
WHERE d.opened_at IS NOT NULL OR d.came_back * 2 < d.went_off OR d.went_off = 0
ORDER BY started_at, d.meter_id
Tests
| meaning | test | result | observed | expected |
|---|---|---|---|---|
| customer | count_between | pass | 27 | between 0.0 and 120.0 |
| customer | storm_evening | pass | 35 | between 5.0 and 150.0 |
| customer | unique | pass | {'duplicates': 0} | meter_id unique |
| customer | detector_shape | pass | {'missing_columns': [], 'bad_rows': 0, 'rows': 27} | columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object |