American Express Interview | SQL Interview Question: Calculate Salary Difference Between Departments

[@aravindareddy7267]

Can we use max(salary) over (partition by department_id)

[@Thedata.techclub]

Yes, it will work.

[@HARSHRAJ-gp6ve]

with cte as(
select salary,department FROM db_employee JOIN db_dept ON db_employee.department_id=db_dept.id
),cte1 as(
select department,MAX(salary) as dept_sal FROM cte GROUP BY department HAVING department IN
('marketing','engineering')
),cte2 as(
select cte1.*,LEAD(dept_sal)OVER() as second_dept_sal FROM cte1
)
select (dept_sal-second_dept_sal) as diff FROM cte2 where (dept_sal-second_dept_sal) is not null;