Publish Date - November 12th, 2022

|Last Modified - November 12th, 2022

Here’s a quick an easy SQL Hackerrank solution for one of the last “aggregate” courses. It’s an interesting one because you can end up overthinking your approach.

## The problem

We define an employee’s *total earnings* to be their monthly *salary x months* worked, and the *maximum total earnings* to be the maximum total earnings for any employee in the **Employee** table. Write a query to find the *maximum total earnings* for all employees as well as the total number of employees who have maximum total earnings. Then print these values 2 as space-separated integers.

**Input Format**

The **Employee** table containing employee data for a company is described as follows:

where *employee_id* is an employee’s ID number, *name* is their name, *months* is the total number of months they’ve been working for the company, and *salary* is the their monthly salary.

**Sample Input**

**Sample Output**

```
69952 1
```

**Explanation**

The table and earnings data is depicted in the following diagram:

The maximum *earnings* value is 69952 . The only employee with *earnings* = 66952 is *Kimberly*, so we print the maximum *earnings* value (66952) and a count of the number of employees who have earned $66592 (which is 1) as two space-separated values.

## The solution

The solution is a lot easier to understand once you know what you need to query. I commonly add comments to organize my thoughts and to almost pseudo code what is required from me.

```
-- Earnings = salary * months
-- Find MAX(earnings) FOR * employees AND
-- COUNT of those who have maximum earnings
-- (there are 7 values) - highest cost is 108064
-- Print these values as 2 seperate integers
SELECT months * salary AS earnings,
COUNT(*) FROM EMPLOYEE
GROUP BY 1
ORDER BY earnings DESC
LIMIT 1
```

If you can see, I’ve added a number of notes to analyze what’s required from the query. The maximum earnings is 108064, and there are 7 people out of 108 who earn that much. Therefore, with that knowledge you can begin to constructor your query.

Firstly, you need to generate the calculation to get *“earnings”*

`SELECT months * salary AS earnings, `

Easy enough! Since it’s a quick multiplication!

Next, within the second column, you’ll want COUNT all columns from employee, because doing this as a secondary column will give you the full count of all of the rows (which are 106).

`COUNT(*) FROM employee `

Now because this is an aggregation function (COUNT), you **must have** a HAVING or GROUP clause added to this or it will throw an error. If you *GROUP BY 1*, you’re essentially asking SQL to GROUP all values of 1 towards a particular *earnings*.

```
SELECT months * salary AS earnings, COUNT(*) FROM employee
GROUP BY 1
```

Returns:

```
1860 1
2086 1
2113 1
2438 1
2492 1
2717 1
3768 1
3958 1
4199 1
4583 1
4672 1
5760 1
8776 1
8780 1
8875 1
9072 1
9664 1
11240 1
11388 1
11799 1
12728 1
13017 1
13461 1
16848 1
17046 1
17424 1
17450 1
18720 1
19180 1
19206 1
19338 1
19665 1
20036 1
20295 1
20832 1
21021 1
21690 1
22160 1
22890 1
24474 1
24548 1
24800 1
25104 1
27544 1
27630 1
28386 1
29648 1
30450 1
30870 1
30898 1
30978 1
31140 1
31614 1
31680 1
32538 2
32718 1
32890 1
33235 1
33787 1
34590 1
37080 1
40352 1
41338 1
41360 1
41976 1
43301 1
45909 1
45980 1
47554 1
47794 1
50738 1
50876 1
54782 1
54960 1
56550 1
57132 1
59736 1
60300 1
61056 1
61789 1
66570 1
66937 1
68628 1
70095 1
70110 1
74080 1
77717 1
80454 1
81136 1
82236 1
83424 1
83644 1
86800 1
89012 1
89936 1
90816 1
91241 1
101154 1
108064 7
```

If you look at the bottom, you’ll notice you have your answer :). So all you need to do is clean up the query with some an ORDER BY and a LIMIT, and you have your answer.

## Conclusion

Like I said, an easy query once you know the answer – and figured out what you have to do! I racked my brain with this one, thinking that I need to use MAX, subqueries and even a JOIN at once to figure this out. Ultimately, the simplest solution is usually the best!

Feel free to check out my articles on related technologies: