LeetCode 1468: Calculate Salaries [SQL]

Solution and walkthrough of leetcode database problem 1468: Calculate Salaries. I'm using MySQL but this solution should work in any SQL dialect such as PostgreSQL SQL Server, etc.

Playlists:

[haowang]

Neat approach as always.👍 I have been watching almost all your videos from easy to medium and hard ones. As a self-learner, my SQL skills is improving, and I am really appreciate about your effort of making those helpful videos and wish to see more.

[sanskarsuman]

with cte1 as (select max(salary) as max_slary, company_id from salaries group by company_id)
, cte 2 as (select max_slary from cte1 join salaries on a.company_id = b.company_id)

select company_id, employee_id, employee_name,
case when max_slary> 10000 then salary-salary*0.49
when max_salary in (1000, 10000) then salary-salary*.24
when max_salary <1000 then salary end as salary
from cte2

[sushantdragoon]

/*Just in case if anyone wanted to see with CASE statement. */

SELECT company_id, employee_id, employee_name,
CASE
WHEN max_salary < 1000 THEN salary
WHEN max_salary BETWEEN 1000 AND 10000 THEN ROUND(salary - (0.24 * salary))
ELSE ROUND(salary - (0.49 * salary))
END AS salary
FROM
(SELECT company_id, employee_id, employee_name, salary, MAX(salary) OVER (PARTITION BY company_id) as max_salary
FROM Salaries) t

[angelmeist]

Can you let me know why this wouldn't work? Thanks!
select company_id, employee_id, employee_name, round(salary*rate, 0) as salary
from salaries
where (company_id, rate) in
(select company_id,
case
when max(salary)>10000 then (1-0.49)
when max(salary) between 1000 and 10000 then (1-0.24)
else (1-0)
end as rate
from salaries
group by company_id)