LeetCode Hard 615 "Average Salary: Departments VS Company" Amazon Interview SQL Question Explanation

[@Karthikmahi007]

with cte as( select e.department_id,s.amount,date_format(pay_date,'%Y-%m') as monthe from employee e join salary s on e.employee_id=s.employee_id), cte2 as( select *,round(avg(amount) over (partition by monthe)) as avg_salary from cte) select department_id,monthe, (case when sum(amount)=avg_salary then 'Same' when sum(amount)>avg_salary then 'higher' else 'Lower' end) as comparison from cte2 group by department_id,monthe; I have tried this on my own, hope it helps :)

[@karthikr4185]

Great thanks for sharing nice and easy solution

[@playwithvihaan7601]

Good job.

[@karangohil4558]

can you solve some of the stratascratch question too, it might be helpful..

[@EverydayDataScience]

Sure Karan, let me finish all 200 of Leetcode and we can move on to Stratascratch next.

[@rayyanamir8560]

More medium problems please.... :)

[@EverydayDataScience]

Sure Rayyan, more videos of easy medium and hard will be coming regularly.

[@minren3260]

Why use left join