# $1468. 计算税后工资
# https://leetcode-cn.com/problems/calculate-salaries/

# SQL架构
Create table If Not Exists Salaries (company_id int, employee_id int, employee_name varchar(13), salary int);
Truncate table Salaries;
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '1', 'Tony', '2000');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '2', 'Pronub', '21300');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('1', '3', 'Tyrrox', '10800');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '1', 'Pam', '300');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '7', 'Bassem', '450');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('2', '9', 'Hermione', '700');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '7', 'Bocaben', '100');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '2', 'Ognjen', '2200');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '13', 'Nyancat', '3300');
insert into Salaries (company_id, employee_id, employee_name, salary) values ('3', '15', 'Morninngcat', '7777');

# Write your MySQL query statement below
select
    s.company_id,
    s.employee_id,
    s.employee_name,
    round(s.salary *(1 - companytaxrate.taxrate)) as salary
from
    Salaries s
    join (
        select
            company_id,
            case
                when max(salary) < 1000 then 0
                when max(salary) between 1000
                and 10000 then 0.24
                when max(salary) > 10000 then 0.49
            end as taxrate
        from
            Salaries
        group by
            company_id
    ) companytaxrate on s.company_id = companytaxrate.company_id;

# clean-up
drop table Salaries;