We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
SELECT
COALESCE(MAX(CASE WHEN occupation = 'Doctor' THEN name END), 'NULL'), -- conditional aggregation
COALESCE(MAX(CASE WHEN occupation = 'Professor' THEN name END), 'NULL'), -- inside the max function the statement only passes the name if it matches with the occupation otherwise it passes null
COALESCE(MAX(CASE WHEN occupation = 'Singer' THEN name END), 'NULL'),
COALESCE(MAX(CASE WHEN occupation = 'Actor' THEN name END), 'NULL')
FROM
(
SELECT
name,
occupation,
ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn -- assigns the sequential row number to each row
FROM
OCCUPATIONS
) AS t
GROUP BY
rn
-- groups the result by the row number
ORDER BY
rn;
-- order the final result based on the row number
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
Occupations
You are viewing a single comment's thread. Return to all comments →
SELECT COALESCE(MAX(CASE WHEN occupation = 'Doctor' THEN name END), 'NULL'), -- conditional aggregation COALESCE(MAX(CASE WHEN occupation = 'Professor' THEN name END), 'NULL'), -- inside the max function the statement only passes the name if it matches with the occupation otherwise it passes null COALESCE(MAX(CASE WHEN occupation = 'Singer' THEN name END), 'NULL'), COALESCE(MAX(CASE WHEN occupation = 'Actor' THEN name END), 'NULL') FROM ( SELECT name, occupation, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS rn -- assigns the sequential row number to each row FROM OCCUPATIONS ) AS t GROUP BY rn -- groups the result by the row number ORDER BY rn; -- order the final result based on the row number