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.Estimate far above the benchmark
estimate_above_benchmark unit: claims operator detectorA repair estimate at least twice the benchmark for that kind of damage, with no engineer's report that backs it up. Complex damage costs more, and complex claims get an engineer; an estimate this high without one needs a second look.
claimsHead of Claims Operations · claims
v2
An open claim whose latest estimate, received in the last window_days, is at least ratio times the benchmark p50 for its line, peril and band, and which has no engineer's report within 10% of the estimate. A motor estimate at or past 70% of the vehicle's value belongs to the total-loss check, not this one.
latest change: Estimates past the total-loss line are left to total_loss_unflagged; they were counted twice.
- pass count_betweenobserved 40, expected between 3.0 and 120.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 40}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH est AS (
SELECT claim_id, arg_max(id, received_at) AS document_id, arg_max(amount, received_at) AS amount,
max(received_at) AS received_at, arg_max(vendor_id, received_at) AS vendor_id,
arg_max(ref, received_at) AS ref
FROM documents WHERE kind = 'estimate' AND received_at <= TIMESTAMP 'Mon 17:00:00'
AND received_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL (45) DAY
GROUP BY claim_id
),
eng AS (SELECT claim_id, max(amount) AS amount FROM documents
WHERE kind = 'engineer_report' AND received_at <= TIMESTAMP 'Mon 17:00:00' GROUP BY claim_id)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, e.vendor_id, e.document_id,
e.received_at AS started_at,
CASE WHEN e.amount >= 3 * b.p50 THEN 'high' ELSE 'medium' END AS severity,
json_object('claim_no', c.claim_no, 'estimate', e.amount, 'estimate_ref', e.ref, 'vendor', v.name,
'line', c.line, 'peril', c.peril, 'band', c.band, 'benchmark_p50', b.p50,
'benchmark_p90', b.p90, 'benchmark_version', b.version,
'ratio_to_p50', round(e.amount / b.p50, 2), 'engineer_report', g.amount IS NOT NULL) AS evidence
FROM est e
JOIN claims c ON c.id = e.claim_id
JOIN benchmarks b ON b.line = c.line AND b.peril = c.peril AND b.band = c.band
JOIN policies p ON p.id = c.policy_id
LEFT JOIN eng g ON g.claim_id = c.id
LEFT JOIN vendors v ON v.id = e.vendor_id
WHERE e.amount >= 2.0 * b.p50
AND (g.amount IS NULL OR abs(g.amount / e.amount - 1) > 0.10)
AND NOT (c.line = 'motor' AND e.amount >= 0.70 * p.vehicle_value)
AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
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 Estimates past the total-loss line are left to total_loss_unflagged; they were counted twice.
- definitionHead of Claims Operations claims v1 First version.