207. Gender-Based Specialty Distribution

SELECT d.specialty, COUNT(DISTINCT a.patient_id) AS total_patients, SUM(CASE WHEN p.gender = 'M' THEN 1 ELSE 0 END) AS male_count, ROUND(SUM(CASE WHEN p.gender = 'M' THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT a.patient_id), 2) AS male_percentage, SUM(CASE WHEN p.gender = 'F' THEN 1 ELSE 0 END) AS female_count, ROUND(SUM(CASE WHEN p.gender = 'F' THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT a.patient_id), 2) AS female_percentage, CASE WHEN SUM(CASE WHEN p.gender = 'M' THEN 1 ELSE 0 END) > SUM(CASE WHEN p.gender = 'F' THEN 1 ELSE 0 END) THEN 'Male' ELSE 'Female' END AS dominant_gender
FROM doctors d
JOIN admissions a ON d.doctor_id = a.attending_doctor_id
JOIN patients p ON a.patient_id = p.patient_id
GROUP BY d.specialty
HAVING MAX(male_percentage, female_percentage) > 60
ORDER BY total_patients DESC;