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.
Quick Answer: 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.