The following advanced-level SQL problems. Focusing on reshaping tables.
LeetCode 618
Students Report by Geography (Hard) [link]
There is no id column, so we need to construct an id using row_number().
We need to GROUP BY rank and MAX for this case where value is string. In other cases such as values are numeric, we use SUM function.
When there are multiple elements in one group, CASE WHEN can only get the first one of them. This is why we need MAX().
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 *,
row_number() OVER(PARTITION BY continent ORDER BY name) AS rnk
FROM Student
) t
GROUP BY rnk;
LeetCode 1179
Reformat Department Table (Easy) [link]
SELECT id,
SUM(CASE month WHEN 'Jan' THEN revenue ELSE NULL END) AS Jan_Revenue,
SUM(CASE month WHEN 'Feb' THEN revenue ELSE NULL END) AS Feb_Revenue,
SUM(CASE month WHEN 'Mar' THEN revenue ELSE NULL END) AS Mar_Revenue,
SUM(CASE month WHEN 'Apr' THEN revenue ELSE NULL END) AS Apr_Revenue,
SUM(CASE month WHEN 'May' THEN revenue ELSE NULL END) AS May_Revenue,
SUM(CASE month WHEN 'Jun' THEN revenue ELSE NULL END) AS Jun_Revenue,
SUM(CASE month WHEN 'Jul' THEN revenue ELSE NULL END) AS Jul_Revenue,
SUM(CASE month WHEN 'Aug' THEN revenue ELSE NULL END) AS Aug_Revenue,
SUM(CASE month WHEN 'Sep' THEN revenue ELSE NULL END) AS Sep_Revenue,
SUM(CASE month WHEN 'Oct' THEN revenue ELSE NULL END) AS Oct_Revenue,
SUM(CASE month WHEN 'Nov' THEN revenue ELSE NULL END) AS Nov_Revenue,
SUM(CASE month WHEN 'Dec' THEN revenue ELSE NULL END) AS Dec_Revenue
FROM Department
GROUP BY id;
LeetCode 1777
Product’s Price for Each Store (Easy) [link]
SELECT
product_id,
SUM(CASE store WHEN 'store1' THEN price ELSE NULL END) AS store1,
SUM(CASE store WHEN 'store2' THEN price ELSE NULL END) AS store2,
SUM(CASE store WHEN 'store3' THEN price ELSE NULL END) AS store3
FROM Products
GROUP BY product_id;
