OCCUPATIONS Problem Hackerrank SQL Solutions Medium

In this Video Im solving the OCCUPATIONS Problem from Hackerrank SQL Problems.
My Solution:
with doctor as (
Select
name,
ROW_NUMBER() OVER(order by name) as rn
FROM OCCUPATIONS
WHERE occupation LIKE 'Doctor'
)
,professor as (
Select
name,
ROW_NUMBER() OVER(order by name) as rn
FROM OCCUPATIONS
WHERE occupation LIKE 'Professor'
)
,singer as (
Select
name,
ROW_NUMBER() OVER(order by name) as rn
FROM OCCUPATIONS
WHERE occupation LIKE 'Singer'
)
,actor as (
Select
name,
ROW_NUMBER() OVER(order by name) as rn
FROM OCCUPATIONS
WHERE occupation LIKE 'Actor'
)
SELECT
d.name,
p.name,
s.name,
a.name
FROM professor p
LEFT JOIN doctor d on d.rn = p.rn
LEFT JOIN singer s on s.rn = p.rn
LEFT JOIN actor a on a.rn = p.rn

[@marialexandra1421]

thanks for the explanation! I think that your final doubt could be solved by using an outer join. That way you would have all values even if the table on the left/right does not have a matching value!

[@tanyifan8630]

You can full outer join all of the tables. That way you can get all of the null values regardless of column count.

[@virenparshetty4350]

by far the best explanation, thank you!

[@user-qg2gj5bx5u]

very neat and clear approach and explanation❤

[@pksk164]

Good Explanation Mentor

[@karthikmanikanta6371]

i had a similar approach and i dint understand where i was going wrong, thank you so much

[@thecodingmentor7701]

Glad it helped!

[@joaquinhernandez3236]

Great video, thanks a lot for explaining. Do you know a solution that is fully correct (in which you don't need to know which column is longer as you did with the professor one)

[@ducminh7568]

put "or" in the join 'd.rn=p.rn or s.rn= d.rn'. List of all combination

[@malcolmisaiah_]

Bless you

[@karthikdnd]

Lengthy code

[@Yog3shPatel]

if this medium then Im screwed.

[@martinberger365]

Definetelly not a very smart approach, far too complicated

[@thecodingmentor7701]

Hi martin, thanks for the feedback. Feel free to post a better solution so we can all get better :)

[@Puzzlophus]

WITH PivotedData AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowLine,
CASE Occupation WHEN 'Doctor' THEN Name END AS Doctor,
CASE Occupation WHEN 'Professor' THEN Name END AS Professor,
CASE Occupation WHEN 'Singer' THEN Name END AS Singer,
CASE Occupation WHEN 'Actor' THEN Name END AS Actor
FROM OCCUPATIONS
)
SELECT
MIN(Doctor) AS Doctor,
MIN(Professor) AS Professor,
MIN(Singer) AS Singer,
MIN(Actor) AS Actor
FROM PivotedData
GROUP BY RowLine
we can further order by rowline if needed. ASC as default and DESC to play around :D

[@sohailsayyed2]

I was thinking something of same sorts. It is almost alike to your solution. I really don't understand why need MIN() there. why MIN() is not returning one value. Eager to learn

[@sf-spark129]

@@sohailsayyed2 With the group by clause, we can only select desired columns(doctor, professor, singler, actor) using, min(), max(), etc. it doesn't matter whichever func you use since each group by contains one value and the rest is null.