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;

[@ishamajumdar5580]

WITH cte AS ( SELECT e.salary, ROW_NUMBER() OVER(PARTITION BY d.department ORDER BY salary DESC) AS rn FROM employee e JOIN dept d ON e.department_id = d.id WHERE d.department IN ('marketing', 'engineering') ) SELECT ABS(c1.salary - c2.salary) AS Absolute_diff FROM cte c1 JOIN cte c2 ON c1.rn = 1 AND c2.rn = 1 AND c1.salary > c2.salary;