rado

select EMPLOYEE_LEVEL,COUNT(employee_id) as Employee_Count
FROM
(select war.warehouse_name,
       upper(dep.department_name) || ' (' || dep.department_id || '-' || substr(loc.postal_code,1,3) || ')' Department,

       case  when round((sysdate-emp.hire_date)/365) <17 then 'Middle level'
       when round((sysdate-emp.hire_date)/365) between 17 and 22 then 'Senior'
       else 'Expert'
       end Employee_Level,emp.employee_id

from departments dep
join oe.warehouses war
on dep.location_id = war.location_id
join locations loc
on dep.location_id = loc.location_id
join employees emp
on emp.department_id = dep.department_id
where loc.city !=  war.warehouse_name
)
group by EMPLOYEE_LEVEL;