Loss Ratio Analysis and Anomalous Claim Detection

Goal

You calculate loss ratios and combined ratios by product and quarter from contract and claim data, find three kinds of anomalies — claims right after a contract, concentration at a particular hospital, and shared payee accounts — and build an investigation referral list.

Why it matters

A non-life insurer's report card is two numbers. The loss ratio is how much of the premium received went out as claims, and the combined ratio adds expenses to that and checks whether it exceeds 100%. If the combined ratio exceeds 100%, it means the insurance operation by itself is losing money. It is the number management looks at every month, and rate increases, tighter underwriting criteria, and suspending sales are decided on this number.

It matters that the denominator is earned premium. Even if you received a year's premium, only three months' worth of it was earned this quarter. The numerator is incurred loss, which includes not only what has already been paid but also the estimated amounts of claims that were filed but are still under adjustment. If you count only payments, the loss ratio looks better than it really is, and that illusion bursts in the next quarter.

Fraud detection is the other face of loss ratio management. A contract that gets a claim as soon as it is enrolled, claims where large amounts repeat at one particular hospital, claims where money goes out to the same account although the contracts are different — each one alone may be coincidence, but the pattern is an investigation target. A rule creates the basis for an investigation referral, not a judgment, and the final judgment is made by the investigation unit (SIU).

Rules of this lab

• The quarters are 2026Q1 (January–March) and 2026Q2 (April–June). Earned premium is the sum, over contracts in force on the first day of the quarter (start_date is on or before the first day of the quarter), of the integer quotient of annual_premium ÷ 4 taken for each contract (truncating below one won) (a simplification for this lab). If you add everything up first and then divide by 4, the result can be off by 1 won.
• Incurred loss = the sum of paid_amount + reserve_amount of claims whose accident date is in that quarter, excluding DENIED. Even if a denied claim still has an estimated amount, do not include it.
• Loss ratio (%) = incurred loss ÷ earned premium × 100 and expense ratio (%) = expenses ÷ earned premium × 100, both rounded to one decimal place. The combined ratio is the sum of the rounded loss ratio and expense ratio.
• Claim right after a contract = the accident date is less than 90 days from start_date (exactly 90 days does not count; it is a count of days, not "three months"). Count it regardless of the claim status. The date that decides the quarter is always the accident date.
• Hospital concentration = the hospital has 4 or more PAID claims, and the average payment of those PAID claims is more than 2 times the average of all PAID claims. Denied and open claims go into neither the count nor the average, and the HOSPITAL reason is likewise attached only to the PAID claims of a flagged hospital.
• Shared payee account = 3 or more different contracts with the same payee_account. Every claim of a flagged account gets the PAYEE reason regardless of its status.

Steps

1. Save the generator in the example on the right as /root/ins/make_claims.py and run it with python3 make_claims.py. claims.db will contain 65 contracts, 110 claims, and 6 rows of expenses.
2. Write the earned premium by product and quarter in /root/ins/earned.csv with the header product,quarter,policies,earned_premium, 6 lines.
3. Write the loss ratio by product and quarter in /root/ins/loss_ratio.csv with the header product,quarter,earned,incurred,loss_ratio_pct.
4. Write the combined ratio in /root/ins/combined.csv with the header product,quarter,loss_ratio_pct,expense_ratio_pct,combined_pct, and write three lines in /root/ins/combined.txt: worst_product=, worst_quarter=, and over_100_count=.
5. Write the claims right after a contract in /root/ins/early_claims.csv with the header claim_no,policy_no,days_since_start,paid_amount.
6. Write the hospital concentration in /root/ins/hospital_flag.csv with the header hospital_id,claims,avg_amount,ratio. claims is the number of PAID claims of that hospital, and ratio is the multiple against the overall PAID average (two decimal places).
7. Write the shared payee accounts in /root/ins/payee_flag.csv with the header payee_account,policies,claims,total_paid, and write the claims that triggered at least one of the three rules in /root/ins/siu_referral.csv with the header claim_no,reasons. reasons joins EARLY, HOSPITAL, and PAYEE with + in alphabetical order.
8. In /root/ins/loss_report.md, write a report in four sections: ## Loss Ratio and Combined Ratio, ## Anomalies, ## SIU Referral, and ## Limitations. The section titles can be the English ones or titles with the same meaning in Korean, Japanese, or Chinese, and you can also write both, one in parentheses.

Notes

• You can split the quarters with CASE WHEN substr(accident_date, 6, 2) <= '03' THEN '2026Q1' ELSE '2026Q2' END.
• Do ratios with floating-point division like ROUND(x * 100.0 / y, 1). Dividing integers gives 0.
• Common mistake 1: leaving out the estimated amount of OPEN claims from the loss ratio numerator. Money that has not gone out yet is also incurred loss.
• Common mistake 2: including a contract enrolled on 2026-02-15 in the Q1 earned premium. It was not a contract on the first day of the quarter.
• Common mistake 3: computing the hospital average over all claims rather than PAID. Denied and open claims have a payment of 0 and pull the average down.

Create the contract and claim data

Save the generator in the example on the right as /root/ins/make_claims.py and run it with python3 make_claims.py. claims.db will contain 65 contracts, 110 claims, and 6 rows of expenses.

Save the generator in the lab instructions as /root/ins/make_claims.py and run it with python3. If you edit it by hand, all the numbers in the later steps go off.

Calculate earned premium

Write the earned premium by product and quarter in /root/ins/earned.csv with the header product,quarter,policies,earned_premium, 6 lines.

For each quarter, count the contracts with start_date <= the first day of the quarter by product and add annual_premium / 4 (integer division). Combining the two queries with UNION ALL gives 6 lines. A contract enrolled on the first day of a quarter goes into that quarter, and contracts enrolled on 2026-02-15 go only into Q2. Take the quotient for each contract and then add.

Calculate incurred loss and the loss ratio

Write the loss ratio by product and quarter in /root/ins/loss_ratio.csv with the header product,quarter,earned,incurred,loss_ratio_pct.

Join claim with policy to get the product, split the quarters by the month of the accident date, and add paid_amount + reserve_amount (excluding DENIED). If you load the step 2 result with .import and LEFT JOIN it, the denominator is attached. ROUND(x * 100.0 / y, 1).

Add expenses to see the combined ratio

Write the combined ratio in /root/ins/combined.csv with the header product,quarter,loss_ratio_pct,expense_ratio_pct,combined_pct, and write three lines in /root/ins/combined.txt: worst_product=, worst_quarter=, and over_100_count=.

Join the expense table by product and quarter, round expenses ÷ earned premium × 100 to one decimal place, and add it to the rounded loss ratio (round both, then add). worst is the line with the largest combined_pct, and over_100_count is the number of lines that exceed 100.

Find claims right after enrollment

Write the claims right after a contract in /root/ins/early_claims.csv with the header claim_no,policy_no,days_since_start,paid_amount.

Join claim and policy and pull the cases where julianday(accident_date) − julianday(start_date) < 90. A claim at exactly 90 days is not included.

Find high-value claims concentrated at a particular hospital

Write the hospital concentration in /root/ins/hospital_flag.csv with the header hospital_id,claims,avg_amount,ratio. claims is the number of PAID claims of that hospital, and ratio is the multiple against the overall PAID average (two decimal places).

First get the overall average from only the claims with status='PAID' (a CTE), and keep only the hospitals with COUNT(*) >= 4 per hospital and AVG(paid_amount) > 2 × the overall average. avg_amount is an integer, and ratio is ROUND(…, 2).

Find shared payee accounts and build the investigation referral list

Write the shared payee accounts in /root/ins/payee_flag.csv with the header payee_account,policies,claims,total_paid, and write the claims that triggered at least one of the three rules in /root/ins/siu_referral.csv with the header claim_no,reasons. reasons joins EARLY, HOSPITAL, and PAYEE with + in alphabetical order.

payee_flag is GROUP BY payee_account with COUNT(DISTINCT policy_no) >= 3. For siu_referral, gather the step 5 claims, the PAID claims of the step 6 hospitals, and the claims of the step 7 accounts, join each claim's reasons with + in alphabetical order, and sort by claim_no.

Write the loss ratio and anomalous claims report

In /root/ins/loss_report.md, write a report in four sections: ## Loss Ratio and Combined Ratio, ## Anomalies, ## SIU Referral, and ## Limitations. The section titles can be the English ones or titles with the same meaning in Korean, Japanese, or Chinese, and you can also write both, one in parentheses.

Use the four section titles exactly as given, either the English titles or titles with the same meaning in Korean, Japanese, or Chinese. In the loss ratio section, write the worst product and its combined ratio on one line; in the Anomalies section, every hospital ID from step 6 and every shared payee account from step 7; and in the SIU Referral section, the number of entries in siu_referral.csv as a number.