627. Swap Salary
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 78.0%
Problem Breakdown
The problem “Swap Salary” requires us to swap the values in the salary column for all rows in a given table. The input is a table with at least two columns: id and salary. The output should be the same table, but with the salary values swapped between all pairs of rows.
Inputs:
- A table named
Employeewith at least two columns:id: An integer representing the employee ID.salary: An integer representing the salary of the employee.
Outputs:
- The same table with the
salaryvalues swapped between all pairs of rows.
Implicit Constraints or Tricky Edge Cases:
- If the number of rows is odd, the last row’s salary should remain unchanged.
- The table might be empty.
- All
ids are unique.
Intuition
The brute-force approach would involve iterating through each pair of rows and swapping their salary values. However, this approach would have a time complexity of O(n^2), which is inefficient for large tables.
A better approach involves using SQL’s built-in functions to swap the values in a more efficient manner. The key insight is that we can use a self-join on the table to pair up rows and swap their salary values in a single query.
Approach
- Self-Join the Table: Perform a self-join on the
Employeetable to create pairs of rows. - Swap Salary Values: Use a conditional update to swap the
salaryvalues between the paired rows. - Handle Odd Number of Rows: If there is an odd number of rows, ensure that the last row’s salary remains unchanged.
The algorithm involves:
- Selecting pairs of rows using a self-join.
- Updating the
salaryvalues for each pair. - Ensuring that if there is an odd number of rows, the last row’s salary is not affected.
Complexity Analysis
Time Complexity: O(n), where n is the number of rows in the table. The self-join and update operations are linear with respect to the number of rows.
Space Complexity: O(1), as we are updating the table in place without using any additional data structures.
Solution
-- SQL query to swap salary values between all pairs of rows
UPDATE Employee e1
JOIN Employee e2 ON e1.id < e2.id
SET e1.salary = e2.salary,
e2.salary = e1.salary;
Edge Cases Handled
- Empty Table: The query will execute without error, but no changes will be made since there are no rows to swap.
- Odd Number of Rows: The last row’s salary remains unchanged because it does not have a pair to swap with.
The solution provided is correct and efficient. It handles the edge cases as described in the problem breakdown. The self-join ensures that each pair of rows is swapped, and the condition e1.id < e2.id prevents duplicate swaps and ensures that no row is paired with itself.