Solving ‘The Pads’ Hacker Rank Challenge

Hi Everyone,

Today I will be discussing how I solved, The Pads, Hacker Rank Challenge using Oracle SQL.

The Objective:

Query an alphabetically ordered list of all names in OCCUPATONS, immediately followed by the first letter of each profession as a parenthetical.

For example: AnActorName(A), ADoctorName(D),AProfessorName(P), and ASingerName(S).

1. Query the number of occurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:

There are total of [occupation_count] [occupation]s.

where [occupation_count] is the number of occurrences of an occupation in OCCUPATIONS and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.

My first query I used a case statement. When the occupation equaled, for example, Doctor, then it would concatenate the name and (D). It would also do the same for Actor, Singer, and Professor.

select 
case when occupation = ‘Doctor’ then name ||’(D)’
 when occupation = ‘Actor’ then name ||’(A)’
 when occupation = ‘Singer’ then name || ‘(S)’
 when occupation = ‘Professor’ then name || ‘(P)’ else null end occ
from occupations

However, more efficiently, the first query can be solved by using a simple substring as well and concatenating it with the name and parenthesis. For instance:

select 
 name||'('||substr(occupation,1,1)||')' occ
from occupations

Less code involved. :)

I give the above query an alias of occ for a reason I will explain soon.

The second query is about counting the number of occurrences of each occupation.

I used a case statement to return a 1 or 0 based on the specific occupation returned and summing the amount. Lastly, I concatenated some text around this amount, so it reads as ‘There are a total of 5 Doctors.’ The lower function is to lowercase the occupation to match the sample output above.

select 
'There are a total of '|| 
 sum(case when occupation = 'Doctor' then 1 
 when occupation = 'Actor then 1 
 when occupation = 'Singer' then 1 
 when occupation = 'Professor' then 1 else 0 end) || ' ' || lower(occupation) || 's.' occ
from occupations
group by occupation

However, there is a downside to this query. I’m hard-coding the occupation. If there was a new occupation added to the table this query would not return the proper sum.

select 
'There are a total of '|| 
 count(occupation) || ' ' || lower(occupation) || 's.' occ
from occupations
group by occupation
order by occ

The above code is a much better way of calculating the occurrences. If the table was updated with a new occupation it would still return correctly.

I join both query one and two using an union all and use the alias to sort in ascending order (by default).

Final Query:

select 
 name||'('||substr(occupation,1,1)||')' occ
from occupations
union all
select 
'There are a total of '|| 
 count(occupation) || ' '|| lower(occupation) || 's.' occ
from occupations
group by occupation
order by occ;

It passed! I hope this helps you with solving similar challenges. Please let me know what you think. Until next time!