Very Famous SQL Interview Question | Department Highest Salary
Вставка
- Опубліковано 11 вер 2024
- 𝐖𝐚𝐧𝐭 𝐭𝐨 𝐌𝐚𝐬𝐭𝐞𝐫 𝐒𝐐𝐋? 𝐋𝐞𝐚𝐫𝐧 𝐒𝐐𝐋 𝐭𝐡𝐞 𝐫𝐢𝐠𝐡𝐭 𝐰𝐚𝐲 𝐭𝐡𝐫𝐨𝐮𝐠𝐡 𝐭𝐡𝐞 𝐦𝐨𝐬𝐭 𝐬𝐨𝐮𝐠𝐡𝐭 𝐚𝐟𝐭𝐞𝐫 𝐜𝐨𝐮𝐫𝐬𝐞 - 𝐒𝐐𝐋 𝐂𝐡𝐚𝐦𝐩𝐢𝐨𝐧𝐬 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫!
"𝐀 8 𝐰𝐞𝐞𝐤 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 𝐝𝐞𝐬𝐢𝐠𝐧𝐞𝐝 𝐭𝐨 𝐡𝐞𝐥𝐩 𝐲𝐨𝐮 𝐜𝐫𝐚𝐜𝐤 𝐭𝐡𝐞 𝐢𝐧𝐭𝐞𝐫𝐯𝐢𝐞𝐰𝐬 𝐨𝐟 𝐭𝐨𝐩 𝐩𝐫𝐨𝐝𝐮𝐜𝐭 𝐛𝐚𝐬𝐞𝐝 𝐜𝐨𝐦𝐩𝐚𝐧𝐢𝐞𝐬 𝐛𝐲 𝐝𝐞𝐯𝐞𝐥𝐨𝐩𝐢𝐧𝐠 𝐚 𝐭𝐡𝐨𝐮𝐠𝐡𝐭 𝐩𝐫𝐨𝐜𝐞𝐬𝐬 𝐚𝐧𝐝 𝐚𝐧 𝐚𝐩𝐩𝐫𝐨𝐚𝐜𝐡 𝐭𝐨 𝐬𝐨𝐥𝐯𝐞 𝐚𝐧 𝐮𝐧𝐬𝐞𝐞𝐧 𝐏𝐫𝐨𝐛𝐥𝐞𝐦."
𝐇𝐞𝐫𝐞 𝐢𝐬 𝐡𝐨𝐰 𝐲𝐨𝐮 𝐜𝐚𝐧 𝐫𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐟𝐨𝐫 𝐭𝐡𝐞 𝐏𝐫𝐨𝐠𝐫𝐚𝐦 -
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLINR
𝐑𝐞𝐠𝐢𝐬𝐭𝐫𝐚𝐭𝐢𝐨𝐧 𝐋𝐢𝐧𝐤 (𝐂𝐨𝐮𝐫𝐬𝐞 𝐀𝐜𝐜𝐞𝐬𝐬 𝐟𝐫𝐨𝐦 𝐨𝐮𝐭𝐬𝐢𝐝𝐞 𝐈𝐧𝐝𝐢𝐚) : rzp.io/l/SQLUSD
𝐖𝐚𝐧𝐭 𝐭𝐨 𝐥𝐞𝐚𝐫𝐧 𝐁𝐢𝐠 𝐃𝐚𝐭𝐚 𝐛𝐲 𝐒𝐮𝐦𝐢𝐭 𝐒𝐢𝐫?
𝐜𝐡𝐞𝐜𝐤𝐨𝐮𝐭 𝐭𝐡𝐞 𝐛𝐢𝐠 𝐝𝐚𝐭𝐚 𝐜𝐨𝐮𝐫𝐬𝐞 𝐝𝐞𝐭𝐚𝐢𝐥𝐬
𝐖𝐞𝐛𝐬𝐢𝐭𝐞 : trendytech.in/...
I have trained over 20,000+ professionals in the field of Data Engineering in the last 5 years.
Very Famous SQL Interview Question | Department Highest Salary
In this video we will solve Problem number 184 on LeetCode
Department Highest Salary
This is a medium complexity SQL Interview Question
Do Like, Comment & Subscribe ..
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
🔅Sumit LinkedIn - / bigdatabysumit
🔅Sumit Instagram - / bigdatabysumit
In this video, we will solve SQL question on LeetCode. LeetCode is an excellent platform for practicing SQL Queries. The SQL Questions on LeetCode are framed pretty similar to how they are asked on SQL Interviews hence solving SQL questions on LeetCode can give you a good hands on experience on solving real world SQL questions.
#sql #interview #leetcode
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_lc5
select Department, Employee, Salary
from (
select d.name as Department,e.name as Employee,e.salary,
rank() over (partition by departmentid order by salary desc) rnk
from Employee e
join department d on e.departmentid=d.id ) x
where x.rnk=1
It is more optimised
use dense_rank over rank as it will allow more than 1 highest salary in each dept
Finished the whole playlist. Really helpful. I've learned How to approach any SQL problem.
'where' clause with more than one column, along with 'in' clause is a combination i am learning first time, very interesting, gaining more confidence in sql questions now :)
Thanks for the wonderful solution Sir. I have tried to solve in a different way.
select Department,Employee,salary from (select Department,Employee,salary,dense_rank() over(partition by Department order by salary desc) as denserank from (select e.name as Employee,salary,d.name as Department from Employee e join Department d on e.departmentId = d.id) temp) temp1 where denserank=1;
Please bring few more problem statements showing the usage of full outer join & Cartesian product as well
More Useful Sir. Kindly upload these kinds of problems continuously.
Please upload more videos to this playlist
sir waiting for next session
Very soon
Thank you for all the videos! can you please please do a video about leetcode sql question 2153-number of passengers in each bus 2?Please.Thank you
Thanks @ helpful !!!
Glad that it was helpfull
Select * from (Select employee.department, employee.name employee, salary, dense_rank() over(partition by employee.department order by salary desc) as r from employee, department where employee. Id = department. Id) where r=1;
Will this works?
Yes. It works
There are some changes I have made and after that the query gives expected results:
select Department, Employee, Salary from (
Select Department.name as Department, employee.name as Employee, Salary, dense_rank() over(partition by employee.departmentId order by salary desc) as r from employee join Department on
employee.departmentId = Department.id
) as r where r = 1
@@dikshagupta2795 Thanks
why are we using department id in sub-query . why do we need to include it , can't we just use max(salary) only
No, we can't use only max(salary) in the sub-query, because that will return only one maximum salary, but here we want to retrieve max salaries for both of the departments!
Alternate way:
select Department,Employee,Salary from (select d.name as Department, e.name as Employee, dense_rank() over
(partition by d.name order by salary desc) as Sal, Salary from Employee e inner join Department d
on e.departmentId=d.id)x
where x.Sal=1
Sir I saw all the previous 5 video. This problem I paused and solved the problem on my own. Thanks. But I used Partition method involving 3 select statements.
SELECT temp1.Department,temp1.name as Employee,temp1.Salary
FROM(
SELECT *,
RANK() OVER(PARTITION by temp.departmentId ORDER BY temp.Salary DESC) AS rnk
FROM(
SELECT e.*,d.name as "Department" FROM Employee e
INNER JOIN Department d
ON e.departmentId=d.id) temp
) temp1
WHERE temp1.rnk=1;
for this highest salary rank will work but if you want to get nth highest salary you need to use dense rank...correct me if I'm wrong
select temp.name as Department,temp.employeename as Employee ,temp.salary as Salary from
(select d.name,e.name as employeename,salary,dense_rank() over ( partition by d.name order by salary desc ) as dr from employee e join department d on e.departmentId=d.id) temp where temp.dr=1
Window functions are not always cost effective
Hello Sir,
If we are using sql server then what will be the alternative for "WHERE (departmentId, salary)" as we can't use two columns in WHERE in sql server
use window functions
Can anybody help with identifying what is wrong in this query select d.name as Department,e1.name as Empolyee ,max(salary) as Salary from employee e1,department d where e1.departmentId = d.id group by d.name ;
This will not work because here in each department for each employee there will be only one row for salary...your query gives result department, employee name and the highest salary of him, here there is only one row so basically no aggregation will happen considering there is only one row for each employee in each department. the logic is you need to first partition the data department wise then order the data by salary in descending order for each department and get the first row in each partitioned data set. you can use either Row number or dense rank to achieve this....check once.
pls solve prob 627
Noted :) will cover that in the upcoming sessions
@@sumitmittal07 thank you sir
@@sukanyaiyer2671 you need to use update with case statement in that problem
Looks complicated querry
WITH Solution as
(SELECT Employee.name as Employee, Employee.salary as Sal ,Department.name as Department, DENSE_RANK() OVER(PARTITION BY Department.name ORDER BY Employee.salary DESC) as re
FROM Employee
INNER JOIN Department ON Employee.departmentID=Department.id)
SELECT Department, Employee ,Sal as Salary
FROM Solution
WHERE re=1;
#This one also worked using Dense rank window function