/* 25 Write a SQL query to find the manager who is in charge of the most employees
      and their average salary.*/
SELECT CONCAT(m.first_name, ' ', m.last_name) AS manager_name,
       COUNT(e.employee_id)                   AS num_employees,
       ROUND(AVG(m.salary))                   AS employee_avg_salary
FROM employees e
         JOIN employees m ON e.manager_id = m.employee_id
GROUP BY e.manager_id
ORDER BY num_employees DESC
LIMIT 1;


/* 26 Write a SQL query that outputs the names of the employees who have worked on the most projects.*/
WITH project_counts AS (SELECT e.employee_id, e.first_name, e.last_name, COUNT(ep.project_id) AS projects_count
                        FROM employees e
                                 JOIN employees_projects ep ON e.employee_id = ep.employee_id
                        GROUP BY e.employee_id)
SELECT CONCAT(first_name, ' ', last_name) as empoloyee_name, projects_count
FROM project_counts
WHERE projects_count = (SELECT MAX(projects_count) FROM project_counts)
ORDER BY first_name;