----L&T SQL Interview Problem | Print Highest and Lowest Salary Employees in Each Department---------

---SQL INTERVIEW QUESTION--------

USE [SSIS]
create table employee 
(
emp_name varchar(10),
dep_id int,
salary int
);
delete from employee;
insert into employee values 
('Siva',1,30000),('Ravi',2,40000),('Prasad',1,50000),('Sai',2,20000)
SELECT * FROM employee

---Sample Table Creation--------------------

---Solution------------------------

WITH max_min_salary AS(
SELECT *,max(salary)OVER(PARTITION BY dep_id)AS max_salary,
min(salary)OVER(PARTITION BY dep_id)AS min_salary
FROM employee)

--SELECT * FROM max_min_salary


SELECT dep_id,max(CASE WHEN salary=max_salary THEN emp_name END) AS max_salary_employee,
 max(CASE WHEN salary=min_salary THEN emp_name END) AS min_salary_employee
FROM max_min_salary
GROUP BY dep_id

------------------END OF THE SOLUTION--------------