Adjust claims desk · Quillmark Mutual
World clock Mon 17:00:00 CET paused · 60×

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 detector

A 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 note
now 2.0 · used by claims
now 45 · used by claims

History

append-only; nothing is edited in place
  1. definitionHead of Claims Operations claims v2 Estimates past the total-loss line are left to total_loss_unflagged; they were counted twice.
  2. definitionHead of Claims Operations claims v1 First version.