Problem

Table: Student

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the name of a student and the continent they came from.

A school has students from Asia, Europe, and America.

Write a solution to pivot the continent column in the Student table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia, and Europe, respectively.

The test cases are generated so that the student number from America is not less than either Asia or Europe.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

Input: 
Student table:
+--------+-----------+
| name   | continent |
+--------+-----------+
| Jane   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jack   | America   |
+--------+-----------+
Output: 
+---------+------+--------+
| America | Asia | Europe |
+---------+------+--------+
| Jack    | Xi   | Pascal |
| Jane    | null | null   |
+---------+------+--------+
**Follow up:** If it is unknown which continent has the most students, could
you write a solution to generate the student report?

Solution

Method 1 - SQL Pivot with Row Number

Intuition

We want to pivot the continent column so that each continent is a column and each row contains the student names (sorted alphabetically) for that continent. We can use ROW_NUMBER() to assign a row index per continent, then join the results for each continent on the row index.

Approach

  1. Use ROW_NUMBER() to assign a row number to each student within each continent, ordered by name.
  2. Select the names for each continent and join them on the row number.
  3. Output columns as America, Asia, Europe.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

WITH
  america AS (
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn FROM Student WHERE continent = 'America'
  ),
  asia AS (
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn FROM Student WHERE continent = 'Asia'
  ),
  europe AS (
    SELECT name, ROW_NUMBER() OVER (ORDER BY name) rn FROM Student WHERE continent = 'Europe'
  )
SELECT a.name AS America, b.name AS Asia, c.name AS Europe
FROM america a
LEFT JOIN asia b ON a.rn = b.rn
LEFT JOIN europe c ON a.rn = c.rn
ORDER BY a.name;
1

// Use the SQL query above in your JDBC/ORM code. No special Java logic needed.
1
2
3
4
5
6
7
8
9

# Assuming student is a pandas DataFrame
america = student[student['continent'] == 'America'].sort_values('name').reset_index(drop=True)
asia = student[student['continent'] == 'Asia'].sort_values('name').reset_index(drop=True)
europe = student[student['continent'] == 'Europe'].sort_values('name').reset_index(drop=True)
maxlen = max(len(america), len(asia), len(europe))
america = america['name'].reindex(range(maxlen))
asia = asia['name'].reindex(range(maxlen))
europe = europe['name'].reindex(range(maxlen))
result = pd.DataFrame({'America': america, 'Asia': asia, 'Europe': europe})

Complexity

  • ⏰ Time complexity: O(N log N)
  • 🧺 Space complexity: O(N)