205. Specialist Referral Network

SELECT d1.first_name || ' ' || d1.last_name AS doctor1_name, d1.specialty AS doctor1_specialty, d2.first_name || ' ' || d2.last_name AS doctor2_name, d2.specialty AS doctor2_specialty, COUNT(DISTINCT a1.patient_id) AS shared_patients, AVG(a1.admission_cost + a2.admission_cost) AS avg_combined_cost
FROM doctors d1
JOIN admissions a1 ON d1.doctor_id = a1.attending_doctor_id
JOIN admissions a2 ON a1.patient_id = a2.patient_id AND a1.admission_id != a2.admission_id
JOIN doctors d2 ON a2.attending_doctor_id = d2.doctor_id
WHERE d1.doctor_id < d2.doctor_id AND d1.specialty != d2.specialty
GROUP BY d1.doctor_id, d1.first_name, d1.last_name, d1.specialty, d2.doctor_id, d2.first_name, d2.last_name, d2.specialty
HAVING COUNT(DISTINCT a1.patient_id) >= 3
ORDER BY shared_patients DESC;