Data and SQL interview questions for insurance roles
89 questions from core to advanced: concepts, calculations, whiteboard diagrams, SQL, scenarios and behavioural. Answer out loud first, then reveal the model answer and example. Mark each one practised when you can answer it without looking.
Practised 0 of 89
Write SQL to list motor policies expiring in the next 30 days that have no renewal quote yet.
- Filter active motor policies by end date between today and today + 30.
- Exclude policies that already have a renewal quote, using NOT EXISTS.
- Return what the renewal team needs: policy, customer, end date, NCB.
Example querySELECT p.policy_no, p.customer_id, p.end_date, p.ncb_pct FROM policy p WHERE p.lob = 'MOTOR' AND p.status = 'IN_FORCE' AND p.end_date BETWEEN CURRENT_DATE AND CURRENT_DATE + 30 AND NOT EXISTS ( SELECT 1 FROM quote q WHERE q.prev_policy_no = p.policy_no AND q.type = 'RENEWAL' );Write SQL for the loss ratio by product for a financial year.
- Join earned premium and claims incurred by product for the same period.
- Claims incurred = paid + closing reserve − opening reserve.
- Aggregate separately first, then join, to avoid double counting.
Example queryWITH ep AS ( SELECT product, SUM(earned_premium) AS ep FROM premium_earned WHERE fy = '2025-26' GROUP BY product ), ci AS ( SELECT product, SUM(paid + closing_reserve - opening_reserve) AS ci FROM claims_fy WHERE fy = '2025-26' GROUP BY product ) SELECT ep.product, ROUND(100.0 * COALESCE(ci.ci, 0) / ep.ep, 1) AS loss_ratio_pct FROM ep LEFT JOIN ci ON ci.product = ep.product;What data quality checks would you run when migrating policies to a new system?
- Counts and premium totals reconcile by product, status and branch.
- Mandatory fields present; dates consistent (start before end, endorsement inside term).
- Reference data valid (product codes, RTO codes, intermediary codes).
- Sample policies re-rated in the new system match the old premium.
- Open claims and outstanding reserves carried over exactly.
How would you report on the 1-hour and 3-hour cashless time limits?
- Capture timestamps for request received, each query raised and answered, and decision sent.
- Measure the time the insurer controls, pausing the clock while waiting for the hospital.
- Report % within limit by TPA, hospital and day, plus breaches with their extra cost.
- Alert before breach, not after.