# 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. ([leetcode.com](https://leetcode.com/problems/students-report-by-geography/?utm_source=chatgpt.com))

## Input and Output

Input table:

```sql
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:

```sql
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

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
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:

```sql
TRUNCATE TABLE Student;

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

Expected output:

| America | Asia | Europe |
|---|---|---|
| A | B | NULL |