Post #2696

𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝗲𝗿: You have 2 minutes to solve this SQL query. 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