Swapped Food Delivery [Zomato SQL Interview Question]

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.

Schema:

Example Input:

Here's a sample of the initial incorrect data:

Example Output:

The corrected data should look like this:

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!

Step 1: Count Total Number of Orders

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 CTE in the next step.

Step 2: Integrate Total Orders

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

We are performing a Cartesian join (or ) between the table and the CTE. This means that every row in the table is paired with the single row from the subquery which contains the total number of orders, i.e. 15. We need this value to determine if the current is the last one and to apply the correct swapping logic.

This step prepares the data for the next step, where we will apply the swapping logic.

Here's the output for the first 6 rows:

Step 3: Apply Swapped Order Logic with CASE Statement

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 Line 1 in the statement:

• : This checks if the is odd. When the is divided by 2 and the remainder is not 0, the is odd.
• : This checks if the is not the last order. If is not equal to the total number of orders, then it's not the last order.
• : If both conditions above are met, the is incremented by 1 to swap it with the next order.

Next, let's interpret Line 2 and 3:

• : This checks if the is odd and if it's the last order. If both conditions are true, the 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:

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.

Step 4: Sort the Results

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