This another problem from HackerRank / Advanced Select Challenges called The PADS, Here is the SQL solution in MySQL environment.

Problem Description

Generate the following two result sets:

  1. Query an alphabetically ordered list of all names in OCCUPATIONS, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).
  2. Query the number of ocurrences of each occupation in OCCUPATIONS. Sort the occurrences in ascending order, and output them in the following format:
There are a total of [occupation_count] [occupation]s.
  1. 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.

To know the details about the problem visit : The PADS

Solution

Please check the snippet for the sql implementation. If you have different approach in mind feel free to share in the comment below.

Originally posted at: github.com by @Thomas-George-T