CODEWITHSUNDEEP
mysql
Linnan Chen
Linnan Chen

Reputation: 21

Hackerrank SQL challenge: Occupations

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Maria Explanation

The first column is an alphabetically ordered list of Doctor names. The second column is an alphabetically ordered list of Professor names. The third column is an alphabetically ordered list of Singer names. The fourth column is an alphabetically ordered list of Actor names. The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

After searching and learning from internet, the correct answer for this challenge by using MYSQL:

SELECT MIN(IF(Occupation = 'Doctor',Name,NULL)),MIN(IF(Occupation = 'Professor',Name,NULL)),MIN(IF(Occupation = 'Singer',Name,NULL)),MIN(IF(Occupation = 'Actor',Name,NULL)) 
FROM(
    SELECT ROW_NUMBER() OVER(PARTITION BY Occupation
                             ORDER BY Name) AS row_num,
            Name, 
            Occupation
    FROM OCCUPATIONS) AS ord
GROUP BY row_num

My question is what does the function MIN doing in this code. also, the answer still correct after changing MIN to MAX but if i delete min or max, the answer is wrong.

Upvotes: 2

Views: 21473

Answers (5)

Samvita_Karkal
Samvita_Karkal

Reputation: 1

The following is a much better solution to the problem:

WITH RankedOccupations AS (
    SELECT
        NAME,
        OCCUPATION,
        ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS rn
    FROM
        OCCUPATIONS
)
SELECT
    MAX(CASE WHEN OCCUPATION = 'Doctor' THEN NAME END) AS Doctor,
    MAX(CASE WHEN OCCUPATION = 'Professor' THEN NAME END) AS Professor,
    MAX(CASE WHEN OCCUPATION = 'Singer' THEN NAME END) AS Singer,
    MAX(CASE WHEN OCCUPATION = 'Actor' THEN NAME END) AS Actor
FROM
    RankedOccupations
GROUP BY
    rn
ORDER BY
    rn;

Upvotes: 0

ika .zube
ika .zube

Reputation: 1

I'd tried in another format but still similar code, but why it doesn't work?

select 
    min(if(Occupation = 'Doctor', Name, NULL)),
    min(if(Occupation = 'Professor', Name, NULL)),
    min(if(Occupation = 'Singer', Name, NULL)),
    min(if(Occupation = 'Actor', Name, NULL))
from(
    select row_number() OVER(partition by Occupation 
                             order by Name) as pr,
        Name,
        Occupation
    from OCCUPATIONS) as row
group by pr

Upvotes: 0

Vineet Pokharna
Vineet Pokharna

Reputation: 11

-- Min or Max should be used, as if not used it will be required in group by function then

WITH my_cte AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS row_num, Name,  Occupation
FROM OCCUPATIONS
)

SELECT MIN(IF(Occupation = 'Doctor',Name,NULL)),MIN(IF(Occupation = 'Professor',Name,NULL)),MIN(IF(Occupation = 'Singer',Name,NULL)),MIN(IF(Occupation = 'Actor',Name,NULL)) 
from my_cte
GROUP BY row_num

Upvotes: 1

alex del
alex del

Reputation: 1

Min is just a "trick" to select the non-NULL value in the group RowNumber.

set @r1=0, @r2=0, @r3=0, @r4=0;
with interm as (select 
case when Occupation="Doctor" then (@r1:=@r1+1) when Occupation="Professor" then (@r2:=@r2+1) when Occupation="Singer" then (@r3:=@r3+1) when Occupation="Actor" then (@r4:=@r4+1) end as RowNumber,
case when Occupation="Doctor" then Name end as Doctor,
case when Occupation="Professor" then Name end as Professor,
case when Occupation="Singer" then Name end as Singer,
case when Occupation="Actor" then Name end as Actor 
from OCCUPATIONS 
order by Name)
select min(Doctor), min(Professor), min(Singer), min(Actor)
from interm
group by RowNumber;

It is an implementation without Partition and with variable. Interm , the intermediary table gives for the 8 first rows:

1 Aamina NULL NULL NULL 1 NULL Ashley NULL NULL 2 NULL Belvet NULL NULL 3 NULL Britney NULL NULL 1 NULL NULL Christeen NULL 1 NULL NULL NULL Eve 2 NULL NULL Jane NULL 2 NULL NULL NULL Jennifer

By grouping by rownumber and selecting the only non-NULL (by min or max), the solution is obtained.

Upvotes: -1

Amina mina Belouadah
Amina mina Belouadah

Reputation: 1

Solution by MS SQL Server

Select Doctor, Professor, Singer, Actor from (
 SELECT  name, 
               occupation,
        ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS row_number
      FROM OCCUPATIONS
)dt
PIVOT (
    MAX(name)
    FOR [Occupation] IN ([Doctor],  [Professor],
                       [Singer],  [Actor])
)pt
ORDER BY row_number;

Upvotes: 0

Related Questions