← CVS Health Interview Insights

CVS Health·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Apr 2026

Summary

CVS Health data scientist interview that was basically a pandas gauntlet. Four connected tasks on the same merged DataFrame, each building on the last. More hands-on than I expected for a DS role at a health company.

Questions Asked (4)

Q1

Given two DataFrames (users and orders), merge them on user_id with a left join, then compute total delivered revenue grouped by channel, and separately compute delivered revenue by channel for members only.

Product Analytics & MetricsData Modeling
Author's notes

Pretty standard groupby stuff but the 'members only' filter tripped me up slightly because I initially filtered before merging instead of after.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the join keys and the definition of delivered revenue (e.g., order status = 'delivered'). Then perform a left join of users and orders on user_id, filter to delivered orders, and group by channel to compute total revenue. For members only, apply an additional filter on the user's membership status before grouping.

Pro tip: Always validate the join by checking for duplicate user_ids and ensuring the row count matches expectations. Also, consider using a left join to preserve all users, even those without orders, which is crucial for accurate channel-level metrics.

1. Clarify requirements and data schema

Confirm the join key (user_id), the definition of 'delivered' (e.g., order_status = 'delivered'), and how 'channel' and 'membership' are represented in the data.

2. Perform left join and filter delivered orders

Merge users and orders on user_id using a left join to keep all users. Then filter the resulting DataFrame to only include orders with status 'delivered'.

3. Compute total delivered revenue by channel

Group the filtered data by channel and sum the revenue column to get total delivered revenue per channel.

4. Compute delivered revenue by channel for members only

From the filtered data, further filter to rows where the user is a member (e.g., is_member = True), then group by channel and sum revenue.

5. Validate and present results

Check for anomalies (e.g., nulls, unexpected zeros) and ensure the two revenue breakdowns are consistent. Present the results clearly, perhaps in a side-by-side comparison.

Key Points to Mention

  • Left join ensures all users are included, even those without orders, which is important for accurate channel-level aggregation.
  • Filtering for delivered orders is necessary to avoid including cancelled, pending, or returned orders in revenue calculations.
  • Grouping by channel and summing revenue gives the total delivered revenue per channel.
  • For members only, apply an additional filter on the user's membership status before grouping.
  • Validate the join by checking for duplicate user_ids and ensuring the row count matches expectations.
  • Consider handling nulls in revenue or channel columns appropriately (e.g., fill with 0 or exclude).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q2

Using the merged DataFrame, build a 2x2 pivot table of delivered revenue with membership status as rows and channel (SMS and Email only) as columns, filling missing values with 0.

Data ModelingProduct Analytics & Metrics
Author's notes

I always forget whether pivot_table handles the column subset filtering natively or if you need to pre-filter the rows.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter the merged DataFrame to include only rows where channel is 'SMS' or 'Email'. Then, use the pivot_table function to aggregate delivered revenue, with membership status as the index (rows), channel as the columns, and sum as the aggregation function, filling any missing values with 0.

Pro tip: Always verify the data types and handle missing values appropriately before pivoting; also, consider using the 'observed' parameter if categorical data is involved to avoid empty categories.

1. Filter the DataFrame

Subset the merged DataFrame to include only rows where the channel column is either 'SMS' or 'Email'.

2. Define the pivot parameters

Specify membership status as the index (rows), channel as the columns, and delivered revenue as the values to aggregate.

3. Create the pivot table

Use pd.pivot_table with aggfunc='sum' to aggregate delivered revenue, and set fill_value=0 to replace missing values with 0.

4. Validate the output

Check that the resulting table is 2x2, with the correct row and column labels, and that missing combinations are filled with 0.

Key Points to Mention

  • Use of pandas pivot_table function with appropriate parameters
  • Filtering the DataFrame to include only SMS and Email channels
  • Setting membership status as rows and channel as columns
  • Aggregating delivered revenue using sum
  • Handling missing values with fill_value=0
  • Ensuring the final pivot table is 2x2 as required

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q3

From the merged DataFrame, compute per-state total order count and count of unique purchasers, then return the top 2 states by total orders.

Product Analytics & MetricsAlgorithms & Data Structures
Author's notes

The nunique vs size distinction is the whole point of this question.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the structure of the merged DataFrame and the definitions of 'order count' and 'unique purchasers'. Then use groupby on state to aggregate total orders and nunique on purchaser ID, sort by total orders descending, and select the top 2 states. Finally, present the result as a DataFrame with state, total_orders, and unique_purchasers.

Pro tip: Mention that you would validate the merge didn't duplicate rows (e.g., check for one-to-many relationships) and confirm that 'unique purchasers' means distinct customer IDs, not just distinct orders. This shows attention to data quality and metric definition.

1. Clarify the data and metrics

Ask about the columns in the merged DataFrame, especially the state column, order identifier, and purchaser identifier. Confirm whether 'total order count' means number of orders (rows) or sum of an order quantity column, and whether 'unique purchasers' means distinct customer IDs.

2. Aggregate by state

Use groupby('state') and agg to compute total orders (e.g., count of order IDs or sum of order quantities) and unique purchasers (nunique on purchaser ID). Ensure you handle missing values appropriately.

3. Sort and select top 2

Sort the aggregated DataFrame by total orders in descending order and take the first two rows. Optionally, reset the index to have a clean output.

4. Validate and present

Check that the results make sense (e.g., no negative counts, top states align with expectations). Present the final DataFrame with columns: state, total_orders, unique_purchasers.

Key Points to Mention

  • Use of pandas groupby and agg with named aggregation for clarity
  • Distinction between count (total orders) and nunique (unique purchasers)
  • Handling of potential data issues: duplicates from merge, missing state or purchaser IDs
  • Sorting with sort_values(ascending=False) and head(2) for top states
  • Consideration of ties: if multiple states have the same order count, how to handle? (e.g., include all or use additional tiebreaker)
  • Efficiency: vectorized operations in pandas vs. loops

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.

Q4

Add a binary flag column to the DataFrame: 1 if a user's total lifetime delivered amount is at least 15 OR they have 2 or more delivered SMS orders, otherwise 0. Use np.where and avoid SettingWithCopyWarning.

Data ModelingAlgorithms & Data Structures
Author's notes

This one had more moving parts than it looked.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, compute the two conditions separately: total lifetime delivered amount per user and count of delivered SMS orders per user. Then use np.where to create the binary flag, ensuring the DataFrame is a proper copy to avoid SettingWithCopyWarning.

Pro tip: Always use .copy() when creating a subset DataFrame to avoid SettingWithCopyWarning, and consider using .loc for assignments. Also, verify the data types of the columns used in conditions to prevent unexpected behavior.

1. Understand the requirements

Clarify that the flag should be 1 if a user's total lifetime delivered amount >= 15 OR they have >= 2 delivered SMS orders, else 0. Identify the relevant columns: user ID, delivered amount, order type, and delivery status.

2. Aggregate per user

Group the DataFrame by user ID and compute the sum of delivered amounts and count of delivered SMS orders. Ensure you filter for delivered orders first.

3. Merge aggregates back

Merge the aggregated metrics back to the original DataFrame (or a copy) on user ID, so each row has the user's total delivered amount and SMS order count.

4. Apply np.where

Use np.where with the combined condition (total_delivered_amount >= 15) | (sms_order_count >= 2) to create the binary flag column.

5. Avoid SettingWithCopyWarning

Ensure you are working on a copy of the DataFrame (e.g., using .copy()) before adding the new column, or use .loc to assign the new column.

Key Points to Mention

  • Use of groupby and aggregation functions (sum, count) to compute per-user metrics.
  • Filtering for delivered orders before aggregation.
  • Using np.where for vectorized conditional logic.
  • Combining conditions with logical operators (| for OR).
  • Avoiding SettingWithCopyWarning by using .copy() or .loc.
  • Handling potential missing values or data type issues.

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.