select surname,avg_audit,auditor_staff_id,staff_name from( select substr(staff_name,1,1) surname, count(1)/(count(distinct auditor_staff_id)*31) avg_audit form audit_log join staff_info on audit_log.auditor_staff_id=staff_info.staff_id where from_unixtime(star_time,'%m')=7 group by surname) t1 join ( select surname,staff_name,auditor_staff_id from( select surname,staff_name,auditor_staff_id, rank() over( partition by surname order by audit_count desc ) ranking from( select substr(first(staff_name),1,1) surname,first(staff_name),auditor_staff_id,count(1) audit_count from audit_log join staff_info on audit_log.auditor_staff_id=staff_info.staff_id where from_unixtime(star_time,'%m')=7 group by auditor_staff_id) t1) t2 where ranking =1 ) t2 using(surname)