Summary

Summary of SQL Top Earners Transcript

In this transcript, the speaker discusses solving a SQL problem related to employee earnings using a table called employee. The objective is to find the maximum total earnings for all employees and count how many employees have that maximum earning.

Steps Involved

Understanding the Data:
- The employee table contains the following fields:
  - employee_id
  - name
  - months
  - salary
Calculating Total Earnings:
- The total earnings for each employee are calculated by multiplying salary by months.
Query Implementation:
- The speaker starts with a basic query to display all employee information:
```
SELECT * FROM employee;
```
Finding Maximum Earnings:
- After running the initial query, the speaker runs a calculation for total earnings:
```
SELECT salary * months AS total_earnings FROM employee;
```
- The results are ordered in descending order to identify the highest total earnings.
Grouping and Counting:
- To count how many employees share the same maximum earnings, the speaker uses the GROUP BY clause:
```
SELECT salary * months AS total_earnings, COUNT(*) 
FROM employee 
GROUP BY total_earnings;
```

Limit Results:

Finally, to get only the maximum earnings and the count of employees, the speaker applies a LIMIT to the query:

SELECT salary * months AS total_earnings, COUNT(*) 
FROM employee 
GROUP BY total_earnings 
ORDER BY total_earnings DESC 
LIMIT 1;

This process effectively identifies the highest total earnings and counts the number of employees earning that amount.

Video Transcript

Hey everybody, I will do top earners from the aggregation section of SQL from Hacker Rank. So we're giving this table employee that has the Collins employee ID name, months and salary and we're asked to query the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. So we're going to be interested in the Collins months and salary. We're going to multiply them together to get the total earning. And then we have to find out which one is the highest and then we have to count how many people has that highest value. Let's do my SQL and I'll start off with select star from employee. And I want to see what it did. It looks like first I'll click run code. So here you are. We have this number of months and we have the salary. So we're going to take these two and multiply and let's find out what values we get for each employee. So let's go here and say salary times months and let's run code and see what we get. So as you can see, you got all these values. So what I'm going to do is I'm going to order them by salary times months, which is pretty much the same thing that I wrote here. I write it here, but I want to order this descending so that the very first row is the highest value. Let's see what we get. So as you can see, you got all these values and the highest ones are at the top. As you can see, there's one, two, three, four, five, six, seven people with the same maximum total earnings. But how can we group these together so we had a count of these people? We can use the group by and we have to count by the number of people who have the same total earnings. So go here. In addition to the salary times months, we're going to say count of salary times months. Pretty much the same thing that you typed here. You type it here so we can count how many people has that same value. And we get that as the second column. Now let's go after from employee group by salary times months. So the same thing here is what I typed for the first column here. If you want to not repeat yourself, you could create an alias for this thing and use it here. But I'll keep it simple. Just use without an alias. So let's see what we get. So as you can see it groups every maximum earnings and the number of employees that have that. That's precisely the answer we want except we only want the very first line, the very first row. And to limit that we can use limit one at the end here. So limit one to make sure we just take the very first row and all the subsequent rows are discarded. And there you go.