Find the second highest salary in each department from the employees table, excluding any department with fewer than 2 employees.
๐ ๐ฒ: Challenge accepted!
SELECT
department,
MAX(salary) AS second_highest_salary
FROM (
SELECT
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees
) ranked
WHERE rn = 2
GROUP BY department;
I used a subquery with ROW_NUMBER() window function partitioned by department to rank salaries in descending order within each department. The outer query then filters for rank 2 (second highest) and groups to get distinct departments. This demonstrates mastery of window functions, which are essential for advanced analytics and ranking problems.
๐ง๐ถ๐ฝ ๐ณ๐ผ๐ฟ ๐ฆ๐ค๐ ๐๐ผ๐ฏ ๐ฆ๐ฒ๐ฒ๐ธ๐ฒ๐ฟ๐:
Window functions like ROW_NUMBER(), RANK(), and DENSE_RANK() unlock complex ranking and analyticsโpractice them daily to ace behavioral and technical rounds!
React with โค๏ธ for more