Calculate Medical Claims by Age and Gender in 2024
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
MEMBERSHIP
+----+----------+--------+---------+
| id | age_band | gender | zipcode |
+----+----------+--------+---------+
| 1 | 18-25 | F | 90001 |
| 2 | 26-35 | M | 10001 |
| 3 | 18-25 | M | 02139 |
| 4 | 36-45 | F | 60616 |
| 5 | 26-35 | F | 94105 |
+----+----------+--------+---------+
CLAIM
+----+------------+-----------+-------------+
| id | claim_date | paid_amt | insurance |
+----+------------+-----------+-------------+
| 1 | 2024-03-15 | 550.00 | PPO |
| 1 | 2023-11-20 | 200.00 | HMO |
| 2 | 2024-01-05 | 1300.00 | HMO |
| 3 | 2024-07-23 | 75.00 | PPO |
| 4 | 2022-12-31 | 800.00 | PPO |
+----+------------+-----------+-------------+
##### Scenario
Health-insurance analytics team wants to understand how much was paid for medical claims by specific demographic segments.
##### Question
Calculate the total paid_amt in 2024 for members in a given age_band and gender. 2. For a given age_band, show the yearly trend of total paid_amt across all available years.
##### Hints
JOIN membership and claim on id, filter dates, GROUP BY or use WINDOW functions for yearly totals.
Overview: This question evaluates data manipulation and aggregation competencies using SQL or Python, with emphasis on demographic segmentation and time-based summarization of paid claim amounts.
2024 Total by Age Band and Gender
Using the MEMBERSHIP and CLAIM tables, calculate the total paid_amt in calendar year 2024 for all members in a specified age_band and gender. Return a single row with columns age_band, gender, year (fixed as 2024), and total_paid_amt. If no claims exist in 2024 for that demographic segment, return 0 as total_paid_amt.
Tables
MEMBERSHIP(id INTEGER, age_band VARCHAR(10), gender CHAR(1), zipcode CHAR(5))
CLAIM(id INTEGER, claim_date DATE, paid_amt DECIMAL(10,2), insurance VARCHAR(20))
Hints
- Join MEMBERSHIP to CLAIM on member id.
- Filter claim_date using a closed-open range for 2024: claim_date >= '2024-01-01' AND claim_date < '2025-01-01'.
Yearly Trend of Paid Amounts by Age Band
For a specified age_band, using the MEMBERSHIP and CLAIM tables, return one row per calendar year showing the total paid_amt across all members in that age_band. Include columns age_band, year, and total_paid_amt for all years where claims exist.
Tables
MEMBERSHIP(id INTEGER, age_band VARCHAR(10), gender CHAR(1), zipcode CHAR(5))
CLAIM(id INTEGER, claim_date DATE, paid_amt DECIMAL(10,2), insurance VARCHAR(20))
Hints
- Join CLAIM to MEMBERSHIP on member id.
- Filter MEMBERSHIP by the desired age_band.