The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.
ROW_NUMBER() and CTE
;WITH T AS
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Ord
FROM T t1
FROM T t2
WHERE t1.Department = t2.Department
AND t2.Ord = 2