618 Students Report by Geography

Description

Table: Student

• 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.

Problem Statement

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.

Example 1:

Input:

• Student table:

Output:

Follow up: If it is unknown which continent has the most students, could you write a solution to generate the student report?

Solution

In this case, there are two main problems.

1. We need to pivot the continent column.
2. We need to sort the name column alphabetically.

In order to sort the results per continent, you can use window function. In this case, for example, I have used ROW_NUM?ER(), but other functions like RANK() or DENSE_RANK() can also be used.

1SELECT
2    name, continent,
3    ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) AS row_num
4    FROM Student    

Once you’ve row_num field available, you can pivot the continent column using the CASE WHEN statement with aggregation while grouping the results by row_num field.

 1WITH ranked_names AS (
 2    SELECT
 3        name, continent,
 4        ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) AS row_num
 5        FROM Student
 6) SELECT
 7    MAX(CASE WHEN continent = 'America' THEN name END) AS America,
 8    MAX(CASE WHEN continent = 'Asia' THEN name END) AS Asia,
 9    MAX(CASE WHEN continent = 'Europe' THEN name END) AS Europe
10    FROM ranked_names
11    GROUP BY row_num;