Identify Top Spenders and Segment Customers Using Python
Company: Amazon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
orders
+----------+---------+------------+----------+--------------+-------------------+
| order_id | cust_id | order_date | product | order_amount | shipping_option_id |
+----------+---------+------------+----------+--------------+-------------------+
| 1001 | 200 | 2023-01-02 | camera | 750 | 2 |
| 1002 | 201 | 2023-01-05 | shoes | 80 | 1 |
| 1003 | 200 | 2023-02-10 | laptop | 1200 | 2 |
| 1004 | 202 | 2023-02-15 | clothes | 45 | 1 |
| 1005 | 201 | 2023-03-08 | camera | 700 | 3 |
+----------+---------+------------+----------+--------------+-------------------+
##### Scenario
E-commerce order insights – marketing wants to segment customers and find top spenders.
##### Question
Using Python (pandas), return the list of cust_id who have placed fewer than 2 orders OR have spent less than 100 in total. Write Python code that finds, for each calendar month, the top 5 customers by
(a) highest total number of orders and
(b) highest total order cost. Produce a wide-format DataFrame: columns = ['cust_id','camera','shoes','laptop','clothes'] where each cell shows how much that customer has spent on the product type.
##### Hints
Start with groupby aggregations; for the wide table use pivot_table or groupby+unstack.
Overview: This question evaluates proficiency in data manipulation and aggregation using SQL/Python (pandas), covering customer segmentation, monthly grouping and ranking, and reshaping transactional data into a wide format for product-level spend analysis.
Identify low-activity customers
Return cust_id values for customers who have placed fewer than 2 orders OR whose total spend is less than 100 across all their orders.
Tables
orders(order_id INTEGER, cust_id INTEGER, order_date DATE, product VARCHAR(50), order_amount DECIMAL(10,2), shipping_option_id INTEGER)
Hints
- Aggregate by cust_id to get order counts and total spend.
- Filter using an OR condition on count and sum.
Top 5 by orders monthly
For each calendar month, find the top 5 customers ranked by highest total number of orders; break ties by higher total amount, then lower cust_id.
Tables
orders(order_id INTEGER, cust_id INTEGER, order_date DATE, product VARCHAR(50), order_amount DECIMAL(10,2), shipping_option_id INTEGER)
Hints
- Group by month (DATE_TRUNC) and cust_id.
- Use ROW_NUMBER with PARTITION BY month for ranking.
Top 5 by amount monthly
For each calendar month, find the top 5 customers ranked by highest total order amount; break ties by higher order count, then lower cust_id.
Tables
orders(order_id INTEGER, cust_id INTEGER, order_date DATE, product VARCHAR(50), order_amount DECIMAL(10,2), shipping_option_id INTEGER)
Hints
- Aggregate monthly totals per customer.
- Rank by total_amount with ROW_NUMBER and tie-breakers.
Pivot spend by product
Produce a wide-format table with columns ['cust_id','camera','shoes','laptop','clothes'] where each cell is the total amount the customer spent on that product.
Tables
orders(order_id INTEGER, cust_id INTEGER, order_date DATE, product VARCHAR(50), order_amount DECIMAL(10,2), shipping_option_id INTEGER)
Hints
- Use conditional aggregation with CASE WHEN.
- Sum per product and group by cust_id.