Back to questions

Zomato is a leading online food delivery service that connects users with various restaurants and cuisines, allowing them to browse menus, place orders, and get meals delivered to their doorsteps.

Recently, Zomato encountered an issue with their delivery system. Due to an error in the delivery driver instructions, each item's order was swapped with the item in the subsequent row. As a data analyst, you're asked to correct this swapping error and return the proper pairing of order ID and item.

If the last item has an odd order ID, it should remain as the last item in the corrected data. For example, if the last item is Order ID 7 Tandoori Chicken, then it should remain as Order ID 7 in the corrected data.

In the results, return the correct pairs of order IDs and items.

column_name | type | description |
---|---|---|

order_id | integer | The ID of each Zomato order. |

item | string | The name of the food item in each order. |

Here's a sample of the initial incorrect data:

order_id | item |
---|---|

1 | Chow Mein |

2 | Pizza |

3 | Pad Thai |

4 | Butter Chicken |

5 | Eggrolls |

6 | Burger |

7 | Tandoori Chicken |

The corrected data should look like this:

corrected_order_id | item |
---|---|

1 | Pizza |

2 | Chow Mein |

3 | Butter Chicken |

4 | Pad Thai |

5 | Burger |

6 | Eggrolls |

7 | Tandoori Chicken |

Order ID 1 is now associated with Pizza and Order ID 2 is paired with Chow Mein. This adjustment ensures that each order is correctly aligned with its respective item, addressing the initial swapping error.

Order ID 7 remains unchanged and is still associated with Tandoori Chicken. This preserves the order sequence ensuring that the last odd order ID remains unaltered.

The dataset you are querying against may have different input & output - **this is just an example**!

First, we count the total number of orders in the table using the function. Knowing the total number of orders is important to determine whether there are an odd or even number of orders which affects the swapping logic.

This result will be used as a subquery in the next step.

total_orders |
---|

15 |

In this step, we simply incorporate the query from Step 1 as a subquery named 'order_counts' in the main query to access the total number of orders.

Here's the output for the first 6 rows:

order_id | item | total_orders |
---|---|---|

1 | Chow Mein | 15 |

2 | Pizza | 15 |

3 | Pad Thai | 15 |

4 | Butter Chicken | 15 |

5 | Eggrolls | 15 |

6 | Burger | 15 |

In this step, we use a CASE statement to correct the based on whether it's odd or even, ensuring that each order is paired with the correct item. Explore our tutorial to learn how to use a CASE statement.

Let's break down the logic behind the first line:

- : This condition checks if the is odd. The modulo operator calculates the remainder when is divided by 2. If the remainder is not equal to 0, it means the is odd.
- : This condition ensures that the is not the last order in the table. It compares the with the total number of orders (). If the is not equal to the total number of orders, it's not the last order.
- : If the meets the conditions of being odd and not the last order, then it is incremented by 1. This operation swaps the current order with the next order in the sequence.

Here's an explanation of the logic for the second and third lines:

- : If the odd is the last order, it remains unchanged to maintain the correct order.
- : For even values, they are deducted by 1 to swap with the previous item.

Let's use the example input to illustrate the logic:

order_id | item |
---|---|

1 | Chow Mein |

2 | Pizza |

3 | Pad Thai |

4 | Butter Chicken |

5 | Eggrolls |

6 | Burger |

7 | Tandoori Chicken |

For 1:

- is true because 1 is odd.
- is true because 1 is not the last order.
- Therefore, = 2. So, Chow Mein is swapped with Pizza.

For 2:

- is false because 2 is even.
- Therefore, = 1. Thus, Pizza remains in place.

For 7:

- is true because 7 is odd.
- is false because 7 is the last order.
- Therefore, remains unchanged. So, Tandoori Chicken remains in place.

Finally, sort the corrected table based on the corrected order IDs.

PostgreSQL 14