# $1875. 将工资相同的雇员分组
# https://leetcode.cn/problems/group-employees-of-the-same-salary/

# SQL架构
Create table If Not Exists Employees (employee_id int, name varchar(30), salary int);
Truncate table Employees;
insert into Employees (employee_id, name, salary) values ('2', 'Meir', '3000');
insert into Employees (employee_id, name, salary) values ('3', 'Michael', '3000');
insert into Employees (employee_id, name, salary) values ('7', 'Addilyn', '7400');
insert into Employees (employee_id, name, salary) values ('8', 'Juan', '6100');
insert into Employees (employee_id, name, salary) values ('9', 'Kannon', '7400');

# Write your MySQL query statement below
select
    employee_id,
    name,
    b.salary,
    b.team_id
from
    Employees e
    inner join (
        select
            salary,
            dense_rank() over (
                order by
                    salary
            ) as team_id
        from
            Employees
        group by
            salary
        having
            count(distinct employee_id) > 1
    ) b on e.salary = b.salary
order by
    b.team_id,
    employee_id;

# clean-up
drop table Employees;