🚀 SQL Project Series #16: Insurance Claims Analytics 🏥

Analyze insurance policies, customers, claims, premiums, and settlements using SQL to improve claim processing, detect fraud, and measure business performance.

🎯 Business Objectives
✅ Track insurance policies
✅ Analyze customer demographics
✅ Monitor claim submissions
✅ Measure claim approval rates
✅ Detect fraudulent claims
✅ Analyze premium collections
✅ Evaluate claim settlement time
✅ Build insurance dashboards

📂 Step 1: Create Database
CREATE DATABASE insurance_db;
USE insurance_db;

📂 Step 2: Create Customers Table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
gender VARCHAR(10),
age INT,
city VARCHAR(50)
);

📂 Step 3: Create Policies Table
CREATE TABLE policies (
policy_id INT PRIMARY KEY,
customer_id INT,
policy_type VARCHAR(50),
premium_amount DECIMAL(10,2),
start_date DATE,
end_date DATE,
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

📂 Step 4: Create Claims Table
CREATE TABLE claims (
claim_id INT PRIMARY KEY,
policy_id INT,
claim_date DATE,
claim_amount DECIMAL(10,2),
approved_amount DECIMAL(10,2),
claim_status VARCHAR(30),
settlement_date DATE,
FOREIGN KEY (policy_id)
REFERENCES policies(policy_id)
);

📂 Step 5: Insert Sample Customers
INSERT INTO customers VALUES
(1,'Rahul Sharma','Male',32,'Mumbai'),
(2,'Priya Verma','Female',29,'Delhi'),
(3,'Amit Patel','Male',41,'Pune'),
(4,'Sneha Joshi','Female',36,'Bangalore'),
(5,'Rohan Gupta','Male',45,'Hyderabad');

📂 Step 6: Insert Sample Policies
INSERT INTO policies VALUES
(101,1,'Health',15000,'2025-01-01','2025-12-31'),
(102,2,'Motor',12000,'2025-02-01','2026-01-31'),
(103,3,'Life',25000,'2025-01-15','2035-01-14'),
(104,4,'Health',18000,'2025-03-01','2026-02-28'),
(105,5,'Motor',10000,'2025-04-01','2026-03-31');

📂 Step 7: Insert Sample Claims
INSERT INTO claims VALUES
(1001,101,'2025-03-10',25000,22000,'Approved','2025-03-18'),
(1002,102,'2025-04-05',18000,0,'Rejected',NULL),
(1003,103,'2025-05-12',50000,48000,'Approved','2025-05-22'),
(1004,104,'2025-06-08',12000,10000,'Approved','2025-06-15'),
(1005,105,'2025-07-01',15000,14000,'Under Review',NULL);

🧠 SQL Concepts You'll Practice
✔ DDL & DML
✔ INNER JOIN
✔ LEFT JOIN
✔ Aggregate Functions
✔ GROUP BY
✔ HAVING
✔ CASE WHEN
✔ Date Functions
✔ CTEs
✔ Window Functions

📊 Business KPIs You Can Build
📈 Total Customers
📈 Total Active Policies
📈 Policies by Type
📈 Total Premium Collected
📈 Average Premium Amount
📈 Total Claims Submitted
📈 Total Approved Claims
📈 Total Rejected Claims
📈 Claim Approval Rate
📈 Claim Rejection Rate
📈 Total Claim Amount
📈 Total Approved Amount
📈 Average Claim Amount
📈 Claim Settlement Time
📈 Claims by Policy Type
📈 Claims by City
📈 High-Value Claims
📈 Monthly Claim Trend
📈 Premium vs Claim Ratio
📈 Fraud Detection Candidates
📈 Customer Lifetime Value
📈 Policy Renewal Trend
📈 Executive Insurance Dashboard

🎯 This project reflects real-world SQL analysis performed by Insurance Analysts, Risk Analysts, Claims Operations teams, Fraud Detection teams, and Business Intelligence professionals to improve operational efficiency, manage risk, and enhance customer service.

💡 Double Tap ❤️ For More