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.
WITH occupation_row AS (
SELECT o.name , o.occupation , ROW_NUMBER() OVER (PARTITION BY o.occupation ORDER BY o.name ) row_num FROM occupations o
), doctor_cte AS (
SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Doctor'
), actor_cte AS (
SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Actor'
), professor_cte AS (
SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Professor'
), singer_cte AS (
SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Singer'
)SELECT dc.name , pc.name , sc.name , ac.name FROM
actor_cte ac FULL JOIN professor_cte pc on ac.row_num = pc.row_num FULL JOIN doctor_cte dc on ac.row_num = dc.row_num
FULL JOIN singer_cte sc
on ac.row_num = sc.row_num
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 →
WITH occupation_row AS ( SELECT o.name , o.occupation , ROW_NUMBER() OVER (PARTITION BY o.occupation ORDER BY o.name ) row_num FROM occupations o ), doctor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Doctor' ), actor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Actor' ), professor_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Professor' ), singer_cte AS ( SELECT r.name , r.row_num FROM occupation_row r where r.occupation = 'Singer' )SELECT dc.name , pc.name , sc.name , ac.name FROM actor_cte ac FULL JOIN professor_cte pc on ac.row_num = pc.row_num FULL JOIN doctor_cte dc on ac.row_num = dc.row_num FULL JOIN singer_cte sc on ac.row_num = sc.row_num