Quick Overview

This question evaluates competency in pandas-based tabular data manipulation—specifically DataFrame merging, groupby aggregation, pivot table construction, counting unique values, and conditional flagging with numpy—within the domain of Data Manipulation (SQL/Python) and is primarily at the practical application level.

Use pandas to aggregate, pivot, and label

Company: CVS Health

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Given two pandas DataFrames, write code to: (1) merge and aggregate revenue; (2) produce a 2x2 pivot; (3) compute per-state counts with value_counts, nunique/size; (4) add a binary flag via np.where. Reuse the merged DataFrame across parts (assume it persists between steps). Data (toy, representative) users user_id | is_member | state | age 101 | 1 | CA | 29 102 | 0 | NY | 41 103 | 1 | CA | 35 104 | 0 | TX | 50 orders order_id | user_id | channel | amount | status 7001 | 101 | SMS | 12.00 | delivered 7002 | 102 | Email | 5.00 | delivered 7003 | 103 | SMS | 7.00 | delivered 7004 | 103 | Email | 4.00 | delivered 7005 | 101 | Organic | 3.50 | delivered 7006 | 104 | SMS | 6.00 | undelivered Tasks - Step 1: Merge orders with users on user_id (left join). Compute two outputs: (a) total delivered revenue by channel; (b) delivered revenue by channel restricted to members (is_member==1). Show groupby(...).sum() results as DataFrames. - Step 2: Create a 2x2 pivot of delivered revenue with index=is_member (0/1) and columns=channel in ['SMS','Email'] only, values=amount, aggfunc='sum', fill missing cells with 0. Use pivot_table with aggfunc='sum'. - Step 3: From the merged DataFrame, compute per-state: total orders (size) and unique purchasers (nunique of user_id). Return the top-2 states by total orders using sort_values. - Step 4: Add column high_value_flag = 1 if (user's lifetime delivered amount >= 15) OR (number of delivered SMS orders per user >= 2), else 0. Use np.where and prior groupby aggregations to avoid SettingWithCopy warnings. Show the final head with relevant columns.

Overview: This question evaluates competency in pandas-based tabular data manipulation—specifically DataFrame merging, groupby aggregation, pivot table construction, counting unique values, and conditional flagging with numpy—within the domain of Data Manipulation (SQL/Python) and is primarily at the practical application level.

Read the full CVS Health Data Scientist interview experience this question came from

Join users to orders and aggregate delivered revenue by channel (overall vs members)

You are given two tables: users and orders. Task: LEFT JOIN orders to users on user_id, then compute delivered revenue (sum of amount where status = 'delivered') by channel for: 1) all users 2) members only (is_member = 1) Return a single result set with columns: - revenue_scope (values: 'all_users' or 'members_only') - channel - delivered_revenue Order the output by revenue_scope, then channel.

Tables

users(user_id INT, is_member TINYINT, state CHAR(2), age INT)

orders(order_id INT, user_id INT, channel VARCHAR(20), amount DECIMAL(10,2), status VARCHAR(20))

Hints

  1. Filter to delivered orders before aggregating.
  2. Use UNION ALL with a label column to return both overall and members-only results in one query.

2x2 pivot: delivered revenue by is_member (rows) and channel (SMS/Email columns)

Using users and orders, compute a 2x2 pivot of delivered revenue where: - rows are is_member (0 or 1) - columns are channel in ('SMS','Email') only - values are SUM(amount) over delivered orders only (status = 'delivered') Return columns: - is_member - sms_revenue - email_revenue Missing cells should be 0 (i.e., return 0 when there is no delivered revenue for that combination). Order by is_member.

Tables

users(user_id INT, is_member TINYINT, state CHAR(2), age INT)

orders(order_id INT, user_id INT, channel VARCHAR(20), amount DECIMAL(10,2), status VARCHAR(20))

Hints

  1. A pivot can be implemented with conditional aggregation (SUM(CASE WHEN ... THEN amount END)).
  2. LEFT JOIN from users ensures you still get both is_member values even if one side had no delivered revenue.

Top states by orders and unique purchasers

Write a PostgreSQL query using a LEFT JOIN from orders to users on user_id. For each state, compute total_orders as COUNT(*) and unique_purchasers as COUNT(DISTINCT o.user_id). Return only the top 2 states by total_orders descending, breaking ties by state ascending. Output columns: state, total_orders, unique_purchasers.

Tables

users(user_id INT, is_member SMALLINT, state CHAR(2), age INT)

orders(order_id INT, user_id INT, channel VARCHAR(20), amount DECIMAL(10,2), status VARCHAR(20))

Hints

  1. Use COUNT(*) for total orders and COUNT(DISTINCT user_id) for unique purchasers.
  2. Add a secondary ORDER BY (state) to make the top-2 selection deterministic under ties.

Add a high_value_flag based on per-user delivered spend or delivered SMS order count

Compute a binary flag high_value_flag for each order row after joining orders to users. Definition (per user): high_value_flag = 1 if either condition is true: - user's lifetime delivered amount (sum of amount where status='delivered') is >= 15 OR - number of delivered SMS orders for the user is >= 2 Otherwise high_value_flag = 0. Return one row per order with these columns: order_id, user_id, state, status, amount, user_lifetime_delivered_amount, user_delivered_sms_orders, high_value_flag Order by order_id.

Tables

users(user_id INT, is_member TINYINT, state CHAR(2), age INT)

orders(order_id INT, user_id INT, channel VARCHAR(20), amount DECIMAL(10,2), status VARCHAR(20))

Hints

  1. Compute per-user aggregates in a CTE, then join them back to the order-level rows.
  2. Use conditional aggregation to compute delivered spend and delivered SMS order counts.

Loading coding console...