PracHub
QuestionsPremiumLearningGuidesInterview PrepNEWCoaches

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.

  • Medium
  • Amazon
  • Data Manipulation (SQL/Python)
  • Data Scientist

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.

Last updated: Mar 29, 2026

Loading coding console...

PracHub

Master your tech interviews with 7,500+ real questions from top companies.

Product

  • Questions
  • Learning Tracks
  • Interview Guides
  • Resources
  • Premium
  • For Universities
  • Student Access

Browse

  • By Company
  • By Role
  • By Category
  • Topic Hubs
  • SQL Questions
  • Compare Platforms
  • Discord Community

Support

  • support@prachub.com
  • (916) 541-4762

Legal

  • Privacy Policy
  • Terms of Service
  • About Us

© 2026 PracHub. All rights reserved.

Related Coding Questions

  • Find recommended friend pairs by shared songs - Amazon (medium)
  • Find recommended friend pairs by shared listening - Amazon (easy)
  • Write SQL window functions for D7 retention - Amazon (medium)
  • Find daily first-order merchants with SQL - Amazon (Medium)
  • Design student–course data models and SQL - Amazon (Medium)