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.
Solution to TSQL Beginners Challenge 1
with second as (select EmployeeID,EmployeeName,Department, Salary,
RANK() over(Partition by Department order by salary) as Rank from EX1)
select EmployeeID,EmployeeName,Department, Salary from second where RANK=2