题解 | 统计各个部门平均薪资

with salary as(
select staff_id
, normal_salary-dock_salary sum_salary
from salary_tb
where (normal_salary-dock_salary) between 4000 and 30000
)

select department
, avg(sum_salary) avg_salary
from staff_tb
left join salary
on staff_tb.staff_id = salary.staff_id
group by  1
order by 2 desc