Data Analyst Interview Questions & Answers: Set 15, Insurance

SQL

1. 1.Calculate the claim ratio (claims paid ÷ premium collected) per product.

   Hide answer

   Answer: Join claims and premiums to products separately (aggregate each first), then divide paid claims by collected premium. Tables assumed: policies(policy_id, product), premiums(policy_id, amount), claims(policy_id, paid_amount, status). Aggregating in CTEs before joining avoids double-counting, and NULLIF stops divide-by-zero. Trap: joining claims and premiums directly to policies in one go multiplies rows and inflates both totals.

   WITH prem AS (
     SELECT p.product, SUM(pr.amount) AS premium
     FROM policies p JOIN premiums pr ON pr.policy_id = p.policy_id
     GROUP BY p.product
   ), clm AS (
     SELECT p.product, SUM(c.paid_amount) AS paid
     FROM policies p JOIN claims c ON c.policy_id = p.policy_id
     WHERE c.status = 'PAID'
     GROUP BY p.product
   )
   SELECT pr.product, pr.premium, COALESCE(c.paid, 0) AS claims_paid,
          ROUND(COALESCE(c.paid, 0) / NULLIF(pr.premium, 0), 3) AS claim_ratio
   FROM prem pr LEFT JOIN clm c ON c.product = pr.product
   ORDER BY claim_ratio DESC;

2. 2.Find policies that lapsed within 6 months of starting.

   Hide answer

   Answer: Filter policies with status 'LAPSED' whose lapse date falls within 6 months of the start date. Table assumed: policies(policy_id, customer_id, start_date, lapse_date, status). In PostgreSQL, add an INTERVAL to the start date; in SQL Server use DATEADD(month, 6, start_date). Early lapses are a key quality signal for the sales channel that sold them.

   SELECT policy_id, customer_id, start_date, lapse_date,
          lapse_date - start_date AS days_to_lapse
   FROM policies
   WHERE status = 'LAPSED'
     AND lapse_date < start_date + INTERVAL '6 months'
   ORDER BY days_to_lapse;