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 base_tab as (
select
case
when occupation ='Doctor' then name
else null
end as doctor,
case
when occupation='Professor' then name
else null
end as professor,
case
when occupation='Singer' then Name
else null
end as singer,
case
when Occupation='Actor' then Name
else null
end as actor
from OCCUPATIONS),
doctor_tab as (
select row_number() OVER (ORDER By doctor)as rn,
doctor from (
select distinct doctor
from base_tab
where doctor is not null) a),
professor_tab as (
select row_number() OVER (ORDER By professor)as rn,
professor from (
select distinct professor
from base_tab
where professor is not null) a),
actor_tab as (
select row_number() OVER (ORDER By actor)as rn,
actor from (
select distinct actor
from base_tab
where actor is not null) a),
singer_tab as (
select row_number() OVER (ORDER By singer)as rn,
singer from (
select distinct singer
from base_tab
where singer is not null) a),
doc_prof_tab as (
select doctor,b.professor from doctor_tab a left join professor_tab b on a.rn=b.rn
UNION
select a.doctor,b.professor from doctor_tab a right join professor_tab b on a.rn=b.rn
),
dp_rn as (
select row_number() over () as rn,
a.* from doc_prof_tab a
),
dps_tab as (
select a.,b.singer from dp_rn a left join singer_tab b on a.rn=b.rn
UNION
select a.,b.singer from dp_rn a right join singer_tab b on a.rn=b.rn
),
dps_rn as (
select
row_number() over () as rn,
doctor,professor,singer from dps_tab
),
dpsa_tab as (
select a.,b.actor from dps_rn a left join actor_tab b on a.rn=b.rn
UNION
select a.,b.actor from dps_rn a right join actor_tab b on a.rn=b.rn
)
select doctor,professor,singer,actor from dpsa_tab;
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 →
Works with Mysql:
#with base_tab as ( select case when occupation ='Doctor' then name else null end as doctor, case when occupation='Professor' then name else null end as professor, case when occupation='Singer' then Name else null end as singer, case when Occupation='Actor' then Name else null end as actor from OCCUPATIONS), doctor_tab as ( select row_number() OVER (ORDER By doctor)as rn, doctor from ( select distinct doctor from base_tab where doctor is not null) a), professor_tab as ( select row_number() OVER (ORDER By professor)as rn, professor from ( select distinct professor from base_tab where professor is not null) a), actor_tab as ( select row_number() OVER (ORDER By actor)as rn, actor from ( select distinct actor from base_tab where actor is not null) a), singer_tab as ( select row_number() OVER (ORDER By singer)as rn, singer from ( select distinct singer from base_tab where singer is not null) a), doc_prof_tab as ( select doctor,b.professor from doctor_tab a left join professor_tab b on a.rn=b.rn UNION select a.doctor,b.professor from doctor_tab a right join professor_tab b on a.rn=b.rn ), dp_rn as ( select row_number() over () as rn, a.* from doc_prof_tab a ), dps_tab as ( select a.,b.singer from dp_rn a left join singer_tab b on a.rn=b.rn UNION select a.,b.singer from dp_rn a right join singer_tab b on a.rn=b.rn ), dps_rn as ( select row_number() over () as rn, doctor,professor,singer from dps_tab ), dpsa_tab as ( select a.,b.actor from dps_rn a left join actor_tab b on a.rn=b.rn UNION select a.,b.actor from dps_rn a right join actor_tab b on a.rn=b.rn ) select doctor,professor,singer,actor from dpsa_tab;