Occupations | SQL Advanced Select | HackerRank Solution

A lesson that teaches you how to solve the following problem from the SQL section in HackerRank.
www.hackerrank.com/challenges...
Learn: Building a News Blog Web App with Next.js and Express
www.udemy.com/course/building...

[@ahmadhamzakhan8903]

Very detailed explanation !!! Keep uploading the good stuff.

[@nosejaja]

SELECT MAX(Doctor), MAX(Professor), MAX(Singer), MAX(Actor)
FROM (SELECT COUNT(*) OVER(PARTITION BY Occupation ORDER BY Name) AS row_num,
IF(Occupation = 'Doctor',Name, NULL) AS Doctor,
IF(Occupation = 'Professor',Name, NULL) AS Professor,
IF(Occupation = 'Singer',Name, NULL) AS Singer,
IF(Occupation = 'Actor',Name, NULL) AS Actor
From OCCUPATIONS) AS derived_table GROUP BY row_num;

[@ajayrawtani5321]

Best explanation

[@shafniiqbal1601]

very good explanation

[@surabhijagadish5210]

Nice

[@tiagobovolin]

Thank you very much.

[@nbktechworld]

You're welcome. Stop by our Discord @ discord.com/invite/MJwGcwcdae

[@hongthuongtran2027]

in the case we have many occupations (not only 4 occupations), so what we can do in this case?

[@nbktechworld]

Follow same pattern and add more columns. Add IF with NewAlias, then do MIN(NewAlias) in outer SELECT.

[@eranshapira6533]

great video, but I didn't get why you need to use an Aggregation in the first select

[@nbktechworld]

Try removing it and see what happens! When you are unsure what something does, try removing it and see the effect before / after.

[@eranshapira6533]

yea I saw it didn't work without it but i didn't understand why. It also seems wrong to me that it let you perform that on a string variable@@nbktechworld

[@rajashekharvattipelli4194]

why do we have to use min() function

[@nbktechworld]

We need an aggregate function to use with the GROUP BY. In the inner SELECT, we build a table whose rows are like this:
1 NULL NULL NULL Eve
2 NULL NULL NULL Jennifer
...
1 Aamina NULL NULL NULL
...
We need to combine all the rows (group them) by the row number (the first column). For the example, above, for number 1, it would consolidate the two separate rows into one:
1 Aamina NULL NULL Eve
But to get the name of the person there we have to aggregate a value that is supposed to be derived after going through the rows with same row number. One example of aggregation is consolidating all the rows into a specific group, then counting them with COUNT to get a count of how many rows were consolidated into that category. In the case here, we don't want to count, but rather extract the single non-NULL value among all the rows with the same row number.
The way the rows are built, there's only one column value for the specific person for that specific row number, all the other values in other rows for that specific row number being NULL.
You can also use MAX and it works the same way.
To better understand the query, try breaking it into pieces, starting from the simple SELECT * FROM OCCUPATIONS; Try to understand each step of the way as you build the query. See what the inner SELECT by itself does. See what having only one column does. Then keep incrementing and seeing how the query evolves.