Insurance Loss Ratio Analysis (SQL Project) Project Overview

This project demonstrates how to calculate an adjusted loss ratio for an insurance portfolio using SQL. The goal is to show a realistic approach for analyzing insurance premiums and claims, including unearned premiums and outstanding claims.

This project is ideal for a portfolio because it demonstrates:

SQL database design and table relationships

Sample data creation and manipulation

Complex business calculations using SQL

Analysis of insurance metrics commonly used in the industry

Tables Structure

1. Policies – Insurance policy information | Column | Type | Description | | ------------ | ------------- | ------------------------- | | PolicyID | INT | Unique policy identifier | | CustomerName | NVARCHAR(100) | Name of the customer | | ProductType | NVARCHAR(50) | Type of insurance product | | StartDate | DATE | Policy start date | | EndDate | DATE | Policy end date |

2. Premiums – Premium data | Column | Type | Description | | -------------------- | ------------- | ------------------------------------------- | | PremiumID | INT | Unique premium identifier | | PolicyID | INT | Linked policy ID | | Amount | DECIMAL(10,2) | Premium issued during the period | | PremiumDate | DATE | Date of premium | | UnearnedPremiumStart | DECIMAL(10,2) | Unearned premium at the start of the period | | UnearnedPremiumEnd | DECIMAL(10,2) | Unearned premium at the end of the period |

3. Claims – Claim data | Column | Type | Description | | --------------------- | ------------- | --------------------------------------------- | | ClaimID | INT | Unique claim identifier | | PolicyID | INT | Linked policy ID | | ClaimAmount | DECIMAL(10,2) | Claims paid during the period | | ClaimDate | DATE | Date of claim | | ClaimStatus | NVARCHAR(20) | Paid or Pending | | OutstandingClaimStart | DECIMAL(10,2) | Outstanding claims at the start of the period | | OutstandingClaimEnd | DECIMAL(10,2) | Outstanding claims at the end of the period |

Key Formulas

Earned Premium Earned Premium = Premiums issued during the period + Unearned premium at the beginning of the period – Unearned premium at the end of the period

Incurred Claims Incurred Claims = Claims paid during the period – Outstanding claims at the beginning of the period + Outstanding claims at the end of the period

Adjusted Loss Ratio Adjusted Loss Ratio = Total Incurred Claims / Total Earned Premium

Adjusted Loss Ratio by Product Same formula applied per insurance product type.

Example Output

Earned Premium per Policy

PolicyID	EarnedPremium
1	1,250
2	820
3	520

Incurred Claims per Policy

PolicyID	IncurredClaims
1	650
2	320
3	205

Adjusted Loss Ratio

AdjustedLossRatio
0.53

Adjusted Loss Ratio by Product

ProductType	AdjustedLossRatioByProduct
Car	0.52
Health	0.39
Home	0.40
Note: Example values are based on sample data included in the SQL script.

How to Use

Clone this repository: git clone https://github.com/sheida-shab/insurance-loss-ratio-sql.git

Open insurance_loss_ratio.sql in your SQL environment (SQL Server, MySQL, PostgreSQL, or online SQL playground like DB Fiddle ).

Execute the SQL script to create tables, insert sample data, and run queries for earned premiums, incurred claims, and adjusted loss ratios.