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.
Quick Answer: 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.