Tell Me About Insurance Basics | Dataford Interview Questions

¿ Qué es una reserva?

For this SQL exercise, interpret a claim reserve as the remaining estimated cost of an open or reopened claim after settled payments. Write a query that calculates this amount for each eligible claim.

Output

One row per open or reopened claim with a non-null estimated ultimate cost
Columns: claim_id, claim_number, estimated_ultimate_cost, paid_amount, and reserve_amount
Treat missing settled payments as zero and do not allow a negative reserve
Sort by reserve_amount descending, then claim_id ascending

Schema

claims

Column	Type	Description
claim_idPK	INT	Unique claim identifier
claim_number	VARCHAR(20)	Business claim reference
status	VARCHAR(20)	Current claim status
estimated_ultimate_cost	DECIMAL(12,2)	Estimated total cost of the claim

claim_payments

Column	Type	Description
payment_idPK	INT	Unique payment identifier
claim_id	INT	Referenced claim
payment_status	VARCHAR(20)	Payment processing status
payment_amount	DECIMAL(12,2)	Payment amount

Your question is Tell Me About Insurance Basics. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem