-- Complete JOIN, Subquery & Aggregate Example
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    (SELECT MAX(salary) FROM employees WHERE department_id = d.department_id) AS max_dept_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE d.department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(*) > 5
)
GROUP BY d.department_id, d.department_name
HAVING AVG(e.salary) > 50000
ORDER BY avg_salary DESC;