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.

Recovery not opened

subrogation_missed unit: claims operator detector

We paid a claim where another, insured party was at fault, and nobody has opened a recovery against their insurer. Money we could get back.

recoveriesHead of Recoveries · recoveries v1

A claim marked third party at fault with the third party's insurer recorded, first paid at least days_after_payment ago, and no open recovery as of now. A claim where only the police report says the other party was at fault is not caught by this version: the flag on the claim decides.

latest change: First version.

  • fail count_betweenobserved 0, expected between 20.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 paid AS (
  SELECT claim_id, min(paid_at) AS first_paid_at, sum(amount) AS paid FROM payments
  WHERE paid_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, c.policy_id,
       f.first_paid_at + INTERVAL (14) DAY AS started_at,
       CASE WHEN f.paid >= 10000 THEN 'medium' ELSE 'low' END AS severity,
       json_object('claim_no', c.claim_no, 'third_party_insurer', c.third_party_insurer,
                   'first_paid_at', f.first_paid_at, 'paid', round(f.paid, 2),
                   'days_since_payment', date_diff('day', f.first_paid_at, TIMESTAMP 'Mon 17:00:00'),
                   'peril', c.peril, 'cause', c.cause) AS evidence
FROM claims c
JOIN paid f ON f.claim_id = c.id
WHERE c.third_party_at_fault AND c.third_party_insurer IS NOT NULL
  AND f.first_paid_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL (14) DAY
  AND NOT EXISTS (SELECT 1 FROM recoveries r WHERE r.claim_id = c.id AND r.opened_at <= TIMESTAMP 'Mon 17:00:00' AND r.status = 'open')
ORDER BY started_at, c.id

Parameters

change one, see what changes today, then save it as a new version with a note
now 14 · used by recoveries

History

append-only; nothing is edited in place
  1. definitionHead of Recoveries recoveries v1 First version.