Revenue by state
mediumRevenue by state
Amazon Pandas Interview Question
Amazon's regional marketing team wants to know which states its delivered revenue comes from.
Using the orders and customers DataFrames, find each customer state and the total revenue from delivered orders as revenue, rounded to 2 decimal places. Revenue is the quantity sold times the unit price. Sort the rows by revenue, highest first. Assign the answer to result.
Asked of
- Data Analyst
- Product Analyst
- Business Analyst
- Analytics Engineer
- Data Scientist
ordersDataFrame30 rows
| Column Name | Type |
|---|---|
| order_id | int64 |
| customer_id | int64 |
| order_date | str |
| category | str |
| quantity | int64 |
| unit_price | float64 |
| status | str |
customersDataFrame15 rows
| Column Name | Type |
|---|---|
| customer_id | int64 |
| customer_name | str |
| state | str |
| signup_date | str |
| prime_member | bool |
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 |
customersExample Input
| customer_id | customer_name | state | signup_date | prime_member |
|---|---|---|---|---|
| 501 | Avery Johnson | CA | 2022-03-14 | true |
| 502 | Mateo Rivera | TX | 2021-11-02 | false |
| 503 | Hannah Brooks | NY | 2023-01-19 | true |
| 504 | Jamal Carter | WA | 2022-08-07 | true |
| 505 | Sofia Nguyen | CA | 2020-05-25 | true |
| 506 | Ethan Walsh | TX | 2023-06-30 | false |
| 507 | Leila Haddad | NY | 2021-02-11 | true |
| 508 | Ryan Patel | FL | 2022-12-03 | false |
Example Output
| state | revenue |
|---|---|
| TX | 400 |
| CA | 64.97 |
| NY | 35.88 |
| WA | 25.5 |
Explanation
In the example, customers in Texas had delivered orders worth 249.00, 22.00 and 129.00, a total of 400.00, so Texas comes first. Returned and canceled orders are left out, which is why New York only counts one order, worth 35.88.
The example above is a small slice of the data. Your code runs against the full DataFrames.
Company
Amazon
Difficulty
medium
Topic
joins
Language
Pandas
Your code
Loading Python in the background. You can start writing.