LeetCode 618: Students Report By Geography

Problem Restatement

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

Each row has:

Column	Type	Meaning
name	varchar	Student name
continent	varchar	Continent name

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.

Input and Output

Input table:

Student

Output columns:

Column	Meaning
America	Students from America
Asia	Students from Asia
Europe	Students from Europe

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

Example

Input:

name	continent
Jack	America
Pascal	Europe
Xi	Asia
Jane	America
Hiroshi	Asia
Mary	Europe

Sorted within each continent:

America	Asia	Europe
Jack	Hiroshi	Mary
Jane	Xi	Pascal

Output:

America	Asia	Europe
Jack	Hiroshi	Mary
Jane	Xi	Pascal

First Thought: Grouping Is Not Enough

A simple GROUP BY continent does not work because:

Problem	Reason
We lose row alignment	Need row-wise matching
Unequal group sizes	Requires NULL padding
Ordering requirement	Must sort names first

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:

Continent	Order
America	alphabetical
Asia	alphabetical
Europe	alphabetical

Each student gets a position number:

name	continent	rn
Jack	America	1
Jane	America	2
Hiroshi	Asia	1
Xi	Asia	2

Next we pivot rows into columns using conditional aggregation:

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

This means:

Condition	Output
If continent is America	place name in America column
Otherwise	NULL

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.

Metric	Value	Why
Time	O(n log n)	Sorting within window functions
Space	O(n)	Ranked intermediate table

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:

America	Asia	Europe
Jack	Hiroshi	Mary
Jane	Xi	Pascal

Additional case:

TRUNCATE TABLE Student;

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

Expected output:

America	Asia	Europe
A	B	NULL