← CVS Health Interview Insights

CVS Health·Data Scientist·Technical Phone Screen·Intermediate

Intermediate
Jun 2026

Summary

CVS Health data scientist interview with a pretty involved technical exercise covering SQL and pandas. The questions were specific enough that I had to actually think rather than just recite patterns. No fluff, just schema and specs.

Questions Asked (3)

Q1

Given orders, order_items, and products tables, compute the percentage of distinct orders in calendar year 2024 that contained at least one Subscription-category product. Each order should be counted only once regardless of how many subscription items it has. Return a single row with the result rounded to two decimal places.

Product Analytics & MetricsData Modeling
Author's notes

The deduplication part is what gets you.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, identify distinct orders in 2024 that contain at least one product in the Subscription category by joining orders, order_items, and products. Then compute the percentage by dividing the count of such orders by the total distinct orders in 2024, and round to two decimal places.

Pro tip: Use a semi-join or EXISTS subquery to avoid duplicates and ensure each order is counted once, and explicitly handle NULLs or missing categories to maintain accuracy.

1. Filter orders by year

Select distinct order IDs from the orders table where the order date falls in calendar year 2024.

2. Identify subscription orders

Join order_items and products to find orders that have at least one product with category 'Subscription', using DISTINCT or EXISTS to avoid duplicates.

3. Count distinct orders

Count the number of distinct orders from step 1 (total orders) and the number of distinct orders from step 2 (subscription orders).

4. Calculate percentage

Divide the subscription order count by the total order count, multiply by 100, and round to two decimal places.

Key Points to Mention

  • Use of DISTINCT or EXISTS to ensure each order is counted only once.
  • Correctly joining orders, order_items, and products on their respective keys.
  • Filtering orders by calendar year 2024 using appropriate date functions.
  • Handling potential NULLs in category or missing product matches.
  • Rounding the final percentage to two decimal places.
  • Considering performance implications of large tables and using efficient subqueries.

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

Q2

Using the same schema, compute year-over-year order count change broken down by user location and age group (18-29, 30-44, 45+). Return orders in 2023, orders in 2024, and the YoY percent change. If 2023 count is zero, return NULL instead of dividing by zero.

Product Analytics & MetricsData Modeling
Author's notes

I overcomplicated this initially by trying to write two separate subqueries and union them.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, create a CTE that aggregates order counts by user location, age group, and year (2023 and 2024), using a CASE statement to bucket ages into 18-29, 30-44, and 45+. Then, pivot the yearly counts into separate columns and compute the YoY percent change with a NULLIF or CASE to handle division by zero. Finally, ensure the output includes location, age group, orders_2023, orders_2024, and yoy_percent_change.

Pro tip: Always clarify the definition of 'order count' (e.g., distinct orders vs. order line items) and confirm whether the age should be calculated as of the order date or current date—this shows attention to detail and prevents misinterpretation.

1. Define age buckets and filter years

Use a CASE statement to categorize users into 18-29, 30-44, and 45+ based on their age. Filter the data to only include orders from 2023 and 2024.

2. Aggregate order counts by location, age group, and year

Group by user location, age group, and order year, then count the number of orders (or distinct order IDs) for each combination.

3. Pivot yearly counts into separate columns

Use conditional aggregation (e.g., SUM(CASE WHEN year = 2023 THEN order_count ELSE 0 END)) to create columns for orders_2023 and orders_2024.

4. Compute YoY percent change with zero handling

Calculate the percent change as (orders_2024 - orders_2023) / orders_2023 * 100, but return NULL when orders_2023 is zero to avoid division by zero.

5. Format and validate output

Ensure the final result includes location, age_group, orders_2023, orders_2024, and yoy_percent_change, and verify that the NULL handling works as expected.

Key Points to Mention

  • Use of CASE statements for age bucketing (18-29, 30-44, 45+)
  • Conditional aggregation to pivot yearly counts into columns
  • Handling division by zero with NULLIF or CASE to return NULL
  • Grouping by user location and age group
  • Filtering for years 2023 and 2024
  • Ensuring correct order count definition (e.g., COUNT(DISTINCT order_id))

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

Q3

Using pandas DataFrames with orders, products, and users data, find the number of unique users in 2024 who bought any product with 'Pro' in the name (case-insensitive). Return a DataFrame with location, age group, and unique user count, sorted by unique users descending then location ascending.

Product Analytics & MetricsAlgorithms & Data Structures
Author's notes

str.contains with case=False is the move, but you have to remember to filter on the year before merging or you bloat the join.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, filter orders to 2024 and products with 'Pro' in the name (case-insensitive), then merge with users to get user attributes. Group by location and age group, count unique users, and sort by count descending then location ascending.

Pro tip: Clarify how to handle missing or inconsistent location/age group values (e.g., drop or label as 'Unknown') and mention that you would validate the join keys to avoid duplicates.

1. Filter orders and products

Filter orders to those placed in 2024 and products whose name contains 'Pro' (case-insensitive). Use string methods like .str.contains('pro', case=False).

2. Merge datasets

Merge the filtered orders with products on product_id to get product details, then merge with users on user_id to get location and age group. Ensure correct join types (inner) and check for duplicates.

3. Group and count unique users

Group by location and age group, then count unique user_id using nunique(). This gives the number of unique users per group.

4. Sort and format output

Sort the resulting DataFrame by unique user count descending, then by location ascending. Reset index if needed and return the final DataFrame.

Key Points to Mention

  • Case-insensitive filtering using .str.contains('pro', case=False)
  • Date filtering: ensure order_date is in datetime format and filter for year 2024
  • Using nunique() to count distinct users, not just row counts
  • Handling missing values in location or age group (e.g., drop or fill with 'Unknown')
  • Sorting with multiple keys: sort_values(by=['unique_users', 'location'], ascending=[False, True])
  • Potential data quality checks: duplicate user-product pairs, null user_ids, etc.

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