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.

Ring indicator

ring_indicator unit: rings operator detector

One phone number or bank account given on claims by three or more unrelated households within 90 days. Families share accounts and claimants sometimes give their garage's number; neither is a ring.

fraudHead of Special Investigations · fraud v2

A claimant phone or payout bank account that appears on claims from at least min_households different households (surname and address) reported in the last window_days. A phone that belongs to a garage or contractor in the vendor list is not counted. The ring becomes visible when its min_households-th claim is reported.

latest change: Garage and contractor phone numbers no longer count; claimants often give their garage's number.

  • pass count_betweenobserved 7, expected between 1.0 and 12.0
  • pass uniqueobserved {'duplicates': 0}, expected ring_key unique
  • pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 7}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH recent AS (
  SELECT c.id, c.claim_no, c.reported_at, c.vendor_id, c.claimant_phone, c.claimant_bank,
         h.surname || '|' || h.address AS household
  FROM claims c JOIN policyholders h ON h.id = c.holder_id
  WHERE c.reported_at <= TIMESTAMP 'Mon 17:00:00' AND c.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL (90) DAY
),
keyed AS (
  SELECT 'phone:' || claimant_phone AS ring_key, * FROM recent
  WHERE claimant_phone NOT IN (SELECT phone FROM vendors)
  UNION ALL
  SELECT 'bank:' || claimant_bank, * FROM recent
),
rings AS (
  SELECT ring_key, count(DISTINCT household) AS households, count(*) AS claims,
         list(id ORDER BY reported_at, id) AS claim_ids, list(claim_no ORDER BY reported_at, id) AS claim_nos,
         list(reported_at ORDER BY reported_at, id) AS reported, max(id) AS latest_claim_id,
         mode(vendor_id) AS vendor_id, count(DISTINCT vendor_id) AS garages
  FROM keyed GROUP BY ring_key
  HAVING count(DISTINCT household) >= 3
)
SELECT 'ring' AS subject_type, ring_key AS subject_id, ring_key, latest_claim_id AS claim_id, claim_ids,
       vendor_id, reported[3] AS started_at,
       CASE WHEN households >= 5 THEN 'critical' ELSE 'high' END AS severity,
       json_object('shared', split_part(ring_key, ':', 1), 'value', split_part(ring_key, ':', 2),
                   'households', households, 'claims', claims, 'claim_nos', claim_nos,
                   'garages', garages) AS evidence
FROM rings
ORDER BY started_at, ring_key

Parameters

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

History

append-only; nothing is edited in place
  1. definitionHead of Special Investigations fraud v2 Garage and contractor phone numbers no longer count; claimants often give their garage's number.
  2. definitionHead of Special Investigations fraud v1 First version.