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.

Duplicate claim

duplicate_claim unit: claims operator detector

The same loss reported twice: a later claim on the same policy for the same peril, with a loss time close to an earlier claim's. The later claim is the duplicate. Paying both is paying once too often.

claimsHead of Claims Operations · claims v2

An open claim reported in the last 30 days, with an earlier claim on the same policy and peril whose loss time is within loss_window_hours and which was reported at most report_window_days before it. An earlier claim already closed as a duplicate does not count. A duplicate filed under a neighbouring peril (storm for flood) is not caught by this version.

latest change: The test for an earlier claim closed as a duplicate no longer drops open claims (it read NULL as false).

  • pass some_in_the_last_monthobserved 33, expected between 3.0 and 120.0
  • pass uniqueobserved {'duplicates': 0}, expected claim_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 33}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
SELECT 'claim' AS subject_type, d.id AS subject_id, d.id AS claim_id, o.id AS original_claim_id,
       d.policy_id, d.reported_at AS started_at,
       CASE WHEN EXISTS (SELECT 1 FROM payments p WHERE p.claim_id IN (d.id, o.id) AND p.requested_at <= TIMESTAMP 'Mon 17:00:00')
            THEN 'high' ELSE 'medium' END AS severity,
       json_object(
         'claim_no', d.claim_no, 'original_claim_no', o.claim_no, 'peril', d.peril,
         'loss_at', d.loss_at, 'original_loss_at', o.loss_at,
         'hours_between_losses', round(abs(date_diff('minute', o.loss_at, d.loss_at)) / 60.0, 1),
         'reported_at', d.reported_at, 'original_reported_at', o.reported_at,
         'channel', d.channel, 'original_channel', o.channel,
         'initial_estimate', d.initial_estimate, 'original_initial_estimate', o.initial_estimate,
         'original_adjuster_id', o.adjuster_id,
         'payments_requested', (SELECT count(*) FROM payments p WHERE p.claim_id = d.id AND p.requested_at <= TIMESTAMP 'Mon 17:00:00')
       ) AS evidence
FROM claims d
JOIN claims o
  ON o.policy_id = d.policy_id AND o.id <> d.id AND o.peril = d.peril
 AND (o.reported_at < d.reported_at OR (o.reported_at = d.reported_at AND o.id < d.id))
 AND abs(date_diff('minute', o.loss_at, d.loss_at)) <= 36 * 60
 AND d.reported_at - o.reported_at <= INTERVAL (14) DAY
WHERE d.reported_at <= TIMESTAMP 'Mon 17:00:00' AND d.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 30 DAY
  AND (d.closed_at IS NULL OR d.closed_at > TIMESTAMP 'Mon 17:00:00')
  AND NOT coalesce(o.outcome = 'duplicate' AND o.closed_at <= TIMESTAMP 'Mon 17:00:00', FALSE)
QUALIFY row_number() OVER (PARTITION BY d.id ORDER BY o.reported_at, o.id) = 1
ORDER BY started_at, d.id

Parameters

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

History

append-only; nothing is edited in place
  1. definitionHead of Claims Operations claims v2 The test for an earlier claim closed as a duplicate no longer drops open claims (it read NULL as false).
  2. definitionHead of Claims Operations claims v1 First version.