Definitions
The claims terms the operator, its detectors and Ask all run on. Each has an owner, a version, SQL and tests. Edits make a new version.Claim waiting for an adjuster
unassigned_claim unit: claims operator detectorA claim that has been on the claims desk for longer than the desk's service level without an adjuster. The desk works Monday to Friday, 09:00 to 17:00; a claim reported outside those hours reaches the desk when it next opens.
claimsHead of Claims Operations · claims
v2
Reported at most 14 days ago, open as of now, no adjuster as of now, and more than sla_minutes since the claim reached the desk (the next desk opening after it was reported). clean_claim in the evidence uses the claims meaning of clean claim: at or under 1,500, no injury, no police-report peril, policy in force for 30 days or more.
latest change: The clock starts when the desk opens, not when the claim is reported; weekend claims start Monday 09:00.
- pass storm_monday_backlogobserved 482, expected between 300.0 and 2500.0
- pass quiet_tuesdayobserved 1, expected between 0.0 and 60.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH desk AS (
SELECT c.*,
CASE
WHEN isodow(c.reported_at) IN (6, 7)
THEN date_trunc('week', c.reported_at) + INTERVAL 7 DAY + INTERVAL 9 HOUR
WHEN hour(c.reported_at) < 9 THEN date_trunc('day', c.reported_at) + INTERVAL 9 HOUR
WHEN hour(c.reported_at) >= 17 AND isodow(c.reported_at) = 5
THEN date_trunc('day', c.reported_at) + INTERVAL 3 DAY + INTERVAL 9 HOUR
WHEN hour(c.reported_at) >= 17 THEN date_trunc('day', c.reported_at) + INTERVAL 1 DAY + INTERVAL 9 HOUR
ELSE c.reported_at
END AS desk_from
FROM claims c
WHERE c.reported_at <= TIMESTAMP 'Mon 17:00:00' AND c.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 14 DAY
)
SELECT 'claim' AS subject_type, d.id AS subject_id, d.id AS claim_id, d.policy_id,
d.desk_from + INTERVAL (60) MINUTE AS started_at,
CASE WHEN d.injury OR d.initial_estimate > 60000 THEN 'high' ELSE 'medium' END AS severity,
json_object(
'claim_no', d.claim_no, 'line', d.line, 'peril', d.peril, 'cause', d.cause,
'reported_at', d.reported_at, 'desk_from', d.desk_from,
'minutes_on_desk', date_diff('minute', d.desk_from, TIMESTAMP 'Mon 17:00:00'),
'sla_minutes', 60, 'initial_estimate', d.initial_estimate, 'injury', d.injury,
'storm', d.storm_id IS NOT NULL, 'region', r.name, 'region_id', d.region_id,
'policy_age_days', date_diff('day', p.first_start, CAST(d.loss_at AS DATE)),
'clean_claim', d.initial_estimate <= 1500 AND NOT d.injury
AND d.peril NOT IN ('theft', 'burglary', 'vandalism', 'hit_and_run')
AND date_diff('day', p.first_start, CAST(d.loss_at AS DATE)) >= 30
) AS evidence
FROM desk d
JOIN policies p ON p.id = d.policy_id
JOIN regions r ON r.id = d.region_id
WHERE d.desk_from + INTERVAL (60) MINUTE <= TIMESTAMP 'Mon 17:00:00'
AND (d.assigned_at IS NULL OR d.assigned_at > TIMESTAMP 'Mon 17:00:00')
AND (d.closed_at IS NULL OR d.closed_at > TIMESTAMP 'Mon 17:00:00')
ORDER BY started_at, d.id
Parameters
change one, see what changes today, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionHead of Claims Operations claims v2 The clock starts when the desk opens, not when the claim is reported; weekend claims start Monday 09:00.
- definitionHead of Claims Operations claims v1 First version: one hour after reporting.