Delivery time by city
easyDelivery time by city
Swiggy Pandas Interview Question
Swiggy sets the delivery estimate it shows at checkout city by city: too tight and the order lands late and gets refunded, too loose and the customer orders somewhere else. An order that was canceled never left the restaurant and has no delivery time, so it tells the estimate nothing.
Using the orders DataFrame, find each city, the number of orders that were actually delivered as delivered_orders, and their average delivery time in minutes as avg_minutes, rounded to 1 decimal place. Sort by avg_minutes, slowest first. Assign the answer to result.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Scientist
ordersDataFrame32 rows
| Column Name | Type |
|---|---|
| order_id | int64 |
| city | str |
| restaurant_id | int64 |
| placed_at | str |
| delivery_minutes | float64 |
| order_total | float64 |
| status | str |
ordersExample Input
| order_id | city | restaurant_id | placed_at | delivery_minutes | order_total | status |
|---|---|---|---|---|---|---|
| 4001 | Bengaluru | 1 | 2024-07-01 12:10:00 | 32 | 540 | delivered |
| 4002 | Bengaluru | 2 | 2024-07-01 13:05:00 | 41 | 320 | delivered |
| 4003 | Bengaluru | 1 | 2024-07-01 20:15:00 | 28 | 460 | delivered |
| 4004 | Bengaluru | 3 | 2024-07-02 12:40:00 | NULL | 275 | canceled |
| 4005 | Bengaluru | 2 | 2024-07-02 19:20:00 | 36 | 680 | delivered |
| 4006 | Bengaluru | 1 | 2024-07-03 13:15:00 | 25 | 390 | delivered |
| 4007 | Bengaluru | 3 | 2024-07-03 20:05:00 | 52 | 510 | delivered |
| 4011 | Hyderabad | 4 | 2024-07-01 12:30:00 | 38 | 410 | delivered |
| 4012 | Hyderabad | 5 | 2024-07-01 19:50:00 | 44 | 295 | delivered |
| 4013 | Hyderabad | 4 | 2024-07-02 13:20:00 | 31 | 530 | delivered |
| 4014 | Hyderabad | 6 | 2024-07-02 20:40:00 | 58 | 700 | delivered |
| 4015 | Hyderabad | 5 | 2024-07-03 12:55:00 | NULL | 260 | canceled |
| 4016 | Hyderabad | 6 | 2024-07-03 19:35:00 | 49 | 480 | delivered |
Example Output
| city | delivered_orders | avg_minutes |
|---|---|---|
| Hyderabad | 5 | 44 |
| Bengaluru | 6 | 35.7 |
Explanation
Hyderabad delivered five orders in the example at 44 minutes on average, slower than Bengaluru's six at 35.7. The canceled order in each city has no delivery time and is kept out of both the count and the average.
The example above is a small slice of the data. Your code runs against the full DataFrames.
Company
Swiggy
Difficulty
easy
Topic
null handling
Language
Pandas
Your code
Loading Python in the background. You can start writing.