SQL Screen Drills (Timed Sets)

Most people learn row number, rank and dense rank, but in interviews, they struggle to apply them. In this video, I will show you five real SQL interview questions that will make this functions crystal clear, even if you are a beginner. Hi guys, Saloni here, welcome back to the channel. In the last videos, we understood how row number, rank and dense rank work. Now, it's time to apply them in real interview questions. And don't worry, I will explain the logic step by step, not just the query. But before we start, just remember this simple rule. Row number, always unique. Rank, allow duplicates, but skips numbers. And dense rank, allow duplicates, but no gaps. If you understand this, half of your interview is already sorted. So, let's start with the first question. Find employees who are in the top three positions based on the salary. Now, focused on the keyword positions. This means we are talking about ranking, not just top three values. Now, ask yourself one important question. What if two employees have the same salary? Will they get the same position? So, the answer is yes. And what happened to the next position? So, it will get skipped because here we are talking about the positions. So here, we need a function that allows duplicates and skips ranks. That's exactly what rank function does. So, here is our employee table where salaries are sorted in descending order. Now, let's understand the query carefully. So, according to the question, we want top three employees position based on the salary. So, we will use the rank window function over the salary column where the salaries are sorted in descending order. So, we have assigned a rank to each row. Since we cannot directly filter window functions in the same query, we will write an outer query. So, in outer query, we will filter the records which are returned by the inner query using where clause. So, here we have written where rank is less than or equal to three. So, it will select only those rows where rank is less than or equal to three. Now, let's execute this query and see the output. So, here you can see the highest salary gets rank one. Now, next two employees have the same salary, that's why their rank is two. Now, the next rank will become four. And in our query, the condition is rank must be less than or equal to three. That's why we are getting top three positions including duplicates. So the key takeaway is, understand the keyword position. And that directly tells you to use the rank window function. And what would happen if we use row number here? So, think and drop your answer in the comment box. Now, let's see our next question. Which is, find the nth highest salary from the employee table. At first, this looks simple. But this is where most people make mistakes. So, what does nth highest mean? It means we are again ranking salaries. Now, imagine this. If we will use rank window function, then ranks will be like one, two, two, and four. Notice something. Here rank three is missing. So, what if interviewer ask you for the third highest salary? Then you won't get it correctly using the rank window function. So, that means we need a function where ranking does not skip numbers. That's why we will use dense rank. So, in this query, dense rank will assign ranks based on the salary sorted in descending order. Then we will filter the results using where clause. So, if interviewer ask for the third highest salary, then the value of n will be three. And for the second highest salary, it will be two. And so on. And this query is a favorite interview trap. They want to see if you really understand rank or dense rank. Now, let's execute this query. So, this is the correct output. 62,000 is the third highest salary in the employee table. Now, let's increase the difficulty by finding the employee who have the highest salary in each department. So guys, in previous question, we ranked the entire table. But now, ranking should happen inside each department. So, first we will divide department into groups. And in SQL, we do this using partition by clause. Partition by means create separate groups and apply ranking inside each group. And if you want to know more about the partition by clause, then this video is for you. And you will find its link in the description box. Now, inside each department, we want top salary. But what if two employees have the same salary? Should we include both? So, the answer is yes. Both should be included. Now, ask yourself, do we care about the rank gaps here? No, because we are only picking first rank. And it actually doesn't matter whether we use rank or dense rank. But why? Let's see. So, here, even if we have duplicate values, when we filter rank equal to one, both function will give you the same results. So, remember, for the rank equal to one, rank and dense rank, both the function will give you same output. But in interview, I prefer using rank function because we are talking about the position. So, in our query, we will group the department using partition by clause where salaries are sorted in descending order. And then rank window function will assign ranks to each group. And in last, the outer query will fetch only those rows where rank is equal to one. Now, let's see the output. So, here we have top salary of all the departments. So guys, this type of questions are small tricks interviewer use to see if you panic here. But if you understand the logic, then you can easily handle such questions. Now, we will solve a different type of question in which we have to find the latest order for each customer. Here, we are not ranking anymore. We are picking the most recent order. So here, for each customer, we want only one row, which is the latest one. So, do we need to handle duplicate data here? No. So, we will use row number because it always gives unique rows. So, here is our order table. So, what we will do is, we will group the customer ID using partition by clause, and in each partition, orders are sorted in descending order by the order date. And row number will assign unique rank to each order. So, we will pick only those order where rank is equal to one. So, let's execute this query. So, here we have all the latest order for each customer. So, remember, whenever you need exactly one record per group, then row number is usually the best choice. Now, it's time to solve our final question to find the top five artists based on the concert revenue. So, again we are ranking, similar to the question one. But revenue can be same for multiple artists. And they can also share same rank. And rank should not be skipped because here we want proper five artists. So, we will use dense rank to solve this question. And here is our table. So, in the solution, we will rank the revenue which are sorted in descending order. And then we will fetch only those artists where rank is less than or equal to five. So now, let's execute this query. So, here are our top five artists based on the revenue. So, whenever you see a question which involves ranking or position, then always ask yourself these two questions. Are duplicate possible? And should rank skip? And if you remember this logic, then you will never get confused again. Now, what if our last question was like, find artists who are in the top five position based on the revenue. Then here we will use rank window function because the keyword position changes everything. So, always remember this logics. And if this video helped you understand window function in a practical way, make sure to subscribe. And let me know in the comment box what topic I should cover in the next video. Till then, keep watching Code Era and thank you so much for watching.