Top Earners in SQL | HackerRank Solution

Hello coders, today we are going to solve Top Earners HackerRank Solution in SQL.

Top Earners

Contents

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 as 2 space-separated integers.

Input Format

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

ColumnType
employee_idInteger
nameString
monthsInteger
salaryInteger

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

employee_idnamemonthssalary
12228Rose151968
33645Angela13443
45692Frank171608
56118Patrick71345
59725Lisa112330
74197Kimberly164372
78454Bonnie81771
83565Michael62017
98607Todd53396
99989Joe93573

Sample Output

69952 1

Explanation

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

employee_idnamemonthssalaryearnings
12228Rose15196829520
33645Angela134433443
45692Frank17160827336
56118Patrick713459415
59725Lisa11233025630
74197Kimberly16437269952
78454Bonnie8177114168
83565Michael6201712102
98607Todd5339616980
99989Joe9357332157

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 – Top Earners in SQL

MySQL

select max(months * salary), count(months * salary) 
from Employee where (months * salary) 
= (select max(months * salary) from Employee);

Disclaimer: The above Problem (Top Earners) is generated by Hacker Rank but the Solution is Provided by CodingBroz. This tutorial is only for Educational and Learning Purpose.

4 thoughts on “Top Earners in SQL | HackerRank Solution”

  1. SELECT MAX(salary*months),COUNT(employee_id) from Employee
    GROUP BY salary*months
    ORDER BY salary*months DESC
    LIMIT 1;

  2. SELECT salary * months AS earnings, COUNT(employee_id)
    FROM Employee
    GROUP BY earnings
    ORDER BY earnings DESC
    LIMIT 1;

  3. with earning_monthly as (select months*salary as earning from employee),
    max_earning as ( select max ( earning) as earning_max from earning_monthly)
    select max ( earning_monthly.earning ) , count(*) from earning_monthly join max_earning on earning_monthly.earning=max_earning.earning_max;

    // what is wrong in this query
    \

  4. select salary*months,count(salary*months)
    from Employee
    where salary*months=(select max (salary*months) from Employee)
    group by salary*months;

Leave a Comment

Your email address will not be published.