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.Stalled claim
stalled_claim unit: claims operator detector no canonical variant: Ask returns all 2An open claim nobody has moved for too long. Claims Operations and Customer Contact count it differently: one looks at the file (nothing done on it), the other at the customer (nobody has spoken to the claimant).
An open, assigned claim with nothing done on the file for stalled_days or more: no claim event (any event except the first report) and no outbound call, email or text. A file waiting for documents from the claimant (an awaiting_claimant event with no reply since) is not stalled. The operator's detector uses this meaning.
latest change: Files waiting for the claimant no longer count; the clock restarts when the claimant replies.
- pass count_betweenobserved 0, expected between 0.0 and 400.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 activity AS (
SELECT claim_id, max("at") AS last_at, arg_max(type, "at") AS last_type FROM claim_events
WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND type <> 'reported' GROUP BY claim_id
UNION ALL
SELECT claim_id, max("at"), 'outbound ' || arg_max(channel, "at") FROM contacts
WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND direction = 'outbound' GROUP BY claim_id
),
last AS (SELECT claim_id, max(last_at) AS last_at, arg_max(last_type, last_at) AS last_type FROM activity GROUP BY claim_id),
waiting AS (
SELECT claim_id, arg_max(type, "at") AS state FROM claim_events
WHERE type IN ('awaiting_claimant', 'claimant_replied') AND "at" <= TIMESTAMP 'Mon 17:00:00' GROUP BY claim_id
),
caseload AS (
SELECT adjuster_id, count(*) AS open_claims FROM claims
WHERE assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (closed_at IS NULL OR closed_at > TIMESTAMP 'Mon 17:00:00') GROUP BY adjuster_id
)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.policy_id, c.adjuster_id,
l.last_at + INTERVAL (10) DAY AS started_at,
CASE WHEN a.left_at <= TIMESTAMP 'Mon 17:00:00' THEN 'high' ELSE 'medium' END AS severity,
json_object(
'claim_no', c.claim_no, 'line', c.line, 'peril', c.peril, 'last_activity_at', l.last_at,
'last_activity', l.last_type, 'days_quiet', date_diff('day', l.last_at, TIMESTAMP 'Mon 17:00:00'),
'stalled_days', 10, 'adjuster', a.name, 'team_id', c.team_id,
'adjuster_status', CASE WHEN a.left_at <= TIMESTAMP 'Mon 17:00:00' THEN 'left the company'
WHEN a.leave_from <= TIMESTAMP 'Mon 17:00:00' AND a.leave_to > TIMESTAMP 'Mon 17:00:00' THEN 'on leave'
ELSE 'working' END,
'adjuster_open_claims', coalesce(k.open_claims, 0), 'adjuster_caseload_cap', a.caseload_cap
) AS evidence
FROM claims c
JOIN last l ON l.claim_id = c.id
LEFT JOIN waiting w ON w.claim_id = c.id
LEFT JOIN adjusters a ON a.id = c.adjuster_id
LEFT JOIN caseload k ON k.adjuster_id = c.adjuster_id
WHERE c.assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
AND l.last_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL (10) DAY
AND coalesce(w.state, '') <> 'awaiting_claimant'
ORDER BY started_at, c.id
An open, assigned claim whose claimant nobody has spoken to (a call, email or text either way) for stalled_days or more, whatever is happening on the file. Files waiting for the claimant count too: that is when customers feel forgotten.
latest change: First version.
- pass count_betweenobserved 1550, expected between 100.0 and 6000.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 1550}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH spoken AS (
SELECT claim_id, max("at") AS last_at FROM contacts
WHERE "at" <= TIMESTAMP 'Mon 17:00:00' AND party = 'claimant' GROUP BY claim_id
)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.adjuster_id,
s.last_at + INTERVAL (10) DAY AS started_at, 'low' AS severity,
json_object('claim_no', c.claim_no, 'last_claimant_contact_at', s.last_at,
'days_without_contact', date_diff('day', s.last_at, TIMESTAMP 'Mon 17:00:00')) AS evidence
FROM claims c
JOIN spoken s ON s.claim_id = c.id
WHERE c.assigned_at <= TIMESTAMP 'Mon 17:00:00' AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
AND s.last_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL (10) DAY
ORDER BY started_at, c.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 Files waiting for the claimant no longer count; the clock restarts when the claimant replies.
- definitionHead of Claims Operations claims v1 First version: 14 days.
- definitionHead of Customer Contact service v1 First version.