Using the attached Insurance
Policies data set, answer Questions 1, 2, and 3. The modified excel sheet
with your work must be included as an attachment with your response.
Question 1
a.
Create a pivot table
which shows the number of single drivers with a bachelor’s degree broken out by
age group, coverage zone, and car use.
b.
Which age group has the
second highest number of commercial vehicles? Of that age group, how many
commercial vehicles were in the suburban coverage zone?
Question 2
a. Using the full set
of data, review all claims made for commercial vehicles. Prepare a one
paragraph analysis comparing the total claim amount per coverage zone and any
trends you find.
Question 3
a.
Create a bar or column chart that
visualizes the average insurance claim amount by car color. Ensure the chart
has a title and the columns are appropriately labeled.
b.
Which color has the highest average
claim amount? Which has the lowest claim amount? In your chart, add callouts to
identify these two colors.
Question 4
In
order to make data driven decisions and recommendations, the Operations
Analysis team runs data reports using SQL. SQL stands for ‘Structured Query
Language’ and allows analysts to review and create views of any data in an easy
and relatively quick way. Please review the below query and answer the
following questions:
SELECT
first_name,
last_name
FROM
Census_2020
WHERE
hair_color = ‘brown’
AND
Birth_date BETWEEN
‘1995-01-01’ AND ‘1995-07-31’
AND Last_name not like ‘c%’
ORDER BY
Last_Name
LIMIT
1000
;
a.
What data is being pulled by this query? In other words,
what question are we trying to answer?
b.
What do you think the data will show (be specific)?
