Revenue by Category for One City's Customers
Whetcode Supply is scouting cities for a pop-up shop, and the team works through the shortlist one city at a time. For whichever city is on the table today, they need to know what its customers actually buy. Not how many orders they place — money, split by product category, so the team can decide what to put on the shelves.
Everything needed for that answer is spread across three tables and none of it is on the order. An order row holds a customer number and a product number: it cannot tell you where the buyer lives, what shelf the item belongs on, or what it cost.
customers_df — one row per customer. city is where they are based.
| id | name | city | signup_date |
|---|---|---|---|
| 1 | Priya Nair | Austin | 2022-01-15 |
| 2 | Tom Becker | Berlin | 2022-03-02 |
| 3 | Sofia Rossi | Milan | 2022-05-19 |
| 4 | Liam OConnor | Dublin | 2023-01-08 |
| 5 | Wei Zhang | Austin | 2023-04-27 |
| 6 | Ana Mendes | Austin | 2023-09-12 |
products_df — the catalogue. category is the shelf a product sits on; price is the cost of a single unit.
| id | name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | electronics | 25.0 |
| 2 | Standing Desk | furniture | 350.0 |
| 3 | Desk Lamp | furniture | 40.0 |
| 4 | Mechanical Keyboard | electronics | 85.0 |
| 5 | Notebook Set | office | 12.0 |
orders_df — one row per order placed this year.
| id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 1 | 2 | 2023-02-01 |
| 2 | 1 | 4 | 1 | 2023-02-01 |
| 3 | 2 | 2 | 1 | 2023-02-10 |
| 4 | 3 | 3 | 3 | 2023-03-05 |
| 5 | 4 | 5 | 5 | 2023-03-11 |
| 6 | 1 | 3 | 1 | 2023-04-02 |
| 7 | 5 | 1 | 4 | 2023-05-20 |
| 8 | 2 | 5 | 2 | 2023-05-22 |
| 9 | 3 | 4 | 1 | 2023-06-01 |
| 10 | 5 | 2 | 1 | 2023-06-15 |
Input
All the tables above are already built for you. One line of input arrives: the name of the city under consideration, spelled exactly as it appears in the city column.
Task: Print a dict whose keys are category names, in alphabetical order, and whose values are the total money spent in that category by customers based in that city. A category nobody in that city has bought from must not appear at all — leave it out rather than giving it a zero.
Example: the printed dict looks like {'stationery': 12.5, 'lighting': 40.0}.
Sign in to solve this problem
Reading problems is free for everyone — solving them (Run, Submit, and tracking what you've solved) needs an account.
Sign in