Calculate annual percentages and YoY by cohorts
Company: CVS Health
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Answer both SQL and Python parts. Be precise about deduping and denominator choices.
SQL schema (sample rows):
orders
order_id | user_id | order_date
1 | 101 | 2023-01-10
2 | 102 | 2023-05-03
3 | 101 | 2024-02-12
4 | 103 | 2024-11-20
5 | 104 | 2024-12-28
order_items
order_id | product_id | qty
1 | 10 | 1
1 | 11 | 2
2 | 11 | 1
3 | 12 | 1
4 | 10 | 1
5 | 13 | 1
products
product_id | name | category
10 | Widget Pro | Subscription
11 | Widget | Standard
12 | Gadget Pro | Subscription
13 | Service | Standard
users
user_id | age | location
101 | 27 | NY
102 | 42 | CA
103 | 35 | NY
104 | 23 | TX
A) Three-table percentage (CTE/subquery/case-when allowed): For calendar year 2024, compute the percentage of distinct orders that contained at least one product with category = 'Subscription'. Count each order at most once even if it has multiple subscription items. Output a single row with pct_subscription_2024 rounded to two decimals.
B) YoY change by location and age group: Define age_group buckets as [18–29], [30–44], [45+]. For each (location, age_group) present in users, compute distinct-order counts in 2023 and 2024 and the YoY percent change = (orders_2024 - orders_2023) / NULLIF(orders_2023, 0). Return columns: location, age_group, orders_2023, orders_2024, yoy_pct_change. If orders_2023 = 0, return NULL for yoy_pct_change (avoid divide-by-zero). Assume an order belongs to the age/location of its user at order time. You may use window functions or conditional aggregation.
Python part (use pandas): You are given two DataFrames with the same data as above: df_orders(order_id, user_id, order_date), df_products(product_id, name, category), df_users(user_id, age, location). For year Y = 2024, compute the number of unique users who purchased any product whose name contains the substring 'Pro' (case-insensitive). Return a DataFrame with columns [location, age_group, unique_users] where age_group uses the same bins as in part B, sorted by unique_users descending, then location ascending. You must use merge, str.contains, groupby, and an aggregation (nunique), and ensure stable sorting for ties.
Overview: This question evaluates SQL and pandas data manipulation skills, specifically joins, conditional aggregation, deduplication, cohort-based percentage computations, and year-over-year change calculations.
Read the full CVS Health Data Scientist interview experience this question came from
Percentage of 2024 orders containing a Subscription product
Using the tables below, for calendar year 2024 (2024-01-01 through 2024-12-31 inclusive), compute the percentage of distinct orders that contained at least one product with category = 'Subscription'.
Rules:
- Count each order at most once, even if it has multiple subscription items.
- The denominator is the total number of distinct orders in 2024.
- Output a single row with a single column pct_subscription_2024, rounded to two decimals.
You may use CTEs/subqueries and CASE WHEN logic.
Tables
orders(order_id INT, user_id INT, order_date DATE)
order_items(order_id INT, product_id INT, qty INT)
products(product_id INT, name VARCHAR(100), category VARCHAR(50))
Hints
- First reduce to one row per order with a 0/1 flag for whether it contains any Subscription product.
- Compute percent = 100 * (subscription_orders / total_orders) and round to 2 decimals.
YoY order count change by location and age group
Using the tables below, define age_group buckets based on users.age:
- '18-29' for ages 18 through 29
- '30-44' for ages 30 through 44
- '45+' for ages 45 and above
For each (location, age_group) combination present in the users table, compute:
- distinct-order count in calendar year 2023 (2023-01-01 through 2023-12-31)
- distinct-order count in calendar year 2024 (2024-01-01 through 2024-12-31)
- yoy_pct_change = (orders_2024 - orders_2023) / NULLIF(orders_2023, 0)
Rules:
- An order belongs to the age/location of its user.
- Return NULL for yoy_pct_change when orders_2023 = 0 (avoid divide-by-zero).
Return columns: location, age_group, orders_2023, orders_2024, yoy_pct_change.
Tables
orders(order_id INT, user_id INT, order_date DATE)
users(user_id INT, age INT, location VARCHAR(2))
Hints
- Start from users to ensure you return every (location, age_group) present, even if a group has zero orders in a year.
- Use conditional COUNT(DISTINCT ...) for 2023 and 2024, then compute YoY with NULLIF to avoid divide-by-zero.