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.

Photo taken before the loss

photo_before_loss unit: claims operator detector

A photo of the damage whose own metadata says it was taken well before the loss happened. Phones with a wrong clock and claimants unsure of the date make this the noisiest check on the list.

fraudHead of Special Investigations · fraud v2

An open claim with a photo received in the last 90 days whose taken_at is more than hours_before before the loss time. A taken_at before earliest_year is a phone's factory clock, and one almost exactly a year before the loss (within two days) is a clock set a year back; neither counts. One row per claim, the oldest photo.

latest change: Photos about a year off are clock errors, not evidence; the margin went from 24 to 30 hours.

  • pass count_betweenobserved 30, expected between 1.0 and 80.0
  • pass uniqueobserved {'duplicates': 0}, expected claim_id unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 30}, 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, arg_min(d.id, d.taken_at) AS document_id,
       min(d.received_at) AS started_at, 'high' AS severity,
       json_object('claim_no', any_value(c.claim_no), 'loss_at', any_value(c.loss_at),
                   'photo_taken_at', min(d.taken_at), 'photo_ref', arg_min(d.ref, d.taken_at),
                   'device', arg_min(d.device, d.taken_at),
                   'days_before_loss', round(date_diff('hour', min(d.taken_at), any_value(c.loss_at)) / 24.0, 1),
                   'photos_on_file', count(*), 'reported_at', any_value(c.reported_at)) AS evidence
FROM documents d
JOIN claims c ON c.id = d.claim_id
WHERE d.kind = 'photo' AND d.received_at <= TIMESTAMP 'Mon 17:00:00' AND d.received_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
  AND d.taken_at < c.loss_at - INTERVAL (30) HOUR
  AND year(d.taken_at) >= 2010
  AND abs(date_diff('hour', d.taken_at + INTERVAL 365 DAY, c.loss_at)) > 48
  AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
GROUP BY c.id
ORDER BY started_at, c.id

Parameters

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

History

append-only; nothing is edited in place
  1. definitionHead of Special Investigations fraud v2 Photos about a year off are clock errors, not evidence; the margin went from 24 to 30 hours.
  2. definitionHead of Special Investigations fraud v1 First version: 24 hours.