Largest order per customer
hardLargest order per customer
Amazon Pandas Interview Question
Amazon's customer insights team wants to see each customer's biggest purchase so far.
Using the orders DataFrame, find each customer's most valuable delivered order. Show the customer_id, the order_id and the order's value as order_value, rounded to 2 decimal places. An order's value is the quantity times the unit price, and no customer has two orders with the same top value. Assign the answer to result.
Asked of
- Data Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
- ML Engineer
ordersDataFrame30 rows
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| order_date | str |
| category | str |
| quantity | int64 |
| unit_price | float64 |
| status | str |
ordersExample Input
| order_id | customer_id | order_date | category | quantity | unit_price | status |
|---|---|---|---|---|---|---|
| 10001 | 501 | 2024-05-01 | Books | 2 | 14.99 | delivered |
| 10002 | 502 | 2024-05-01 | Electronics | 1 | 249 | delivered |
| 10003 | 503 | 2024-05-02 | Home | 3 | 19.5 | returned |
| 10004 | 501 | 2024-05-02 | Toys | 1 | 34.99 | delivered |
| 10005 | 504 | 2024-05-03 | Grocery | 6 | 4.25 | delivered |
| 10006 | 505 | 2024-05-03 | Electronics | 2 | 89.99 | canceled |
| 10007 | 502 | 2024-05-04 | Books | 1 | 22 | delivered |
| 10008 | 506 | 2024-05-04 | Home | 1 | 129 | delivered |
| 10009 | 507 | 2024-05-05 | Grocery | 12 | 2.99 | delivered |
| 10010 | 503 | 2024-05-05 | Electronics | 1 | 59.99 | returned |
Example Output
| customer_id | order_id | order_value |
|---|---|---|
| 501 | 10004 | 34.99 |
| 502 | 10002 | 249 |
| 504 | 10005 | 25.5 |
| 506 | 10008 | 129 |
| 507 | 10009 | 35.88 |
Explanation
Customer 501 had two delivered orders in the example, worth 29.98 and 34.99, so order 10004 is that customer's largest. Customer 503's orders were both returned, so that customer does not appear.
The example above is a small slice of the data. Your code runs against the full DataFrames.
Company
Amazon
Difficulty
hard
Topic
ranking
Language
Pandas
Your code
Loading Python in the background. You can start writing.