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.

Dates that do not fit

date_mismatch unit: claims operator detector

A claim whose dates contradict each other: a repair invoice or estimate dated before the loss it is for, or a loss that happened before the policy began.

fraudHead of Special Investigations · fraud v1

An open claim with an invoice or estimate whose printed date is before the day of the loss (received at or before now, in the last 90 days), or whose policy first started after the day of the loss. A same-day invoice (towing, emergency repairs) is not a mismatch.

latest change: First version.

  • pass count_betweenobserved 26, expected between 1.0 and 80.0
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 26}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.policy_id, d.id AS document_id,
       d.received_at AS started_at, 'high' AS severity,
       json_object(
         'claim_no', c.claim_no, 'what', d.kind || ' dated before the loss', 'document_ref', d.ref,
         'document_date', d.doc_date, 'loss_at', c.loss_at,
         'days_before_loss', date_diff('day', d.doc_date, CAST(c.loss_at AS DATE)), 'vendor_id', d.vendor_id,
         'amount', d.amount
       ) AS evidence
FROM documents d
JOIN claims c ON c.id = d.claim_id
WHERE d.kind IN ('invoice', 'estimate') AND d.received_at <= TIMESTAMP 'Mon 17:00:00' AND d.received_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
  AND d.doc_date < CAST(c.loss_at AS DATE)
  AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
UNION ALL
SELECT 'claim', c.id, c.id, c.policy_id, NULL, c.reported_at, 'high',
       json_object(
         'claim_no', c.claim_no, 'what', 'loss before the policy began', 'policy_no', p.policy_no,
         'policy_first_start', p.first_start, 'loss_at', c.loss_at,
         'days_before_cover', date_diff('day', CAST(c.loss_at AS DATE), p.first_start)
       )
FROM claims c
JOIN policies p ON p.id = c.policy_id
WHERE c.reported_at <= TIMESTAMP 'Mon 17:00:00' AND c.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
  AND p.first_start > CAST(c.loss_at AS DATE)
  AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
ORDER BY started_at, subject_id

History

append-only; nothing is edited in place
  1. definitionHead of Special Investigations fraud v1 First version.