Scribd
Upload
19 views5 pages

SQL Analysis of Auto Insurance Claims

This document contains the SQL code and analysis for an auto insurance risk dataset. Some key findings include: 1) 50.23% of customers made a claim in the current exposure period. 2) Those with higher average exposure tended to claim more often than others. 3) Exposure buckets E1 and E4 had the highest claim rates, comprising almost 2/3 of total claims. 4) Area C had the highest number of average claims as a percentage of total policies. 5) Average vehicle age was lower for those who claimed compared to those who didn't. BonusMalus decreases with older driver age groups. 6) Vehicle brand B12 with regular gas had the

Uploaded by

Harsh Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
19 views5 pages

SQL Analysis of Auto Insurance Claims

This document contains the SQL code and analysis for an auto insurance risk dataset. Some key findings include: 1) 50.23% of customers made a claim in the current exposure period. 2) Those with higher average exposure tended to claim more often than others. 3) Exposure buckets E1 and E4 had the highest claim rates, comprising almost 2/3 of total claims. 4) Area C had the highest number of average claims as a percentage of total policies. 5) Average vehicle age was lower for those who claimed compared to those who didn't. BonusMalus decreases with older driver age groups. 6) Vehicle brand B12 with regular gas had the

Uploaded by

Harsh Singh
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Graded Project

Utkarsh Atri – Edart C-III


Dataset - Auto_Insurance_Risk

Use auto_insurance_risk;

1. Write a query to calculate what % of the customers have made a claim in the current exposure period[i.e. in the given
dataset]?

select count(*) from Auto_insurance_risk


where ClaimNb>0; 2
(34060/678013)*100 = 50.23%

2.1. Create a new column as 'claim_flag' in the table 'auto_insurance_risk' as integer datatype.

alter table Auto_insurance_risk 1.5


add column claim_flag int;

2.2 Set the value to 1 when ClaimNb is greater than 0 and set the value to 0 otherwise.

UPDATE Auto_insurance_risk
SET claim_flag = 1.5
CASE WHEN ClaimNb > 0 THEN 1
ELSE 0
END;

3.1. What is the average exposure period for those who have claimed?

select avg(Exposure) from Auto_insurance_risk


where claim_flag = 1; 1

3.2 What do you infer from the result? 1


Use claim_flag variable to group the data.
select claim_flag,avg(Exposure) from Auto_insurance_risk
GROUP by claim_flag

Thus those with higher avg. Exposure tend to claim much more often as compared to the rest.

4.1. If we create an exposure bucket where buckets are like below, what is the % of total claims by these buckets? 2
UPDATE Auto_insurance_risk
SET ebucket =
CASE
WHEN Exposure >= 0 and Exposure <=0.25 THEN "E1"
WHEN Exposure > 0.25 and Exposure <=0.50 THEN "E2"
WHEN Exposure >= 0.51 and Exposure <=0.75 THEN "E3"

This study source was downloaded by 100000826983498 from [Link] on 06-07-2022 06:46:43 GMT -05:00

[Link]
WHEN Exposure > 0.75 THEN "E4"
END

For percent claims per bucket

select ebucket,count(ClaimNb), count(ClaimNb)/6780.13


from Auto_insurance_risk
group by ebucket;

4.2 What do you infer from the summary?

We can conclude that E1 and E4 have higher claim (total they both comprise of almost 2/3 rd of 1
total claims. Thus need to be checked into as this means anyperson within E1/E4 has high
chances of claim.

5. Which area has the highest number of average claims? Show the data in percentage w.r.t. the number of policies in
corresponding Area.

select area,count(ClaimNb)
from Auto_insurance_risk
group by area 2
order by count(ClaimNb) desc
limit 1;

Area C

6. If we use these exposure bucket along with Area i.e. group Area and Exposure Buckets together and look at the claim
rate, an interesting pattern could be seen in the data. What is that?

select Area,ebucket,sum(claim_flag)/6780.13 as
claim_rate,sum(ClaimNb)
from Auto_insurance_risk
group by Area,ebucket 3
order by sum(ClaimNb) desc;

We can see that as mentioned earlier E4 and


E1 have higher claims.
So we see that it’s E4  E1 E2  E3 as an
average trend.

7.1. If we look at average Vehicle Age for those who claimed vs those who didn't claim, what do you see in the summary?
1.5 Marks for SQL and 1 for inference.

select claim_flag,avg(VehAge) from Auto_insurance_risk


group by claim_flag; 2.5

Those who did not claim have higher vehicle age as compared
to those who claimed.

This study source was downloaded by 100000826983498 from [Link] on 06-07-2022 06:46:43 GMT -05:00

[Link]
7.2. Now if we calculate the average Vehicle Age for those who claimed and group them by Area, what do you see in the
summary? Any particular pattern you see in the data?

select Area,avg(VehAge) 2.5


from Auto_insurance_risk
group by area
having claim_flag=1;

8. If we calculate the average vehicle age by exposure bucket(as mentioned 3


above), we see an interesting trend between those who claimed vs those who
didn't. What is that?

select ebucket,avg(VehAge),claim_flag
from Auto_insurance_risk
group by ebucket,claim_flag;

9.1. Create a Claim_Ct flag on the ClaimNb field as below, and take average of the BonusMalus by Claim_Ct. 2
UPDATE Auto_insurance_risk

SET Claim_Ct =
CASE WHEN ClaimNb = 1 THEN "1 Claim"
WHEN ClaimNb > 1 THEN "MT 1 Claims"
WHEN ClaimNb = 0 THEN "No Claims"
END;

select Claim_Ct,avg(BonusMalus)from Auto_insurance_risk


group by Claim_Ct;

9.2 What is the inference from the summary?

We can see that the average BonuMalus is almost same for categories being a bit inclined 1
towards those who have already claimed more than once.

10. Using the same Claim_Ct logic created above, if we aggregate the 4
Density column (take average) by Claim_Ct, what inference can we
make from the summary data?

select Claim_Ct,avg(Density)from Auto_insurance_risk


group by Claim_Ct

Average Density is higher for those with more than


one claims. It increases with the claims, thus being
more for those who’ve claimed.

2
11. Which Vehicle Brand & Vehicle Gas combination have the
highest number of Average Claims (use ClaimNb field for
aggregation)?

select VehBrand,VehGas,avg(ClaimNb)
from Auto_insurance_risk
group by VehBrand,VehGas
order by avg(ClaimNb) desc;

This study source was downloaded by 100000826983498 from [Link] on 06-07-2022 06:46:43 GMT -05:00

[Link]
Thus Vehicle Brand B12 which is a Regular Vehicle Gas has the highest average claims.

12. List the Top 5 Regions & Exposure[use the buckets


created above] Combination from Claim Rate's
perspective. Use claim_flag to calculate the claim rate.

select
Region,Exposure,count(claim_flag)/6780.13
from Auto_insurance_risk 3
group by Region,Exposure
order by count(claim_flag)/6780.13 DESC
limit 5;

13.1. Are there any cases of illegal driving i.e. underaged folks
driving and committing accidents?

select claim_flag,count(claim_flag)
from Auto_insurance_risk
where age = "1 - Beginner" 1
group by claim_flag;

Yes, there are a total of 61 cases of illegal driving

13.2 Create a bucket on DrivAge and then take average of BonusMalus by this Age Group Category. WHat do you infer from
the summary?
DrivAge=18 then 1-Beginner, DrivAge<=30 then 2-Junior, DrivAge<=45 then 3-Middle Age, DrivAge<=60 then 4-Mid-
Senior, DrivAge>60 then 5-Senior 2.5 Marks for SQL and 1.5 for inference.

UPDATE Auto_insurance_risk
SET age =
CASE WHEN DrivAge =18 THEN "1 - Beginner"
WHEN DrivAge > 18 and DrivAge <=30 THEN "2 -
Junior"
WHEN DrivAge > 30 and DrivAge <=45 THEN "3 -
Middle Age"
WHEN DrivAge > 45 and DrivAge <=60 THEN "4 - Mid
Senior" 4
WHEN DrivAge > 60 THEN "5 - Senior"
END;

select age as Age_Category,avg(BonusMalus)


from Auto_insurance_risk
group by age;

We can see that BonusMalus i.e. which penalises them for making claims decreases with age.
This can be due to the fact the that older people have much more experience in driving as
compared to younger ones so they are expected to drive cautiously.

14. Mention one major difference between unique constraint and primary key? 2
Primary Key - Only one primary key is allowed to use in a table, thus used to uniquely identify each
record in the table. The primary key does not accept the any duplicate and NULL values

This study source was downloaded by 100000826983498 from [Link] on 06-07-2022 06:46:43 GMT -05:00

[Link]
Unique key - A column with a unique key constraint can only contain unique values. It is not a
compulsion to have a unique key in a table.

15. If there are 5 records in table A and 10 records in table B and we cross-join these two tables, how many records will be
there in the result set?
2
5*10 = 50

16. What is the difference between inner join and left outer join?

Inner join returns a combined tuples between two or more tables where at least one attribute in
common. If there is no attribute in common between tables then it will return nothing.
2
Left Outer join is an operation that returns a combined tuples from a specified table even the join
condition will fail. It returns all records from the left table (Table 1) and matching records from the
right table (Table 2).

17. Consider a scenario where Table A has 5 records and Table B has 5 records. Now while inner joining Table A and Table
B, there is one duplicate on the joining column in Table B (i.e. Table A has 5 unique records, but Table B has 4 unique
values and one redundant value). What will be record count of the output? 2
25

18. What is the difference between WHERE clause and HAVING clause?

WHERE Clause is used to filter the records from the table based on the specified condition whereas
HAVING Clause is used to filter record from the groups based on the specified condition.
WHERE Clause can be used without GROUP BY Clause, but HAVING Clause cannot be used
without GROUP BY Clause.

This study source was downloaded by 100000826983498 from [Link] on 06-07-2022 06:46:43 GMT -05:00

[Link]
Powered by TCPDF ([Link])

Common questions

Powered by AI

The creation of exposure buckets allows for segmented analysis of claim distribution. Buckets such as E1 and E4, which account for almost two-thirds of total claims, highlight areas with higher claim tendencies. This segmentation reveals that exposure level is strongly linked to claim frequency, which aids in focusing risk assessment and policy adjustments .

There are documented instances of illegal driving among underaged drivers, shown by 61 cases. This raises regulatory concerns for insurers to implement stricter checks and balances to prevent such incidents, possibly adjusting insurance terms involving age verification procedures to mitigate this risk .

Vehicle brand B12 with Regular Gas shows the highest average claims. This suggests that specific combinations of vehicle brand and fuel type might be associated with higher risk, leading insurance companies to reconsider premium pricing or focus on preventive measures regarding higher-risk brands and fuel types .

The primary key is unique across the table and does not accept NULL values, ensuring that each record is uniquely identified. The unique constraint also ensures uniqueness of data but allows for one NULL entry. This affects database design by dictating data integrity rules; primary keys ensure entity integrity while unique constraints can enforce alternate key uniqueness in a flexible manner .

There is a clear difference in vehicle age between claimants and non-claimants, with exposure bucket data further refining this trend. Lower vehicle age in high exposure buckets among claimants suggests a nuanced interaction in risk profiling, indicating that risk models should dynamically incorporate variables like vehicle age and exposure to predict claims accurately .

Those who did not claim have a higher vehicle age compared to those who claimed. This implies that there might be an inverse relationship between vehicle age and likelihood of claim, suggesting that older vehicles might be less prone to claims for various reasons such as more cautious driving or different ownership patterns .

Segmenting driver age into categories shows that BonusMalus decreases with age. Older drivers demonstrate lower penalty scores, suggesting safer driving habits. This demographic analysis enables insurers to tailor policies and risk measures according to driver age, enhancing personalization and competitive market positioning .

The average BonusMalus score, which increases slightly with claim count, implies a gradual penalty system that may deter frequent claims. Insurance policies might leverage this by designing graduated incentive structures that encourage fewer claims, balancing risk with consumer cost satisfaction .

Average density values are higher for customers with multiple claims, suggesting higher urban density correlates with increased claim rates. This informs risk assessment by highlighting the influence of geographical and demographic factors in insurance risk models, potentially guiding changes in premiums or terms in densely populated areas .

Claims in exposure and area combinations show that E4, followed by E1, have the highest claim rates. The trend E4 -> E1 -> E2 -> E3 suggests that insurance strategies can prioritize monitoring and adjusting policies in high-risk combinations, particularly focusing on the highest risk areas to balance premiums and payouts effectively .

You might also like