209. Cost Efficiency by Specialty

SELECT d.specialty, COUNT(DISTINCT a.patient_id) AS total_patients, ROUND(AVG(JULIANDAY(a.discharge_date) - JULIANDAY(a.admission_date)), 2) AS avg_length_of_stay, ROUND(AVG(a.admission_cost / NULLIF(JULIANDAY(a.discharge_date) - JULIANDAY(a.admission_date), 0)), 2) AS avg_cost_per_day, RANK() OVER (ORDER BY AVG(a.admission_cost / NULLIF(JULIANDAY(a.discharge_date) - JULIANDAY(a.admission_date), 0))) AS efficiency_rank
FROM doctors d
JOIN admissions a ON d.doctor_id = a.attending_doctor_id
WHERE a.discharge_date IS NOT NULL
GROUP BY d.specialty
HAVING COUNT(DISTINCT a.patient_id) >= 10
ORDER BY avg_cost_per_day;