626. Exchange Seats

Problem¶

Given Seat(id, student) with continuous IDs starting at 1, swap students in each adjacent pair of seats: (1,2), (3,4), etc. If there is an odd last seat, keep it unchanged.

Input

# Seat
---+----------+---------------
id | student  | result_student
---+----------+---------------
1  | 'Abbot'  | 'Doris'
2  | 'Doris'  | 'Abbot'
3  | 'Emerson'| 'Green'
4  | 'Green'  | 'Emerson'
5  | 'Jeames' | 'Jeames'

Output:

# Seat
---+----------
id | student
---+----------
1  | 'Doris'
2  | 'Abbot'
3  | 'Green'
4  | 'Emerson'
5  | 'Jeames'

Key trick¶

Use neighbor access.

• Odd id: take the next student's name.
• Even id: take the previous student's name.
• Last odd id: keep the same name.

Trap¶

• Forgetting the last odd row.
• Not ordering by id before using LEAD, LAG, or shift.
• Returning the swapped rows without final ordering by id.

Why is it interesting?¶

It is a clean test of parity logic plus neighbor-row operations.

SQL solution¶

-- SQLite
SELECT
    id,
    CASE
        -- Odd seats take the next student, except the last odd seat.
        WHEN id % 2 = 1 AND id < MAX(id) OVER () THEN LEAD(student) OVER (ORDER BY id)

        -- Even seats take the previous student.
        WHEN id % 2 = 0 THEN LAG(student) OVER (ORDER BY id)

        -- Last odd seat stays unchanged.
        ELSE student
    END AS student
FROM Seat
ORDER BY id;

Pandas solution¶

import pandas as pd


def exchange_seats(seat: pd.DataFrame) -> pd.DataFrame:
    # Sort first because swapping is based on seat order.
    df = seat.sort_values("id").reset_index(drop=True).copy()

    # Odd positions in 1-based IDs are even positions in 0-based indexing.
    swapped = df["student"].copy()

    # Seat 1 takes seat 2, seat 3 takes seat 4, etc.
    swapped.iloc[0::2] = df["student"].shift(-1).iloc[0::2]

    # Seat 2 takes seat 1, seat 4 takes seat 3, etc.
    swapped.iloc[1::2] = df["student"].shift(1).iloc[1::2]

    # If the last row is unpaired, keep it unchanged.
    if len(df) % 2 == 1:
        swapped.iloc[-1] = df["student"].iloc[-1]

    df["student"] = swapped
    return df[["id", "student"]]

Pytest test¶

import sqlite3

import pandas as pd
import pytest
from pandas.testing import assert_frame_equal


SQL_QUERY = """
SELECT
    id,
    CASE
        WHEN id % 2 = 1 AND id < MAX(id) OVER () THEN LEAD(student) OVER (ORDER BY id)
        WHEN id % 2 = 0 THEN LAG(student) OVER (ORDER BY id)
        ELSE student
    END AS student
FROM Seat
ORDER BY id
"""


def exchange_seats(seat: pd.DataFrame) -> pd.DataFrame:
    df = seat.sort_values("id").reset_index(drop=True).copy()

    swapped = df["student"].copy()
    swapped.iloc[0::2] = df["student"].shift(-1).iloc[0::2]
    swapped.iloc[1::2] = df["student"].shift(1).iloc[1::2]

    if len(df) % 2 == 1:
        swapped.iloc[-1] = df["student"].iloc[-1]

    df["student"] = swapped
    return df[["id", "student"]]


@pytest.mark.parametrize(
    "rows, expected",
    [
        (
            [],
            [],
        ),
        (
            [(1, "A")],
            [(1, "A")],
        ),
        (
            [(1, "A"), (2, "B")],
            [(1, "B"), (2, "A")],
        ),
        (
            [(1, "A"), (2, "B"), (3, "C")],
            [(1, "B"), (2, "A"), (3, "C")],
        ),
        (
            [(1, "Abbot"), (2, "Doris"), (3, "Emerson"), (4, "Green"), (5, "Jeames")],
            [(1, "Doris"), (2, "Abbot"), (3, "Green"), (4, "Emerson"), (5, "Jeames")],
        ),
        (
            [(3, "C"), (1, "A"), (4, "D"), (2, "B")],
            [(1, "B"), (2, "A"), (3, "D"), (4, "C")],
        ),
    ],
)
def test_exchange_seats_sql_and_pandas(rows, expected):
    con = sqlite3.connect(":memory:")
    con.execute("CREATE TABLE Seat (id INTEGER, student TEXT)")
    con.executemany("INSERT INTO Seat (id, student) VALUES (?, ?)", rows)

    sql_result = pd.read_sql_query(SQL_QUERY, con)

    seat = pd.DataFrame(rows, columns=["id", "student"])
    if seat.empty:
        seat = seat.astype({"id": "int64", "student": "object"})

    pandas_result = exchange_seats(seat)

    expected_df = pd.DataFrame(expected, columns=["id", "student"])

    assert_frame_equal(sql_result, expected_df, check_dtype=False)
    assert_frame_equal(pandas_result, expected_df, check_dtype=False)

Comment on my solution¶

• Your SQL solution is simple and interview-friendly.
• I would replace COALESCE(LEAD(...), student) with an explicit last-row check using MAX(id) OVER (); it avoids confusing a missing next row with a possible NULL student.
• Your Pandas solution is correct and clear.
• The shift approach is the right level of simplicity for this problem.

SELECT
    id,
    CASE
        WHEN id % 2 = 1 THEN COALESCE(LEAD(student) OVER (ORDER BY id), student)
        ELSE LAG(student) OVER (ORDER BY id)
    END AS student
FROM Seat
ORDER BY id;

import pandas as pd

def exchange_seats(seat: pd.DataFrame) -> pd.DataFrame:
    df = seat.sort_values("id").reset_index(drop=True).copy()
    df.loc[df.index[0::2], "swapped_student"] = df["student"].shift(-1).fillna(df["student"])
    df.loc[df.index[1::2],"swapped_student"] = df["student"].shift(1)
    result = (
        df[["id", "swapped_student"]].rename(columns={"swapped_student":"student"})
    )
    return result

Extra¶

Window functions¶

Use of OVER () in the above SQL query¶

In SQLite (SQL), how does OVER keyword works? I'm missing something. In the following example, as I understand:

1) LEAD(student) OVER (ORDER BY id) means "return student on next row with rows odered by id" 2) But I don't understand why we need OVER () in MAX(id) OVER (). It seems clear that MAX(id) has to be applied on id columns.

What am I missing?

OVER tells SQLite to use a function as a window function: compute a value while still keeping one output row per input row.

In your query:

MAX(id) OVER ()

means:

• compute MAX(id) over the entire result set
• return that same maximum value on every row
• do not collapse the result into a single row

So if Seat is:

id | student
1  | A
2  | B
3  | C

then:

SELECT id, MAX(id) OVER () AS max_id
FROM Seat;

returns:

id | max_id
1  | 3
2  | 3
3  | 3

But plain:

SELECT MAX(id)
FROM Seat;

returns only:

MAX(id)
3

The key point:

• MAX(id) as a normal aggregate reduces rows into one result
• MAX(id) OVER () computes the aggregate while preserving each row

So this condition:

id < MAX(id) OVER ()

means:

• for each row, compare its id to the maximum id in the table
• this detects whether the current row is not the last seat

OVER () is empty because no partitioning or ordering is needed. It means "use all rows as one window."

Window functions definition¶

• Define window functions?
• Why and when do we generaly needs window functions?
• Give me 3 simple and standard window function examples?

Window functions¶

A window function computes a value across a set of related rows called a window, while still returning one row per original row.

Syntax pattern:

function(...) OVER (
    PARTITION BY ...
    ORDER BY ...
)

• PARTITION BY splits rows into groups.
• ORDER BY defines row order inside each group.
• OVER makes the function a window function.

Why use them?¶

Use window functions when you need calculations involving other rows, but you do not want to collapse rows like GROUP BY does.

Common uses:

• Ranking rows
• Comparing current row with previous or next row
• Running totals
• Per-group totals while keeping row details
• Percentages within groups

3 simple examples¶

1. Row number¶

SELECT
    student,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS rank_position
FROM Scores;

Gives each row a number based on score order.

2. Previous row value¶

SELECT
    id,
    student,
    LAG(student) OVER (ORDER BY id) AS previous_student
FROM Seat;

Shows the previous student according to id.

3. Total per group without collapsing rows¶

SELECT
    department,
    employee,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS department_total_salary
FROM Employees;

Shows each employee and the total salary of their department.

Complement on df.index[1::2] and df.iloc[1::2]¶

Why this statement in my pandas solution works?

df.loc[df.index[1::2],"swapped_student"] = df["student"].shift(1)

And how does it compare to your statement?

swapped.iloc[1::2] = df["student"].shift(1).iloc[1::2]

What am I missing? Be concise.

Why your statement works¶

df.loc[df.index[1::2], "swapped_student"] = df["student"].shift(1)

It works because Pandas aligns by index labels during assignment.

• df.index[1::2] selects rows at positions 1, 3, 5, ....
• df["student"].shift(1) creates a full Series:

index | student.shift(1)
------+-----------------
0     | NaN
1     | row_0_student
2     | row_1_student
3     | row_2_student

• When assigning that Series to only rows 1, 3, 5, ..., Pandas matches labels:
  • Row 1 receives shifted value at index 1.
  • Row 3 receives shifted value at index 3.

So only the selected rows are filled, and values line up correctly.

Compared to my statement¶

swapped.iloc[1::2] = df["student"].shift(1).iloc[1::2]

This works by position, not label alignment.

• Left side selects positions 1, 3, 5, ....
• Right side also explicitly keeps positions 1, 3, 5, ....
• Assignment is direct and visually symmetric.

What you are missing¶

Nothing major.

Your version is valid because the DataFrame has a clean index after:

df = seat.sort_values("id").reset_index(drop=True).copy()

The subtle difference:

• Your code relies on index-label alignment.
• My code relies on positional alignment.

If you did not reset the index, your version would still likely work because labels are preserved consistently, but it is less obvious to readers. My version makes the intended same-position slice explicit.