Database/SQL Server

MySQL - Problem solving , Data analysis

청렴결백한 만능 재주꾼 2020. 10. 1. 05:02
반응형

MySQL version

 

Claims                  Field Description
Claim_Number - Unique ID for insurance claim
Policy_Holder_Id -  Unique ID for policy holder
Claim_Type - Indicator (Accident or Theft)
Claim_Amount - $$ amount of claim
Claim_Date_Filed - Date claim was filed
Claim_Date_Settle -  Date claim was settled

 


Policyholders           Field Description
PolicyHolderId - Unique ID for policy holder
YearEnrolled - Year policy holder enrolled in insurance
AnnualPremium - $$ amount policy holder pays yearly for coverage
Gender - Indicator (Male or Female)
Age - Age of policy holder
HomeDemo - Demographic of policy holder’s area of residence

 

EERD

 

 

a-1. How many claims has policy holder 405 made?

SELECT
CLAIM_NUMBER
FROM CLAIMS
WHERE POLICY_HOLDER_ID=405;

 

OR

 

 

SELECT COUNT(CLAIM_NUMBER) COUNTED 
FROM CLAIMS 
WHERE POLICY_HOLDER_ID=405;

 

a-2. What was the file date of the most recent claim that policy holder 405 has made?

SELECT CLAIM_DATE_FILED
FROM CLAIMS
WHERE POLICY_HOLDER_ID=405
ORDER BY CLAIM_DATE_FILED DESC;

ANSWER IS 06/24/2017 . 

 

data type is text...

 

 

 

 

a-3. What was the total amount of all claims filed by policy holder 405?

SELECT SUM(CLAIM_AMOUNT) TOTAL_AMOUNT 
FROM CLAIMS 
WHERE POLICY_HOLDER_ID=405;

 

 

b. What was the total claim amount and the average claim amount for accident claims by claim type?

Claim Type Total Claim Amount Average Claim Amount
Accident 2264503.66 9803.04
Theft 481711.10 1926.84 
SELECT SUM(CLAIM_AMOUNT) TOTAL_AMOUNT FROM CLAIMS WHERE CLAIM_TYPE="ACCIDENT";
SELECT AVG(CLAIM_AMOUNT) TOTAL_AMOUNT FROM CLAIMS WHERE CLAIM_TYPE="ACCIDENT";
SELECT SUM(CLAIM_AMOUNT) TOTAL_AMOUNT FROM CLAIMS WHERE CLAIM_TYPE="THEFT";
SELECT AVG(CLAIM_AMOUNT) TOTAL_AMOUNT FROM CLAIMS WHERE CLAIM_TYPE="THEFT";

 

 

C. DaysToSettle is a kpi that measures the number of days between the claim being filed and the claim being settled. What was the mean DaysToSettle for Accident claims vs. Theft claims?

select 
avg(datediff(str_to_date(claim_date_settled, '%c/%d/%Y'),str_to_date(claim_date_filed, '%c/%d/%Y'))) AVG_GAP 
from claims 
where claim_type="theft";

select 
avg(datediff(str_to_date(claim_date_settled, '%c/%d/%Y'),str_to_date(claim_date_filed, '%c/%d/%Y'))) AVG_GAP 
from claims 
where claim_type="accident";

 

 

D. How many policy holders in the policyholders table did not file any claims?

 

SELECT COUNT(POLICYHOLDERID) TOTAL FROM POLICYHOLDERS;

Total policyholder - 150

 

SELECT POLICY_HOLDER_ID TOTAL FROM CLAIMS GROUP BY POLICY_HOLDER_ID;

 

policyholder who filed claim - 118

 

 

반응형