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.Total loss not flagged
total_loss_unflagged unit: claims operator detectorA motor repair estimate at or past the total-loss line of the car's value, a day after it arrived, with the claim still not flagged as a total loss. Repairing a car worth less than its repair is money lost.
claimsHead of Motor Claims · claims
v1
An open motor claim whose latest estimate (as of now) is at least threshold times the vehicle value on the policy, whose first estimate arrived at least grace_hours ago, and that has no total-loss flag as of now. This is the engineering meaning of total loss; see the total_loss term for the finance meaning.
latest change: First version.
- fail count_betweenobserved 0, expected between 1.0 and 60.0
- pass uniqueobserved {'duplicates': 0}, expected claim_id unique
- pass detector_shapeobserved {'missing_columns': [], 'bad_rows': 0, 'rows': 0}, expected columns ['subject_type', 'subject_id', 'started_at', 'evidence'], evidence is an object
SQL, rendered for now
WITH est AS (
SELECT claim_id, arg_max(amount, received_at) AS amount, min(received_at) AS first_at, arg_max(ref, received_at) AS ref
FROM documents WHERE kind = 'estimate' AND received_at <= TIMESTAMP 'Mon 17:00:00' GROUP BY claim_id
)
SELECT 'claim' AS subject_type, c.id AS subject_id, c.id AS claim_id, c.policy_id, c.vendor_id,
e.first_at + INTERVAL (24) HOUR AS started_at, 'high' AS severity,
json_object('claim_no', c.claim_no, 'estimate', e.amount, 'estimate_ref', e.ref,
'vehicle_value', p.vehicle_value, 'repair_ratio', round(e.amount / p.vehicle_value, 3),
'threshold', 0.7, 'vehicle_class', p.vehicle_class, 'vehicle_year', p.vehicle_year,
'deductible', p.deductible,
'settlement', round(p.vehicle_value - p.deductible, 2)) AS evidence
FROM claims c
JOIN est e ON e.claim_id = c.id
JOIN policies p ON p.id = c.policy_id
WHERE c.line = 'motor' AND e.amount >= 0.7 * p.vehicle_value
AND e.first_at + INTERVAL (24) HOUR <= TIMESTAMP 'Mon 17:00:00'
AND (c.total_loss_at IS NULL OR c.total_loss_at > TIMESTAMP 'Mon 17:00:00')
AND (c.closed_at IS NULL OR c.closed_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 Motor Claims claims v1 First version.