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 ...
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.
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;
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
Great Video, Just went through your channel and the idea of tackling interview questions like this is great.
Thanks, you just got a sub💯
thank you sir for explaining step by step😊😊
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 ...
Yes, definitely if the interviewer asked us to provide the same rank for duplicate values.
Thanks for mentioning it 👍
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.
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;
@RAHULEDITZS Great! Keep learning :)
MY answer 2 qst :SELECT AVG(salary) AS avg_salary_hired_last_5_years
FROM employee_data
WHERE hire_year BETWEEN 2019 AND 2024;
@sambasivaraonelluri Nice. Keep practicing :)
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
@sambasivaraonelluri Great! Keep learning :)