SELECT
  e.first_name || ' ' || e.last_name AS employee,
  d.dept_name,
  e.salary,
  e.hire_date,
  RANK() OVER (ORDER BY e.salary DESC) AS salary_rank_company,
  RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS salary_rank_dept,
  ROUND(e.salary / AVG(e.salary) OVER (PARTITION BY e.dept_id) * 100 - 100, 1) AS pct_above_dept_avg,
  ROUND(e.salary / AVG(e.salary) OVER () * 100 - 100, 1) AS pct_above_company_avg,
  COUNT(*) OVER (PARTITION BY e.dept_id) AS dept_size
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'active'
ORDER BY salary_rank_company;