# $618. 学生地理信息报告
# https://leetcode-cn.com/problems/students-report-by-geography/

# SQL架构
Create table If Not Exists Student (name varchar(50), continent varchar(7));
Truncate table Student;
insert into Student (name, continent) values ('Jane', 'America');
insert into Student (name, continent) values ('Pascal', 'Europe');
insert into Student (name, continent) values ('Xi', 'Asia');
insert into Student (name, continent) values ('Jack', 'America');

# Write your MySQL query statement below
select
    max(
        case
            continent
            when 'America' then name
            else null
        end
    ) as America,
    max(
        case
            continent
            when 'Asia' then name
            else null
        end
    ) as Asia,
    max(
        case
            continent
            when 'Europe' then name
            else null
        end
    ) as Europe
from
    (
        select
            name,
            continent,
            row_number() over (
                partition by continent
                order by
                    name
            ) as rk
        from
            Student
    ) tmp
group by
    rk;

# clean-up
drop table Student;