Students Report By Geography - Solution & Explanation

Problem Overview: The Students table stores a student's name and their continent. The task is to produce a report with three columns: America, Asia, and Europe. Each column lists student names from that continent in alphabetical order, aligned row‑by‑row. When a continent has fewer students, the remaining cells should appear as NULL. This is essentially a pivot transformation with ordered grouping.

Approach 1: Window Function + Conditional Aggregation (O(n log n) time, O(n) space)

Assign a sequence number to students within each continent using ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name). This ranks students alphabetically inside their continent. Once each row has a rank, group rows by that rank and use conditional aggregation such as MAX(CASE WHEN continent='America' THEN name END) to place each name into the correct column. The database sorts names during the window function step (O(n log n)), then performs a linear aggregation. This approach is clean, portable across most SQL engines, and widely used for pivot-style interview questions.

This technique relies heavily on SQL window functions and grouping logic. The key insight: ranking per continent allows rows from different continents to align by index before pivoting.

Approach 2: MySQL User Variables Ranking (O(n log n) time, O(n) space)

In older MySQL versions without window functions, you can simulate ranking using session variables. First sort students by continent and name. Track the current continent and increment a counter whenever the continent stays the same. This produces a row index similar to ROW_NUMBER(). After generating the index, apply the same conditional aggregation technique (MAX(CASE...)) grouped by the generated index. The result aligns students from different continents into the same output rows.

This method works when window functions are unavailable but is more error‑prone and less readable. Modern SQL engines strongly favor window functions for this pattern. The logic still depends on grouping and pivoting operations common in database query design.

Recommended for interviews: The window function approach is the expected solution today. Interviewers want to see that you can partition data, assign ranks, and pivot using conditional aggregation. Mentioning the variable-based workaround shows deeper MySQL knowledge, but the ROW_NUMBER() strategy demonstrates stronger mastery of modern SQL and window functions.