Compute specialty spend share and top age band
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: easy
Interview Round: Technical Screen
You are given healthcare claims data split across member tables.
## Tables
Assume the following schemas (types may be adapted to your SQL dialect):
### `mem1`
- `member_id` INT PRIMARY KEY
- `age_band` VARCHAR
### `mem2`
- `member_id` INT PRIMARY KEY
- `age_band` VARCHAR
### `claim`
- `claim_id` INT PRIMARY KEY
- `member_id` INT
- `specialty` VARCHAR
- `paid_amt` DECIMAL(12,2)
- `claim_date` DATE
### `risk` (provided but not necessarily needed)
- `member_id` INT
- `risk_score` DECIMAL(10,4)
Assume `claim.member_id` joins to either `mem1.member_id` or `mem2.member_id`.
Assume `claim_date` is in UTC and “year = 2017” means calendar year 2017.
---
## Part A (SQL)
For each `specialty`, compute:
1) `total_paid_amt` = total paid amount for that specialty
2) `paid_share` = that specialty’s share of total paid amount across all claims
**Output columns:** `specialty`, `total_paid_amt`, `paid_share`.
---
## Part B (SQL)
Considering only claims in calendar year 2017:
1) Union `mem1` and `mem2` into a single member set.
2) Join members to claims.
3) Compute claim counts per `age_band`.
4) Return the age band(s) with the maximum number of claims (keep ties).
**Output columns:** `age_band`, `claim_cnt`.
---
## Part C (Python / Pandas)
You are given a Pandas DataFrame `df_claims` with columns:
- `paid_amt` (numeric)
- `gender` (values are `'M'` or `'F'`)
- `claim_timestamp` (string or datetime-like)
Tasks:
1) Recode `gender` from `M/F` to `male/female`.
2) Compute total `paid_amt` for **each month in 2017**, grouped by `gender` and month.
**Output:** a DataFrame with columns like `gender`, `month` (1–12), `total_paid_amt` (and optionally `year` if you keep it).
Overview: This question evaluates data manipulation and aggregation skills in SQL and Pandas, including joins/unions across disjoint member tables, computation of specialty spend shares, identification of top age bands by claim counts with tie handling, year-based date filtering, and categorical recoding with monthly grouping.
Read the full CVS Health Data Scientist interview experience this question came from
Community answers
Answer by jeremy
(answers checked by Chatgpt)
question 3
import numpy as np
import pandas as pd
result = (
df_claims
.assign(
claim_timestamp=lambda x: pd.to_datetime(x["claim_timestamp"]),
gender=lambda x: np.where(x["gender"] == "M", "male", "female"),
month=lambda x: x["claim_timestamp"].dt.month,
year=lambda x: x["claim_timestamp"].dt.year
)
.query("year == 2017")
.groupby(["gender", "month"], as_index=False)
.agg(total_paid_amt=("paid_amt", "sum"))
)
result
question 1
WITH t AS (
SELECT
specialty,
SUM(paid_amt) AS total_paid_amt
FROM claim
GROUP BY specialty
)
SELECT
specialty,
total_paid_amt,
total_paid_amt / SUM(total_paid_amt) OVER () AS paid_share
FROM t;
question 2
WITH t AS (
SELECT *
FROM mem1
UNION ALL
SELECT *
FROM mem2
),
s AS (
SELECT
age_band,
COUNT(claim_id) AS claim_count
FROM claim AS c
LEFT JOIN t
ON t.member_id = c.member_id
WHERE EXTRACT(YEAR FROM c.claim_date) = 2017
GROUP BY age_band
),
ct AS (
SELECT
age_band,
claim_count,
DENSE_RANK() OVER (ORDER BY claim_count DESC) AS rank
FROM s
)
SELECT
age_band,
claim_count
FROM ct
WHERE rank = 1;