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.Missing police report
missing_police_report unit: claims operator detectorA theft, burglary, vandalism or hit-and-run claim with no police report on the file two days after it was reported. Without the report the claim cannot be settled.
claimsHead of Claims Operations · claims
v1
An open, assigned claim whose peril is one of police_perils, reported at least hours_allowed ago (and at most 90 days ago), with no police report received as of now. The peril decides: a collision whose notes say the other driver left the scene is not caught by this version.
latest change: First version.
- pass count_betweenobserved 106, expected between 10.0 and 400.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 106}, 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,
c.reported_at + INTERVAL (48) HOUR AS started_at,
CASE WHEN c.reported_at <= TIMESTAMP 'Mon 17:00:00' - INTERVAL 7 DAY THEN 'high' ELSE 'medium' END AS severity,
json_object(
'claim_no', c.claim_no, 'peril', c.peril, 'cause', c.cause, 'reported_at', c.reported_at,
'hours_since_reported', date_diff('hour', c.reported_at, TIMESTAMP 'Mon 17:00:00'), 'hours_allowed', 48,
'adjuster_id', c.adjuster_id, 'document_kind', 'police_report',
'documents_on_file', (SELECT count(*) FROM documents d WHERE d.claim_id = c.id AND d.received_at <= TIMESTAMP 'Mon 17:00:00')
) AS evidence
FROM claims c
WHERE list_contains(string_split('theft,burglary,vandalism,hit_and_run', ','), c.peril)
AND c.reported_at + INTERVAL (48) HOUR <= TIMESTAMP 'Mon 17:00:00'
AND c.reported_at >= TIMESTAMP 'Mon 17:00:00' - INTERVAL 90 DAY
AND c.assigned_at <= TIMESTAMP 'Mon 17:00:00'
AND (c.closed_at IS NULL OR c.closed_at > TIMESTAMP 'Mon 17:00:00')
AND NOT EXISTS (SELECT 1 FROM documents d WHERE d.claim_id = c.id AND d.kind = 'police_report'
AND d.received_at <= TIMESTAMP 'Mon 17:00:00')
ORDER BY started_at, c.id
Parameters
change one, see what changes today, then save it as a new version with a noteHistory
append-only; nothing is edited in place- definitionHead of Claims Operations claims v1 First version.