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

Loading coding console...