Students Report By Geography - Problem
You have a Student table containing student names and their continents. Write a SQL solution to pivot the continent column so that each name is sorted alphabetically and displayed underneath its corresponding continent.
Requirements:
- Output headers should be
America,Asia, andEurope - Names should be sorted alphabetically within each continent
- The test cases guarantee that America has at least as many students as Asia or Europe
Table Schema
Student
| Column Name | Type | Description |
|---|---|---|
name
|
varchar | Student name |
continent
|
varchar | Continent (America, Asia, or Europe) |
Note: Table may contain duplicate rows. Each row indicates a student name and their continent.
Input & Output
Example 1 — Basic Pivot
Input Table:
| name | continent |
|---|---|
| Jane | America |
| Pascal | Europe |
| Xi | Asia |
| Jack | America |
Output:
| America | Asia | Europe |
|---|---|---|
| Jack | Xi | Pascal |
| Jane |
💡 Note:
Students are grouped by continent and sorted alphabetically within each group. Since America has 2 students (Jack, Jane) and Asia/Europe each have 1, the second row shows null for Asia and Europe.
Example 2 — Equal Distribution
Input Table:
| name | continent |
|---|---|
| Alice | America |
| Bob | Asia |
| Charlie | Europe |
Output:
| America | Asia | Europe |
|---|---|---|
| Alice | Bob | Charlie |
💡 Note:
When each continent has exactly one student, all students appear in the first row, sorted alphabetically within their respective continents.
Example 3 — Duplicate Names
Input Table:
| name | continent |
|---|---|
| John | America |
| John | America |
| Mary | Asia |
Output:
| America | Asia | Europe |
|---|---|---|
| John | Mary | |
| John |
💡 Note:
Duplicate names are handled by the ROW_NUMBER() function, which assigns different ranks to duplicate entries, allowing both 'John' entries to appear in separate rows.
Constraints
-
nameandcontinentare varchar fields - The table may contain duplicate rows
-
Students come from exactly three continents:
America,Asia, andEurope - The number of students from America is not less than either Asia or Europe
Visualization
Tap to expand
Understanding the Visualization
1
Input
Student table with name and continent
2
Rank & Pivot
ROW_NUMBER() within continent, then pivot
3
Output
Columns for America, Asia, Europe
Key Takeaway
🎯 Key Insight: Use window functions to create row positions, then pivot with conditional aggregation for complex data transformations
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code