Students report by geography
SQL data engineering interview problem. Difficulty: intermediate. Pattern: Pivot. About 14 minutes. Part of the Pro drill bank.
student_geo contains student names and continents with columns name and continent ('America', 'Asia', or 'Europe'). Pivot the names so that each continent becomes a column header (America, Asia, Europe). Student names within each column must be sorted alphabetically ascending. If a continent has fewer students than another, pad the empty trailing cells with NULL. Order rows so the alphabetical sequences align from top to bottom.
Requirements
- Output columns: America, Asia, Europe.
- Sort order aligns by alphabetical position index.
Constraints
- continent is one of 'America', 'Asia', 'Europe'.
- name contains distinct strings.
- Names must be sorted alphabetically inside each continent column.
Examples
Input: student_geo name,continent Jack,America Pascal,Europe Xiang,Asia Jane,America Ravi,Asia Output: America | Asia | Europe Jack | Ravi | Pascal Jane | Xiang | NULL Why this passes: Names are ranked alphabetically inside each continent and rows are aligned by that rank, so Jack/Ravi/Pascal share row 1 and Europe gets NULL in row 2.
Input: student_geo name | continent Alice | America Bob | Asia Output: America | Asia | Europe Alice | Bob | NULL Why this passes: Rank 1 pairs Alice and Bob; Europe is empty so it receives NULL.
Topics: lakebench, sql, pivot, row-number.
More SQL interview questions · All interview problems · Learn data engineering
Students report by geography
Interview-style drill: Pivot student names by continent into parallel alphabetical columns without numeric aggregations.
`student_geo` contains student names and continents with columns `name` and `continent` (`'America'`, `'Asia'`, or `'Europe'`). Pivot the names so that each continent becomes a column header (`America`, `Asia`, `Europe`). Student names within each column must be sorted alphabetically ascending. If a continent has fewer students than another, pad the empty trailing cells with NULL. Order rows so the alphabetical sequences align from top to bottom.