Aggregate radiology spend and derive fiscal month

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Technical Screen rounds at CVS Health.

Q: What role is this question designed for?

This question is commonly asked for Data Scientist candidates at CVS Health during technical interviews.

Question

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).

PracHub · Accepted Answer

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.

Quick Overview

Quick Overview