Skip to content

Prepare For Coder Interview – Denny

  • Basic
  • Medium
  • Hard
  • Architect
  • Life

LeetCode: Students Report By Geography

Posted on March 16, 2018July 26, 2020 by braindenny

Students Report By Geography



Similar Problems:

  • CheatSheet: SQL & MySql
  • CheatSheet: Leetcode For Code Interview
  • CheatSheet: Common Code Problems & Follow-ups
  • Tag: #sql

A U.S graduate school has students from Asia, Europe and America. The students’ location information are stored in table student as below.

| name   | continent |
|--------|-----------|
| Jack   | America   |
| Pascal | Europe    |
| Xi     | Asia      |
| Jane   | America   |

Pivot the continent column in this table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia and Europe respectively. It is guaranteed that the student number from America is no less than either Asia or Europe.
For the sample input, the output is:

| America | Asia | Europe |
|---------|------|--------|
| Jack    | Xi   | Pascal |
| Jane    |      |        |

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

Github: code.dennyzhang.com

Credits To: leetcode.com

Leave me comments, if you have better ways to solve.


## https://code.dennyzhang.com/students-report-by-geography
select t1.name as America, t2.name as Asia, t3.name as Europe
from
    (select (@cnt1 := @cnt1 + 1) as id, name
    from student
    cross join (select @cnt1 := 0) as dummy
    where continent='America'
    order by name) as t1 
    left join
    (select (@cnt2 := @cnt2 + 1) as id, name
    from student
    cross join (select @cnt2 := 0) as dummy
    where continent='Asia'
    order by name) as t2
    on t1.id = t2.id
    left join
    (select (@cnt3 := @cnt3 + 1) as id, name
    from student
    cross join (select @cnt3 := 0) as dummy
    where continent='Europe'
    order by name) as t3
    on t1.id = t3.id
linkedin
github
slack

Post Views: 7
Posted in HardTagged sql

Post navigation

LeetCode: Shortest Distance in a Plane
LeetCode: Average Salary: Departments VS Company

Leave a Reply Cancel reply

Your email address will not be published.

Tags

#array #backtracking #bfs #binarytree #bitmanipulation #blog #classic #codetemplate #combination #dfs #dynamicprogramming #game #graph #greedy #heap #inspiring #interval #linkedlist #manydetails #math #palindrome #recursive #slidingwindow #stack #string #subarray #trie #twopointer #twosum binarysearch editdistance hashmap intervaldp knapsack monotone oodesign presum rectangle redo review rotatelist series sql treetraversal unionfind

Recent Posts

  • a
  • a
  • a
  • a
  • a

Recent Comments

    Archives

    Categories

    • Amusing
    • Basic
    • Easy
    • Hard
    • Life
    • Medium
    • Resource
    • Review
    • Series
    • Uncategorized
    Proudly powered by WordPress | Theme: petals by Aurorum.