DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
The Question
SQL

Correcting Swapped Sequences in Delivery Logs

A delivery platform experienced a technical glitch where every pair of consecutive items in their order logs was swapped (i.e., the item intended for ID 1 was recorded for ID 2, and vice versa). You are given a table orders with columns order_id (unique, sequential integers starting from 1) and item (the name of the food). Write a query to restore the correct mapping. If the total number of orders is odd, the final order ID should remain paired with its original item. The output should be sorted by order_id in ascending order.
PostgreSQL
CTE
Window Function
LEAD
LAG
Questions & Insights

Clarifying Questions

Are `order_id` values strictly sequential? I will assume order_id starts at 1 and increases by 1 for every row. If there are gaps (e.g., 1, 3, 4), the logic might need to shift from using order_id % 2 to using ROW_NUMBER() % 2. Based on the prompt, we will assume sequential integers.
What should the final output look like? The goal is to return the original order_id sequence (1, 2, 3...) but with the corrected item names associated with those IDs.
How do we handle the "Last Odd ID" edge case? If the total count of orders is odd, the very last order_id (which would be odd) remains paired with its original item.
Data Volume? For a company like Zomato, this table could be massive. I will use Window Functions, which are generally more efficient than self-joins or correlated subqueries in PostgreSQL.
Schema Assumptions:
orders table:
order_id: Integer (Primary Key).
item: Varchar/String (The food item name).
Relationship: 1:1 between ID and Item in the raw data, but the item data is logically shifted.

Thinking Process

Identify the Parity: To "swap" rows 1 & 2, 3 & 4, etc., we need to identify if a row is odd or even.
Determine the Swap Logic:
If order_id is Odd (1, 3, 5...): It needs the item from the next row (order_id + 1).
If order_id is Even (2, 4, 6...): It needs the item from the previous row (order_id - 1).
Handle the Boundary:
The special condition: if the last row has an odd ID, it has no "next" row to swap with. It must remain unchanged.
We can use LEAD() to get the next item and LAG() to get the previous item.
To detect the "last row" within the window function logic, we can compare the current order_id to the MAX(order_id).
SQL Pattern choice: I'll use a Common Table Expression (CTE) to calculate the maximum order_id first, then apply a CASE statement with LEAD and LAG. This avoids multiple scans of the table.
Implementation Breakdown

Problem Set

Requirement: Swap items between adjacent rows (1↔2, 3↔4).
Constraint: If the last row is odd, do not swap it.
Output:order_id, item (corrected).
Edge Case: A table with only 1 row (should return that row as is).
Edge Case: An empty table (should return empty).

Approach

Window Functions:
LEAD(item): Fetches the item from the next row in the sequence.
LAG(item): Fetches the item from the previous row in the sequence.
MAX(order_id) OVER(): A windowed aggregate to identify the last ID in the set without a second scan.
Conditional Logic:CASE statement to decide whether to look forward, look backward, or stay put.
Computational Cost:O(N \log N) due to the required sort for the window functions.

Implementation

Wrap Up

Advanced Topics

Handling Non-Sequential IDs: If the order_id values were not 1, 2, 3 but rather 101, 105, 110, we should replace order_id % 2 with ROW_NUMBER() OVER (ORDER BY order_id) % 2. This ensures the swapping logic remains robust regardless of the actual ID values.
Performance on Massive Datasets: In a distributed PostgreSQL environment (like Citus) or a large-scale RDS instance:
Indexing: Ensure there is a primary key or B-Tree index on order_id to make the ORDER BY and Window Function boundaries efficient.
Parallelism: PostgreSQL's query optimizer can parallelize window functions if the dataset is large enough, provided max_parallel_workers_per_gather is configured correctly.
Alternative - Lead/Lag with default: We could technically use LEAD(item, 1, item) for the odd-row logic, but the specific constraint about the "last odd row" makes the CASE statement more readable and explicit for maintainability.