Students Report By Geography

This pivot works because a synthetic per-continent rank (rn) becomes a join key across columns: students that share an rn are forced onto the same output row, and within that single-rn group each column's CASE emits one real name plus NULLs, which MAX silently discards because aggregate functions skip NULLs.

The problem

The Student table is a tall, two-column list: (name, continent), possibly with duplicate rows. A school draws students from Asia, Europe, and America. We must pivot it into three columns — America, Asia, Europe — with each continent's names sorted alphabetically and stacked under its header. Where a continent runs out of students, the cell is NULL. This is LeetCode 618.

Student (input)            Desired output
+----------+-----------+   +---------+------+--------+
| name     | continent |   | America | Asia | Europe |
+----------+-----------+   +---------+------+--------+
| Jane     | America   |   | Jack    | Xi   | Pascal |
| Pascal   | Europe    |   | Jane    | NULL | NULL   |
| Xi       | Asia      |   +---------+------+--------+
| Jack     | America   |
+----------+-----------+

The core difficulty: rows have no natural alignment

Pivoting in SQL means turning row values (continent names) into columns. A plain GROUP BY continent can't do it: that produces one row per continent (three rows), but the spec wants America's names stacked vertically next to Asia's and Europe's. There is no column in the source that says "Jack and Xi and Pascal all belong on output line 1." Jack (America) and Xi (Asia) have nothing in common — different names, different continents.

So we manufacture the missing alignment key. Number each continent's students independently, alphabetically: America gets Jack=1, Jane=2; Asia gets Xi=1; Europe gets Pascal=1. That number, rn, is the load-bearing trick — it says "the 1st American, the 1st Asian, and the 1st European share output row 1." Without rn there is nothing to GROUP BY that lines the columns up.

Step 1 — manufacture the alignment key with ROW_NUMBER

ROW_NUMBER() is a window function: it numbers rows within each partition in a specified order, restarting the count at every partition boundary.

SELECT continent,
       name,
       ROW_NUMBER() OVER (
         PARTITION BY continent   -- restart counting per continent
         ORDER BY name            -- alphabetical within the continent
       ) AS rn
FROM Student;

PARTITION BY continent splits the rows into one bucket per continent; ORDER BY name sorts inside each bucket; ROW_NUMBER() stamps 1, 2, 3, … per bucket. After Step 1:

+-----------+--------+----+
| continent | name   | rn |
+-----------+--------+----+
| America   | Jack   |  1 |   <- 1st American
| America   | Jane   |  2 |   <- 2nd American
| Asia      | Xi     |  1 |   <- 1st Asian
| Europe    | Pascal |  1 |   <- 1st European
+-----------+--------+----+

Now rows that belong on the same output line carry the same rn. The alignment problem is solved; the rest is collapsing.

Step 2 — collapse each rn-group with MAX(CASE …)

Group the Step-1 result by rn and, for each continent, pull the name with a conditional aggregate. This is the part the mechanism sentence is really about, so trace it carefully.

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 (
  SELECT continent, name,
         ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) AS rn
  FROM Student
) t
GROUP BY rn
ORDER BY rn;

Why MAX over the CASE actually works (the deep part)

Take the group rn = 1. After Step 1 it contains exactly three rows — Jack/America, Xi/Asia, Pascal/Europe — and we feed all three into each of the three aggregate columns. Watch the America column. Its CASE WHEN continent = 'America' THEN name END has no ELSE, so any row whose continent isn't America falls through to an implicit ELSE NULL:

So the America column inside group rn=1 sees the multiset {'Jack', NULL, NULL}. Now the two facts that make the trick collapse cleanly:

1. Aggregate functions ignore NULL. The SQL standard says MAX (and MIN, SUM, AVG, COUNT(col)) discard NULL inputs before aggregating. So MAX({'Jack', NULL, NULL}) = MAX({'Jack'}) = 'Jack'.
2. At most one non-NULL can ever exist per group. Within one continent, rn is unique (ROW_NUMBER never repeats a number in a partition). So among the three rows sharing rn=1, exactly one is American — meaning the America column's CASE yields exactly one real name and the rest NULL. MAX of "one value and some NULLs" is just that one value.

That second fact is why the choice of aggregate is irrelevant: MAX, MIN, or even SUM (for numbers) would all return the same answer, because there is never more than one non-NULL to choose between. MAX is not picking the alphabetically-largest name — there is only ever one candidate. The aggregate's real job is purely to collapse three rows into one while the NULL-skipping rule lets the lone real value survive in each column.

For the group rn=2 the multiset for America is {'Jane'} (only Jane has rn=2), and for Asia and Europe it is {NULL} — there is no 2nd Asian or European. MAX({NULL}) is NULL, which is exactly the empty cell the spec wants. After Step 2, ordered by rn:

+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack    | Xi   | Pascal |   <- rn = 1
| Jane    | NULL | NULL   |   <- rn = 2
+---------+------+--------+

Why the naive attempts are wrong

Plain GROUP BY continent. SELECT MAX(name) FROM Student GROUP BY continent gives one row per continent and only the alphabetically-last name — you lose every other student and never get the vertical stacking. There is no rn, so nothing aligns columns.

Self-joins. Joining three filtered copies of Student on a shared row index also works in principle, but you must still synthesize that row index with ROW_NUMBER, and you need a FULL OUTER JOIN to keep continents of unequal length — more code, more NULL bookkeeping, same idea underneath. The MAX(CASE) pivot is the compact form.

Pitfalls

• Adding GROUP BY continent to the outer query. The grouping key must be rn alone. Grouping by continent un-aligns the columns and re-splits the result back into one row per continent.
• Putting a non-aggregated column in the outer SELECT. Once you GROUP BY rn, every output expression must be an aggregate or rn itself. Selecting bare continent or name errors in standard SQL and PostgreSQL, and silently returns arbitrary values under MySQL's ONLY_FULL_GROUP_BY-off mode.
• Forgetting that ROW_NUMBER needs a deterministic ORDER BY. If two students in a continent share a name and you don't add a tiebreaker, the assignment of rn between the duplicates is nondeterministic. For this problem alphabetical order on name is the spec, but in general add a unique tiebreaker.
• Duplicate rows. The table "may contain duplicate rows." ROW_NUMBER treats duplicates as distinct rows, so (Jack, America) appearing twice produces rn=1 and rn=2 both named Jack — two output lines. If the spec wanted distinct students you would SELECT DISTINCT in the inner query first.
• Reaching for the PIVOT keyword. SQL Server and Oracle have a native PIVOT operator, but MySQL and (until recently) PostgreSQL do not. The MAX(CASE) idiom is the portable pivot that runs everywhere, which is why it is the canonical answer.
• Using MIN vs MAX and expecting a difference. Because each rn-group has at most one non-NULL per column, MIN and MAX return identical results. If you ever see them differ, your rn is not unique within the continent — a bug in the partitioning.

Takeaways

• Pivoting tall data is two moves: manufacture an alignment key (a per-group ROW_NUMBER) so unrelated rows can share an output line, then collapse on that key with conditional aggregation.
• MAX(CASE WHEN … THEN col END) works only because each group feeds each column exactly one real value plus NULLs, and aggregates ignore NULL — the aggregate isn't choosing a maximum, it's surviving the NULLs.
• The empty cells (NULL) come for free: a column with no matching row in a group aggregates over an all-NULL set, which yields NULL — exactly the desired blank.
• MAX(CASE) is the portable pivot; reach for native PIVOT only when you know the engine has it.

Problem from LeetCode 618 "Students Report By Geography." NULL-handling of aggregate functions per the SQL standard (ISO/IEC 9075) and the PostgreSQL and MySQL manuals ("Aggregate functions … ignore NULL values"). ROW_NUMBER window-function semantics per the PostgreSQL window-functions documentation. Re-authored and deepened for this guide to explain the load-bearing rn-alignment idea and, in full, why MAX over the CASE collapses each group to a single non-NULL value.