1. Classify patients into 4 tiers based on number of claims. (6 points)
a. Tier 1 (High-Risk) (75-100 percentile); Tier 2 (Rising Risk) (50-75percentile), Tier 3 (Med Risk)(25-50%) Tier 4 (Low Risk) (0-25%)
b. Add a new column to the Beneficiary table that indicates the tier of the patient
c. Add a new column to the beneficiary table that calculates patient age in 2019 (2019 – Year of birth)
a. For each tier identify
i. Average and Median Claim count and claim amount
ii. Percentage of the population with each type of chronic condition
iii. Percentage of population in different age ranges (65 -74, 75-85, 85+)