202. Doctor Workload Distribution

WITH specialty_avg AS (
    SELECT d.specialty, AVG(patient_count) AS avg_patients
    FROM (
        SELECT d.doctor_id, d.specialty, COUNT(DISTINCT a.patient_id) AS patient_count
        FROM doctors d
        LEFT JOIN admissions a ON d.doctor_id = a.attending_doctor_id
        GROUP BY d.doctor_id, d.specialty
    ) AS doc_counts
    GROUP BY specialty
)
SELECT d.first_name || ' ' || d.last_name AS doctor_name, d.specialty, COUNT(DISTINCT a.patient_id) AS patient_count, sa.avg_patients AS specialty_avg, ROUND(((COUNT(DISTINCT a.patient_id) - sa.avg_patients) / sa.avg_patients) * 100, 2) AS percent_above_avg
FROM doctors d
JOIN admissions a ON d.doctor_id = a.attending_doctor_id
JOIN specialty_avg sa ON d.specialty = sa.specialty
GROUP BY d.doctor_id, d.first_name, d.last_name, d.specialty, sa.avg_patients
HAVING COUNT(DISTINCT a.patient_id) >= 5 AND COUNT(DISTINCT a.patient_id) > sa.avg_patients * 1.2
ORDER BY percent_above_avg DESC;