Deloitte SQL Interview Question 2024 | Find the top 3 highest-paid employees in each department

[@NafisAnsari-vr2xq]

Great Video, Just went through your channel and the idea of tackling interview questions like this is great. Thanks, you just got a sub💯

[@ಅಜಯಕನ್ನಡಿಗ-ಪ4ಫ]

thank you sir for explaining step by step😊😊

[@RAHULEDITZS]

MY ANSWER FOR 1ST QUE AND TQ BRO select distinct(EE.Salary),concat(EE.FirstName,'',EE.LastName) as Full_name,DD.DepartmentName FROM department DD Join employee EE on DD.DepartmentID=EE.DepartmentID group by concat(EE.FirstName,'',EE.LastName),DD.DepartmentName,EE.Salary order by DD.DepartmentName, EE.Salary DESC LIMIT 9;

[@skilltechath0n]

@RAHULEDITZS Great! Keep learning :)

[@varmakvm4812]

Hi, In first question while finding the top 3 employees within each department, dense_rank() would be appropriate one because it will handle tie values without skipping the ranking rather than row_number IMO. Thank you for the constant motivation through SQL Questions ! Looking for more ...

[@skilltechath0n]

Yes, definitely if the interviewer asked us to provide the same rank for duplicate values. Thanks for mentioning it 👍

[@parthchauhan9305]

On the contrary, using dense_rank may give us more than 3 values (if there are ties), rather we only need to find top 3 highest paid employees (3 rows), row_number will always ensure that we get only 3 rows in the result set.

[@sambasivaraonelluri]

MY answer 2 qst :SELECT AVG(salary) AS avg_salary_hired_last_5_years FROM employee_data WHERE hire_year BETWEEN 2019 AND 2024;

[@skilltechath0n]

@sambasivaraonelluri Nice. Keep practicing :)

[@sambasivaraonelluri]

1 st question answer: WITH RankedEmployees AS ( SELECT employee_id, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employee_data ) SELECT employee_id, salary, department FROM RankedEmployees WHERE rank

[@skilltechath0n]

@sambasivaraonelluri Great! Keep learning :)