Coding questions · CVS Health
Calculate Medical Claims by Age and Gender in 2024
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.
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.
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.
Table MEMBERSHIP
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| age_band | VARCHAR(10) | NOT NULL |
| gender | CHAR(1) | NOT NULL |
| zipcode | CHAR(5) | NOT NULL |
Sample data:
| age_band | gender | id | zipcode |
|---|---|---|---|
| 18-25 | F | 1 | 90001 |
| 26-35 | M | 2 | 10001 |
| 18-25 | M | 3 | 02139 |
| 36-45 | F | 4 | 60616 |
| 26-35 | F | 5 | 94105 |
Table CLAIM
| column | type | constraints |
|---|---|---|
| id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES MEMBERSHIP(id) |
| claim_date | DATE | NOT NULL |
| paid_amt | DECIMAL(10,2) | NOT NULL |
| insurance | VARCHAR(20) | NOT NULL |
Sample data:
| claim_date | id | insurance | paid_amt |
|---|---|---|---|
| 2024-03-15 | 1 | PPO | 550 |
| 2023-11-20 | 1 | HMO | 200 |
| 2024-01-05 | 2 | HMO | 1300 |
| 2024-07-23 | 3 | PPO | 75 |
| 2022-12-31 | 4 | PPO | 800 |
Expected output
Single row with the 2024 total paid_amt for the specified age_band and gender. With the sample data and choosing age_band = '18-25' and gender = 'M', only member id=3 qualifies and has one 2024 claim for 75.00.
| age_band | gender | total_paid_amt | year |
|---|---|---|---|
| 18-25 | M | 75 | 2024 |
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'.
- Use COALESCE(SUM(...), 0) so demographics with no 2024 claims return 0.
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.
Table MEMBERSHIP
| column | type | constraints |
|---|---|---|
| id | INTEGER | PRIMARY KEY, NOT NULL |
| age_band | VARCHAR(10) | NOT NULL |
| gender | CHAR(1) | NOT NULL |
| zipcode | CHAR(5) | NOT NULL |
Sample data:
| age_band | gender | id | zipcode |
|---|---|---|---|
| 18-25 | F | 1 | 90001 |
| 26-35 | M | 2 | 10001 |
| 18-25 | M | 3 | 02139 |
| 36-45 | F | 4 | 60616 |
| 26-35 | F | 5 | 94105 |
Table CLAIM
| column | type | constraints |
|---|---|---|
| id | INTEGER | NOT NULL, FOREIGN KEY REFERENCES MEMBERSHIP(id) |
| claim_date | DATE | NOT NULL |
| paid_amt | DECIMAL(10,2) | NOT NULL |
| insurance | VARCHAR(20) | NOT NULL |
Sample data:
| claim_date | id | insurance | paid_amt |
|---|---|---|---|
| 2024-03-15 | 1 | PPO | 550 |
| 2023-11-20 | 1 | HMO | 200 |
| 2024-01-05 | 2 | HMO | 1300 |
| 2024-07-23 | 3 | PPO | 75 |
| 2022-12-31 | 4 | PPO | 800 |
Expected output
One row per year showing the total paid_amt for the specified age_band across all years with claims. For age_band '18-25', members 1 and 3 have claims in 2023 and 2024, giving totals 200.00 and 625.00 respectively.
| age_band | total_paid_amt | year |
|---|---|---|
| 18-25 | 200 | 2023 |
| 18-25 | 625 | 2024 |
Hints
- Join CLAIM to MEMBERSHIP on member id.
- Filter MEMBERSHIP by the desired age_band.
- Use EXTRACT(YEAR FROM claim_date) (or equivalent date function) and GROUP BY year to compute yearly totals.