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, and Europe
  • 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

  • name and continent are varchar fields
  • The table may contain duplicate rows
  • Students come from exactly three continents: America, Asia, and Europe
  • The number of students from America is not less than either Asia or Europe

Visualization

Tap to expand
Students Report By Geography INPUT: Student Table name continent Jane America Pascal Europe Xi Asia Jack America Tom Europe Amy Asia Grouped by Continent: America Jane, Jack Asia Xi, Amy Europe Pascal, Tom ALGORITHM STEPS 1 Assign Row Numbers ROW_NUMBER() per continent ORDER BY name ASC 2 Filter by Continent Create 3 subqueries: WHERE continent='America' 3 JOIN on Row Number FULL OUTER JOIN all 3 ON rn = rn 4 SELECT Pivoted Columns America, Asia, Europe Row Numbers: America: Jack(1), Jane(2) Asia: Amy(1), Xi(2) Europe: Pascal(1), Tom(2) FINAL RESULT America Asia Europe Jack Amy Pascal Jane Xi Tom OK - Pivoted Successfully! Pivot Transformation: Rows (vertical) Columns (horizontal) Names sorted A-Z within each continent column Key Insight: ROW_NUMBER() with PARTITION BY continent creates a unique row ID for each student within their continent. By joining on these row numbers, we align students across columns - first alphabetical names align in row 1, second names in row 2, etc. This transforms vertical data (rows) into horizontal format (columns). TutorialsPoint - Students Report By Geography | Optimal Solution

Related Problems

Asked in
Amazon 25 Microsoft 18 Google 15
35.0K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen