Coding questions · Amazon

Identify Top Spenders and Segment Customers Using Python

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.

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.

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.

Table `orders`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
cust_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
product	VARCHAR(50)	NOT NULL
order_amount	DECIMAL(10,2)	NOT NULL, CHECK (order_amount >= 0)
shipping_option_id	INTEGER	NOT NULL

Sample data:

cust_id	order_amount	order_date	order_id	product	shipping_option_id
200	750	2023-01-02	1001	camera	2
201	80	2023-01-05	1002	shoes	1
200	1200	2023-02-10	1003	laptop	2
202	45	2023-02-15	1004	clothes	1
201	700	2023-03-08	1005	camera	3

Expected output

List of customers who either have fewer than 2 total orders or have spent less than 100 overall.

cust_id
202

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.

Table `orders`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
cust_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
product	VARCHAR(50)	NOT NULL
order_amount	DECIMAL(10,2)	NOT NULL, CHECK (order_amount >= 0)
shipping_option_id	INTEGER	NOT NULL

Sample data:

cust_id	order_amount	order_date	order_id	product	shipping_option_id
200	750	2023-01-02	1001	camera	2
201	80	2023-01-05	1002	shoes	1
200	1200	2023-02-10	1003	laptop	2
202	45	2023-02-15	1004	clothes	1
201	700	2023-03-08	1005	camera	3

Expected output

Top 5 customers per month by number of orders with tie-breakers by total amount and cust_id.

cust_id	rank	total_amount	total_orders	year_month
200	1	750	1	2023-01
201	2	80	1	2023-01
200	1	1200	1	2023-02
202	2	45	1	2023-02
201	1	700	1	2023-03

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.

Table `orders`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
cust_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
product	VARCHAR(50)	NOT NULL
order_amount	DECIMAL(10,2)	NOT NULL, CHECK (order_amount >= 0)
shipping_option_id	INTEGER	NOT NULL

Sample data:

cust_id	order_amount	order_date	order_id	product	shipping_option_id
200	750	2023-01-02	1001	camera	2
201	80	2023-01-05	1002	shoes	1
200	1200	2023-02-10	1003	laptop	2
202	45	2023-02-15	1004	clothes	1
201	700	2023-03-08	1005	camera	3

Expected output

Top 5 customers per month by total spend with tie-breakers by order count and cust_id.

cust_id	rank	total_amount	total_orders	year_month
200	1	750	1	2023-01
201	2	80	1	2023-01
200	1	1200	1	2023-02
202	2	45	1	2023-02
201	1	700	1	2023-03

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.

Table `orders`

column	type	constraints
order_id	INTEGER	PRIMARY KEY, NOT NULL
cust_id	INTEGER	NOT NULL
order_date	DATE	NOT NULL
product	VARCHAR(50)	NOT NULL
order_amount	DECIMAL(10,2)	NOT NULL, CHECK (order_amount >= 0)
shipping_option_id	INTEGER	NOT NULL

Sample data:

cust_id	order_amount	order_date	order_id	product	shipping_option_id
200	750	2023-01-02	1001	camera	2
201	80	2023-01-05	1002	shoes	1
200	1200	2023-02-10	1003	laptop	2
202	45	2023-02-15	1004	clothes	1
201	700	2023-03-08	1005	camera	3

Expected output

One row per customer with total spend per product in separate columns.

camera	clothes	cust_id	laptop	shoes
750	0	200	1200	0
700	0	201	0	80
0	45	202	0	0

Hints

Use conditional aggregation with CASE WHEN.
Sum per product and group by cust_id.

Scenario

Question

Hints

Identify low-activity customers

Table orders

Expected output

Hints

Top 5 by orders monthly

Table orders

Expected output

Hints

Top 5 by amount monthly

Table orders

Expected output

Hints

Pivot spend by product

Table orders

Expected output

Hints

Table `orders`

Table `orders`

Table `orders`

Table `orders`