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.

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:

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.

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.

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:

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

Highly organized & motivated Software Engineer with an application analyst/data analyst background.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store