Optimize SQL for Large Datasets

BambooHR's platform tracks employee time off across every tenant in a single time_off_requests table with roughly 80 million rows. A backend engineer wrote the query below to report approved days taken this year per department for active employees, along with each employee's manager, but it is far too slow to run in the reporting job's time budget.

-- time_off_requests has an index on (employee_id, request_date)
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id BIGINT PRIMARY KEY,
    full_name VARCHAR(200),
    department_id INT REFERENCES departments(department_id),
    manager_id BIGINT,
    employment_status VARCHAR(20)
);

CREATE TABLE time_off_requests (
    request_id BIGINT PRIMARY KEY,
    employee_id BIGINT REFERENCES employees(employee_id),
    request_date TIMESTAMP,
    status VARCHAR(20),
    days_requested NUMERIC(4,1)
);

CREATE TABLE holidays (
    holiday_id INT PRIMARY KEY,
    holiday_date DATE,
    region VARCHAR(50)
);

SELECT
    d.department_name,
    e.full_name,
    (SELECT full_name FROM employees m WHERE m.employee_id = e.manager_id) AS manager_name,
    SUM(t.days_requested) AS days_taken
FROM employees e, departments d, time_off_requests t, holidays h
WHERE d.department_id = e.department_id
  AND t.employee_id = e.employee_id
  AND t.status = 'approved'
  AND YEAR(t.request_date) = 2024
  AND UPPER(e.employment_status) = 'ACTIVE'
GROUP BY d.department_name, e.full_name, e.manager_id
ORDER BY days_taken DESC;

Explain what is wrong with this query, both for correctness and for performance, and describe exactly how you would fix each issue.