SQL: HackerRank — Top Earners

The problem in picture is titled “Top Earners” at Hackerrank.

Here, we need to find the maximum earnings of the employees as per the logic of max earning = salary x month. And then later on find, how much the max salary among all the employees and how many employees have it. Unlike real world, where all this information is hidden and we just live on in delusion :)

To solve this problem, my first thought was to make a CTE table and then get the max earning from there. However, one thing that was not working was aggregation. For example, if you write COUNT(*), it gives you a number. But if you write

SELECT SALARY, COUNT(*) FROM EMPLOYEES

this would not work. Why? Because the Count(*) doesn’t have any index to count. And hence we have to use Group By function. What group by does is tells the code that what index it has to count. For example, if you write

SELECT SALARY, COUNT(*) FROM EMPLOYEES
GROUP BY SALARY

this tells it to count the number of salaries. So if 4 people have a salary of 10,000, the count function would return 4.

Get Anmol Goyal’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Once you understand this, the problem is fairly easy. We perform the aggregation, use group by and order by and return our answer. I will explain line-by-line solution below.

SELECT (SALARY * MONTHS) AS MAX_SALARY, COUNT(*) FROM EMPLOYEE
GROUP BY MAX_SALARY
ORDER BY MAX_SALARY DESC
LIMIT 1

In the first line: we select salary x months as described by the problem, and when we write (salary * months) AS max_salary, it essentially makes max_salary as a new column for the table but doesn’t change the table per say. Now we have max_salary as desired and count but this is for the complete table.

In the second line: we use group by as discussed earlier. We group by max_salary so that we can get the number of individuals with max_salary and not the monthly one.

In the third line: We order by in the descending order, because we need the maximum salary and when we apply LIMIT 1, it only gives us the maximum value.

Learning: If you are using any aggregated query, ensure that the SELECT list contains aggregated columns. Else use GROUP BY to aggregate the columns in query.