LeetCode 618: Students Report By Geography

Problem Restatement

We are given a table Student containing names and their geography category.

Each row has:

We need to transform the table so that each continent becomes a column.

Each column should list students from that continent in alphabetical order.

If one continent has fewer students than another, missing values should be filled with NULL.

The result should align students row by row based on alphabetical ordering within each continent.

The official problem asks to pivot student names by continent so that each continent becomes a column with lexicographically ordered names. (leetcode.com)

Input and Output

Input table:

Student

Output columns:

Each row represents the ith student in alphabetical order within each continent.

Example

Input:

Sorted within each continent:

Output:

First Thought: Grouping Is Not Enough

A simple GROUP BY continent does not work because:

So we need a way to assign row numbers inside each continent.

Key Insight

We assign a rank to each student within their continent using:

ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name)

This creates aligned rows across continents.

Then we pivot using conditional aggregation.

Algorithm

Step 1: Assign row numbers per continent sorted by name.

Step 2: Use row number as a grouping key.

Step 3: Pivot continents into columns using CASE WHEN.

Step 4: Group by row number.

SQL Solution

WITH ranked AS (
    SELECT
        name,
        continent,
        ROW_NUMBER() OVER (
            PARTITION BY continent
            ORDER BY name
        ) AS rn
    FROM Student
)
SELECT
    MAX(CASE WHEN continent = 'America' THEN name END) AS America,
    MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
    MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
FROM ranked
GROUP BY rn
ORDER BY rn;

Code Explanation

First we rank students inside each continent:

ROW_NUMBER() OVER (
    PARTITION BY continent
    ORDER BY name
) AS rn

This ensures:

Each student gets a position number:

Next we pivot rows into columns using conditional aggregation:

MAX(CASE WHEN continent = 'America' THEN name END)

This means:

We use MAX because aggregation is required for GROUP BY rn, and each (rn, continent) pair has at most one value.

Finally:

GROUP BY rn
ORDER BY rn;

This aligns all continents row-by-row using the rank index.

Correctness

Each continent is independently sorted using ROW_NUMBER, so names appear in alphabetical order within their respective columns.

The rn value ensures alignment across continents: the first row corresponds to the first student in each continent, the second row corresponds to the second student, and so on.

Because GROUP BY rn combines rows with the same rank across all continents, each output row correctly represents the ith student in each sorted list.

The CASE WHEN ensures that each name is placed into its correct continent column, and MAX safely aggregates the single value per group.

If a continent has fewer students, then for larger rn values there is no matching row, resulting in NULL, which satisfies the requirement.

Thus, the output correctly represents a pivoted, row-aligned view of students by continent.

Complexity

Let n be the number of students.

Window functions require ordering within partitions, which dominates runtime.

Alternative: Pre-Aggregation with Separate Tables

We can also build separate ranked lists per continent:

WITH america AS (
    SELECT name,
           ROW_NUMBER() OVER (ORDER BY name) AS rn
    FROM Student
    WHERE continent = 'America'
),
asia AS (
    SELECT name,
           ROW_NUMBER() OVER (ORDER BY name) AS rn
    FROM Student
    WHERE continent = 'Asia'
),
europe AS (
    SELECT name,
           ROW_NUMBER() OVER (ORDER BY name) AS rn
    FROM Student
    WHERE continent = 'Europe'
)
SELECT
    a.name AS America,
    s.name AS Asia,
    e.name AS Europe
FROM america a
LEFT JOIN asia s ON a.rn = s.rn
LEFT JOIN europe e ON a.rn = e.rn;

This version is more explicit but longer.

Testing

Sample data:

CREATE TABLE Student (
    name VARCHAR(50),
    continent VARCHAR(50)
);

INSERT INTO Student VALUES
('Jack', 'America'),
('Jane', 'America'),
('Xi', 'Asia'),
('Hiroshi', 'Asia'),
('Mary', 'Europe'),
('Pascal', 'Europe');

Expected output:

Additional case:

TRUNCATE TABLE Student;

INSERT INTO Student VALUES
('A', 'America'),
('B', 'Asia');

Expected output: