SELECT h.name AS homeroom_name, COUNT(sh.id) AS total_students, (SUM(CASE WHEN sh.is_eses = 1 THEN 1 ELSE 0 END) / COUNT(sh.id)) * 100 AS eses_percentage FROM shieldtech_admission.homerooms h LEFT JOIN shieldtech_admission.student_homerooms sh ON h.id = sh.homeroom_id WHERE h.school_year = YEAR(CURDATE()) GROUP BY h.id, h.name ORDER BY total_students DESC;
Top 10 Guru dengan Beban Kerja Terbanyak
Beban mengajar berdasarkan jumlah mata pelajaran unik dan tugas wali kelas.
ID Guru
Nama Guru
Jumlah Mapel Unik
Wali Kelas
Tidak ada data.
SELECT td.employee_number, ep.fullname AS teacher_name, COUNT(DISTINCT tdsas.subject_id) AS unique_subjects, COUNT(DISTINCT tdh.homeroom_id) AS homeroom_assignments FROM shieldtech_hcm.teacher_distributions td JOIN shieldtech_hcm.employee_profiles ep ON td.employee_number = ep.employee_number LEFT JOIN shieldtech_hcm.teacher_distribution_subject_areas tdsa ON td.id = tdsa.teacher_distribution_id LEFT JOIN shieldtech_hcm.teacher_distribution_subject_area_subjects tdsas ON tdsa.id = tdsas.teacher_distribution_subject_area_id LEFT JOIN shieldtech_hcm.teacher_distribution_homerooms tdh ON td.id = tdh.teacher_distribution_id WHERE td.school_year = YEAR(CURDATE()) GROUP BY td.employee_number, ep.fullname ORDER BY unique_subjects DESC, homeroom_assignments DESC LIMIT 10;
Rata-rata Masa Kerja Guru per Unit (Tahun)
Menunjukkan tingkat retensi guru di setiap unit sekolah.
SELECT u.short_name as unit, AVG(DATEDIFF(COALESCE(ep.resigned_date, CURDATE()), ep.join_date) / 365) AS avg_tenure_years FROM shieldtech_hcm.employee_profiles ep JOIN shieldtech_common.units u ON ep.unit_id = u.id WHERE ep.join_date IS NOT NULL GROUP BY u.short_name ORDER BY avg_tenure_years DESC;