Cancellation split by restaurant
mediumCancellation split by restaurant
Swiggy SQL Interview Question
A restaurant that accepts an order and then cancels it costs Swiggy more than the order, because the app has already given the customer a delivery time. Partner operations calls the worst offenders and drops them down the listings until they improve, so they need to know which restaurants are canceling and how much of their volume it comes to.
Return the restaurant_name, the number of delivered orders as delivered_orders, the number of canceled ones as canceled_orders, and the canceled share of that restaurant's orders as canceled_share, as a percentage rounded to 2 decimal places. A restaurant that has never canceled an order shows 0.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
swiggy_ordersTable32 rows
| Column Name | Type |
|---|---|
| order_id | BIGINT |
| city | VARCHAR |
| restaurant_id | BIGINT |
| placed_at | TIMESTAMP |
| delivery_minutes | BIGINT |
| order_total | DOUBLE |
| status | VARCHAR |
swiggy_restaurantsTable9 rows
| Column Name | Type |
|---|---|
| restaurant_id | BIGINT |
| restaurant_name | VARCHAR |
| city | VARCHAR |
| cuisine | VARCHAR |
| onboarded_on | DATE |
swiggy_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 |
swiggy_restaurantsExample Input
| restaurant_id | restaurant_name | city | cuisine | onboarded_on |
|---|---|---|---|---|
| 1 | Empire Grill | Bengaluru | North Indian | 2022-03-14 |
| 2 | Truffles Corner | Bengaluru | Continental | 2021-11-02 |
| 3 | Meghana Biryani House | Bengaluru | Biryani | 2023-01-20 |
| 4 | Paradise Spice | Hyderabad | Biryani | 2020-08-11 |
| 5 | Chutneys Express | Hyderabad | South Indian | 2022-06-30 |
| 6 | Shah Ghouse Cafe | Hyderabad | North Indian | 2021-04-05 |
Example Output
| restaurant_name | delivered_orders | canceled_orders | canceled_share |
|---|---|---|---|
| Chutneys Express | 1 | 1 | 50 |
| Empire Grill | 3 | 0 | 0 |
| Meghana Biryani House | 1 | 1 | 50 |
| Paradise Spice | 2 | 0 | 0 |
| Shah Ghouse Cafe | 2 | 0 | 0 |
| Truffles Corner | 2 | 0 | 0 |
Explanation
Meghana Biryani House took two orders in the example, delivered one and canceled one, so half of what it accepted fell through: 50. Empire Grill delivered three and canceled none, and still appears, on a canceled share of 0 rather than dropping out of the report.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Swiggy
Difficulty
medium
Topic
reshaping
Language
SQL
Your query
Starting the SQL engine...