DB ESM - HW1.2

SELECT
  in_t.mon as MONTH,
  in_t.dep as DEPARTMENT,
  TO_CHAR(MAX(in_t.salary), 'fm$999,999,990.00') as MAX_SALARY,
  TO_CHAR(MIN(in_t.salary), 'fm$999,999,990.00') as MIN_SALARY,
  avg(case when in_t.job_id like '%MAN' then in_t.salary else null end) MNG_AVG_SALARY,
  count(distinct in_t.department_id) as DEP_COUNT
FROM
  (SELECT
      TO_CHAR(e.hire_date, 'Mon') AS mon,
      (CASE WHEN e.department_id = 50 THEN 'Shipping' ELSE 'Other' END) as dep,
      e.job_id,
      e.department_id,
      e.salary
    FROM hr.employees e) in_t
GROUP BY
  in_t.dep,
  in_t.mon
 HAVING
  count(case when in_t.job_id like '%MAN' then 1 else null end) > 0
ORDER BY
  in_t.dep DESC,
  TO_DATE(in_t.mon, 'Mon');