题解 | 查找在职员工自入职以来的薪水涨幅情况

# 员工自入职以来的薪水涨幅情况
with
t1 as(
    select
        emp_no,
        salary as max_salary
    from
        salaries
    where
        to_date='9999-01-01'
),
t2 as(
    select
        emp_no,
        salary as min_salary
    from
        employees
        right join t1 using(emp_no)
        left join salaries using(emp_no)
    where
        hire_date=from_date
),
t3 as(
    select emp_no, max_salary as salary from t1
    union
    select emp_no, min_salary as salary from t2
)
select
    emp_no,
    max(salary)-min(salary) as growth
from
    t3
group by
    emp_no
order by
    growth