Students Report By Geography - Practice Coding Problems

Students Report By Geography - Problem

Database Hard

Students Report By Geography is a classic SQL pivot table problem that transforms rows into columns for better data visualization.

You're given a Student table with student names and their continents of origin. Your task is to create a pivoted report that displays students grouped by continent in three columns: America, Asia, and Europe.

Key Requirements:
• Students within each continent must be sorted alphabetically
• Each row shows one student from each continent (if available)
• The number of students from America is guaranteed to be ≥ students from other continents
• Use ROW_NUMBER() window function to assign row positions

Table Schema:
Student table:
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| name | varchar |
| continent | varchar |
+-------------+---------+

Input & Output

example_1.sql — Basic Pivot

$ Input: Student table:\n| name | continent |\n|--------|-----------|\n| Jane | America |\n| Wang | Asia |\n| Fang | Asia |\n| Jack | America |\n| Pascal | Europe |

› Output: | America | Asia | Europe |\n|---------|------|--------|\n| Jack | Fang | Pascal |\n| Jane | Wang | null |

💡 Note: Students are sorted alphabetically within each continent. Jack (1st American) pairs with Fang (1st Asian) and Pascal (1st European). Jane (2nd American) pairs with Wang (2nd Asian), but no 2nd European exists.

example_2.sql — Uneven Distribution

$ Input: Student table:\n| name | continent |\n|--------|-----------|\n| Alice | America |\n| Bob | America |\n| Charlie| America |\n| Li | Asia |

› Output: | America | Asia | Europe |\n|---------|------|--------|\n| Alice | Li | null |\n| Bob | null | null |\n| Charlie | null | null |

💡 Note: America has 3 students, Asia has 1, Europe has 0. After the first row, remaining American students have no counterparts from other continents.

example_3.sql — All Equal Distribution

$ Input: Student table:\n| name | continent |\n|--------|-----------|\n| John | America |\n| Kate | America |\n| Chen | Asia |\n| Zhang | Asia |\n| Anna | Europe |\n| Boris | Europe |

› Output: | America | Asia | Europe |\n|---------|-------|--------| \n| John | Chen | Anna |\n| Kate | Zhang | Boris |

💡 Note: Perfect distribution with 2 students from each continent. All positions are filled with alphabetically ordered names.

Visualization

Tap to expand

Understanding the Visualization

Assign Row Numbers

Each student gets a position number within their continent (1st, 2nd, 3rd...)

Create Pivot Logic

Use CASE statements to conditionally select names for each continent column

Group by Position

Combine students with the same row number across continents

Fill Missing Slots

Use NULL for positions where a continent has no student

Key Takeaway

🎯 Key Insight: The magic happens when ROW_NUMBER() creates position indexes within each continent, allowing conditional aggregation to pivot the data cleanly. GROUP BY rn ensures students from the same position across continents appear in the same output row.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Dominated by sorting within each continent partition using ROW_NUMBER()

⚡ Linearithmic

Space Complexity

O(n)

CTE stores intermediate results with row numbers for all students

⚡ Linearithmic Space

Constraints

1 ≤ number of students ≤ 1000
Student names contain only letters and are unique
Continent values are exactly 'America', 'Asia', or 'Europe'
America always has ≥ students compared to other continents
Names are case-sensitive for sorting

Asked in

a Amazon 45 ⊞ Microsoft 38 ◯ Oracle 52 IBM IBM 28

The optimal solution uses a CTE with ROW_NUMBER() window function partitioned by continent to assign positions, then employs conditional aggregation with MAX(CASE WHEN...) to pivot the data efficiently. This approach requires only a single pass through the data and avoids complex joins, making it both readable and performant.

Common Approaches

Approach	Time	Space	Notes
✓ Multiple Subqueries (Brute Force)	O(n log n)	O(n)	Use separate subqueries for each continent and join them manually
Single CTE with Conditional Aggregation (Optimal)	O(n log n)	O(n)	Use one CTE with ROW_NUMBER() and conditional aggregation to pivot in a single pass

Multiple Subqueries (Brute Force) — Algorithm Steps

Create subquery for America students with ROW_NUMBER()
Create subquery for Asia students with ROW_NUMBER()
Create subquery for Europe students with ROW_NUMBER()
Join all three subqueries on row numbers using FULL OUTER JOIN

Visualization

Tap to expand

Step-by-Step Walkthrough

Create America Subquery

ROW_NUMBER() orders American students alphabetically

Create Asia Subquery

ROW_NUMBER() orders Asian students alphabetically

Create Europe Subquery

ROW_NUMBER() orders European students alphabetically

Join All Three

FULL OUTER JOIN combines all subqueries on row numbers

Code -

solution.c — C

// SQL Solution using Multiple Subqueries
#include <stdio.h>
#include <string.h>

char* solution() {
    static char query[1000];
    strcpy(query, "SELECT "
                  "a.name AS America, "
                  "s.name AS Asia, "
                  "e.name AS Europe "
                  "FROM ("
                  "SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn "
                  "FROM Student WHERE continent = 'America'"
                  ") a "
                  "FULL OUTER JOIN ("
                  "SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn "
                  "FROM Student WHERE continent = 'Asia'"
                  ") s ON a.rn = s.rn "
                  "FULL OUTER JOIN ("
                  "SELECT name, ROW_NUMBER() OVER (ORDER BY name) AS rn "
                  "FROM Student WHERE continent = 'Europe'"
                  ") e ON a.rn = e.rn "
                  "ORDER BY a.rn, s.rn, e.rn");
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting students within each continent dominates the time complexity

⚡ Linearithmic

Space Complexity

O(n)

Temporary storage for each continent's sorted list

⚡ Linearithmic Space

Constraints

1 ≤ number of students ≤ 1000
Student names contain only letters and are unique
Continent values are exactly 'America', 'Asia', or 'Europe'
America always has ≥ students compared to other continents
Names are case-sensitive for sorting

48.2K Views

Medium-High Frequency

~25 min Avg. Time

1.5K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Multiple Subqueries (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler