Debug and Optimize SQL

MITRE tracks open vulnerability findings against internal systems for its own security program. You are handed the query below, printed on paper with no IDE, and asked to say what it is meant to do, find the errors in it, and describe how you would optimize it.

-- findings has an index on (system_id, detected_date)
CREATE TABLE systems (
    system_id INT PRIMARY KEY,
    system_name VARCHAR(200),
    criticality VARCHAR(20)
);

CREATE TABLE vulnerabilities (
    cve_id VARCHAR(20) PRIMARY KEY,
    cvss_score NUMERIC(3,1),
    published_date DATE
);

CREATE TABLE findings (
    finding_id BIGINT PRIMARY KEY,
    system_id INT REFERENCES systems(system_id),
    cve_id VARCHAR(20) REFERENCES vulnerabilities(cve_id),
    detected_date TIMESTAMP,
    status VARCHAR(20)
);

SELECT
    s.system_name,
    COUNT(f.finding_id) AS open_findings,
    AVG(v.cvss_score) AS avg_severity
FROM systems s
LEFT JOIN findings f ON f.system_id = s.system_id
JOIN vulnerabilities v ON v.cve_id = f.cve_id
WHERE f.status = 'open'
  AND s.criticality = 'high'
  AND YEAR(f.detected_date) >= 2023
GROUP BY s.system_name
HAVING COUNT(f.finding_id) > 0
ORDER BY avg_severity DESC;

Explain what this query is meant to do, identify every error you can find in it, and describe how you would optimize it for a findings table with tens of millions of rows.