Solving a Tricky HackerRank SQL Problem — Combining Queries with ORDER BY

Hey there, fellow Data Folks and SQL Ninjas! Today, I want to share a simple solution to a challenging Hackerrank problem using MySQL.

The problem involves querying a list of all names in the “OCCUPATIONS” table, along with the first letter of each profession in parentheses.
For instance, “AnactorName(A)”, “ADoctorName(D)”, and so on.

Additionally, we need to find the number of occurrences of each occupation in the “OCCUPATIONS” table and display the results in this format: “There are a total of 2 doctors” or “There are a total of 2 singers.”

Snapshot of the problem statement and SQL code.

To achieve this, we must create two separate queries and then combine them. I immediately knew the UNION keyword would come in handy for merging the results. So, let’s dive into the step-by-step process:

Step 1: Crafting the First Query To start, we focus on the first query. It requires extracting both the name and the first letter of each occupation. Here’s how I achieved that:

SELECT CONCAT(NAME, "(", LEFT(OCCUPATION, 1), ")") AS NAMEOCC
FROM OCCUPATIONS;

The “ LEFT(OCCUPATION, 1) ” function extracts the first letter from the OCCUPATION column, while the “ CONCAT()” function beautifully joins the name, the opening parenthesis, the first occupation letter, and the closing parenthesis into a single string.

Step 2: Joining with the Second Query Next, we combine the first query with the second one using “ UNION “. Here’s what the second query looks like:

SELECT CONCAT("There are a total of ", COUNT(OCCUPATION), " ", LOWER(OCCUPATION), "s.") AS NAMEOCC
FROM OCCUPATIONS
GROUP BY OCCUPATION;

This query skillfully enables us to achieve the desired output format, such as “There are a total of 2 doctors.”

The Tricky Part: Sorting! Now comes the tricky part. The first query should be sorted alphabetically, and the second query must be ordered by occupation count in ascending order. But what if multiple occupations have the same count? They should be sorted alphabetically as well.

At first, using “ ORDER BY “ in both queries might seem like the obvious solution, but it wouldn’t yield the expected result. This is because ORDER BY in SQL is executed after the whole query result is retrieved, meaning it won’t work correctly in subqueries or CTEs. Using ORDER BY in a derived table can even cause an error in SQL SERVER.

To overcome this challenge, I gave both queries the same alias, “NAMEOCC ”, in the subquery. By doing so, I merged the results horizontally into a single row. Now, querying the alias “ (NAMEOCC) “ in the subquery returns both queries’ results merged together. Finally, we can order our merged query results in ascending order, achieving both alphabetical sorting and ascending occupation count sorting.

Here’s the combined query with ORDER BY:

SELECT NAMEOCC FROM
(
(SELECT CONCAT(NAME,"(", LEFT(OCCUPATION, 1), ")") AS NAMEOCC
FROM OCCUPATIONS)
UNION 
(SELECT CONCAT("There are a total of ", COUNT(OCCUPATION), " ",
LOWER(OCCUPATION), "s.") AS NAMEOCC
FROM OCCUPATIONS
GROUP BY OCCUPATION)
) SUB_1
ORDER BY 1;

I admit, it took some clever thinking to solve this puzzle, and I’m sure there are other fantastic ways to approach it using SQL. So, if you have any suggestions or tips, I’d be thrilled to learn from you!

In conclusion, I hope you enjoyed following my journey in solving this tricky HackerRank SQL problem. You can access the problem on Hackerrank if you already have an account. Stay tuned as I’ll share more of my approaches to SQL problems on Hackerrank and Stratascratch soon.

Thank you for joining me on this adventure, and happy coding! You can connect with me on Linkedin or reach out to me through Gmail.