Aggregate radiology spend and derive fiscal month
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Using Python/pandas, complete the tasks below. Assume the following CSV-like input, where service_dt is object dtype and amounts may be negative (adjustments). Treat missing paid_amt as 0 when aggregating, but do not create NaNs during type conversion.
radiology.csv (toy data)
claim_id,procedure_group,service_dt,paid_amt
1001,CT,2020-07-01 13:05:00,120.00
1002,MRI,2020-10-15 09:30:00,250.00
1003,CT,2020-10-20 11:00:00,-20.00
1004,XRay,2020-12-05 08:00:00,80.00
1005,MRI,2020-01-02 14:10:00,
Tasks
1) Load the file robustly (explicit dtypes, parse_dates) and aggregate paid_amt by procedure_group to produce columns: procedure_group, paid_amt_sum (rounded to 2 decimals).
2) Add each group's percentage of total paid_amt as pct_of_total (0–100 with two decimals). Ensure total uses post-cleaning values and is not double-counted.
3) Convert service_dt from object to datetime and add an integer fiscal_month column where fiscal year starts on October 1 (Oct=1, Nov=2, …, Sep=12). Validate on the sample rows so that 2020-10-15 maps to fiscal_month=2. Show the resulting dtypes and demonstrate that the transformation is vectorized (no per-row Python loops). Handle bad or missing dates gracefully (coerce to NaT, then impute fiscal_month with 0 for unknown).
Overview: This question evaluates a candidate's competency in data manipulation and preprocessing using SQL/Python, covering robust dtype specification, null handling, numeric aggregation and percentage computation, datetime parsing, and fiscal-month derivation.
Aggregate paid amounts by procedure group
You are given a table of radiology claims. The paid_amt column can contain negative values (adjustments), and some paid_amt values may be missing (NULL). Treat missing paid_amt as 0 when aggregating, and include negative values in the arithmetic sum.
Write a SQL query to aggregate paid_amt by procedure_group and return one row per procedure_group with the following columns:
- procedure_group
- paid_amt_sum: the sum of paid_amt for that procedure_group, treating NULL as 0, rounded to 2 decimal places.
Do not filter out any procedure_group values. The result can be returned in any row order.
Tables
radiology_claims(claim_id INT, procedure_group VARCHAR(20), service_dt VARCHAR(19), paid_amt DECIMAL(10,2))
Hints
- Use COALESCE (or a similar function) to treat NULL paid_amt as 0 before summing.
- Apply ROUND(..., 2) to the aggregated sum, not to individual rows.
Add percentage of total paid amount per procedure group
Using the same radiology_claims table, extend the previous aggregation.
Compute, for each procedure_group:
- paid_amt_sum: the sum of paid_amt (treating NULL as 0), rounded to 2 decimals.
- pct_of_total: that group’s percentage share of the overall paid_amt total, on a 0–100 scale, rounded to 2 decimals.
The overall total must be calculated from the same cleaned paid_amt values (NULL treated as 0) and must not be double-counted. Return one row per procedure_group with columns:
- procedure_group
- paid_amt_sum
- pct_of_total
The result can be returned in any row order.
Tables
radiology_claims(claim_id INT, procedure_group VARCHAR(20), service_dt VARCHAR(19), paid_amt DECIMAL(10,2))
Hints
- First compute per-group sums in a CTE or subquery, then compute the overall total in a separate step.
- Use a CROSS JOIN (or scalar subquery) to make the total available to each group when calculating pct_of_total.
Derive fiscal month from service date with October fiscal year start
## Derive a fiscal month from a string service date (fiscal year starts in October)
You are given a single table, **`radiology_claims`**, where the `service_dt` column stores the service date and time as a **string** (e.g. `'2020-10-15 09:30:00'`). Some rows may have a `service_dt` that is `NULL` or that is not a valid `YYYY-MM-DD HH:MI:SS` timestamp.
Write **one** PostgreSQL query that, for **every** row in `radiology_claims`, returns:
1. **`service_ts`** — `service_dt` safely parsed into a `timestamp`. If `service_dt` is `NULL` or cannot be parsed as a valid `YYYY-MM-DD HH:MI:SS` value, `service_ts` must be `NULL` (the query must not error on a bad string).
2. **`fiscal_month`** — an **integer** giving the fiscal month under a fiscal year that **starts on October 1**:
- October = 1, November = 2, December = 3, January = 4, ..., September = 12.
- When `service_ts` is `NULL` (unknown / invalid date), set `fiscal_month = 0`.
### Required output columns (in this order)
- `claim_id`
- `procedure_group`
- `service_dt` (the original string)
- `service_ts` (parsed timestamp, or `NULL`)
- `fiscal_month` (integer)
### Sorting
Return one row per claim, **ordered by `claim_id` ascending**.
> Note: the original problem assumed a SQL-Server `TRY_CONVERT`/`MONTH(...)` dialect. This is graded on **PostgreSQL**, so use a regex guard plus `to_timestamp(...)` for the safe parse and `EXTRACT(MONTH FROM ...)` for the month.
Tables
radiology_claims(claim_id INT, procedure_group VARCHAR(20), service_dt VARCHAR(19), paid_amt DECIMAL(10,2))
Hints
- PostgreSQL has no TRY_CAST that yields NULL on failure — guard the conversion with a regex (`service_dt ~ '^[0-9]{4}-...$'`) and only then call `to_timestamp(service_dt, 'YYYY-MM-DD HH24:MI:SS')`; otherwise produce NULL.
- Use `EXTRACT(MONTH FROM service_ts)` for the calendar month, then a CASE: months >= 10 subtract 9, months <= 9 add 3.