TOP EARNERS FROM EMPLOYEE SALARY TABLE – QUERY WITH SOLUTION

PROBLEM STATEMENT:

We define an employee’s total earnings to be their monthly salary*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 as 2 space-separated integers.

Input Format:

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

employee_idnamemonthssalary
123Naveen36754
342Chandu43257
532John54789
124Snow47856
432Arya121536
453Kimberly164372

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.

Expected Output:

69952 1

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

SOLUTION:

SELECT a.earnings, 
       SUM(rnk) 
FROM   (SELECT salary * months earnings, 
               Rank() 
                 over( 
                   ORDER BY salary*months DESC) rnk 
        FROM   employee) a 
WHERE  a.rnk = 1 
GROUP  BY a.earnings; 

Comments

comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: