This content originally appeared on DEV Community and was authored by Mr Punk da Silva
Repo: https://github.com/mrpunkdasilva/hackerrank/edit/main/sql/basic/the-pads/README.md
Problem Description
Generate the following two result sets:
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).
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 a 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.
Input Format
The OCCUPATIONS table is described as follows:
| Column | Type |
|---|---|
| Name | String |
| Occupation | String |
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Solution Approach
This problem requires two separate queries:
- First query to format names with occupation initials
- Second query to count occupations and format the output
Step-by-Step Explanation
Query 1: Names with Occupation Initials
- Use CONCAT to combine the name with the first letter of occupation in parentheses:
SELECT CONCAT(NAME, '(', LEFT(OCCUPATION, 1), ')')
- Specify the table to query from:
FROM OCCUPATIONS
- Order the results alphabetically by name:
ORDER BY NAME ASC
Query 2: Occupation Counts
- Use CONCAT to format the output string with the count of each occupation:
SELECT CONCAT('There are a total of ', COUNT(*), ' ', LOWER(OCCUPATION), 's.')
- Specify the table to query from:
FROM OCCUPATIONS
- Group the results by occupation to get counts:
GROUP BY OCCUPATION
- Order the results by count and then alphabetically:
ORDER BY COUNT(*), OCCUPATION ASC
Expected Output
The output will consist of multiple rows:
- First set of rows: Names with occupation initials in parentheses, sorted alphabetically
- Second set of rows: Count statements for each occupation, sorted by count and then alphabetically
This content originally appeared on DEV Community and was authored by Mr Punk da Silva
Mr Punk da Silva | Sciencx (2025-07-21T23:34:22+00:00) Hackerrank – SQL – The PADS. Retrieved from https://www.scien.cx/2025/07/21/hackerrank-sql-the-pads/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.