字节-数据分析师题-2022-05-29，求大牛爆破！

select category_name,sum(if(status=1,1,0))/sum(if(status>0,1,0)) pass_rate from audit_log join category_dict on audit_log.object_category=category_dict.category group by object_category select auditor_staff_id,staff_name,department_name from( select auditor_staff_id,avg(creat_time-star_time) avg_time from from audit_log group by auditor_staff_id order by avg_time limit 5) t1 join staff_info on audit_log.auditor_staff_id=staff_info.staff_id order by avg_time desc SELECT m,first(department_name),avg_audit, (avg_audit-last_mon)/last_mon growth_rate  from( select *, lag(avg_audit,1) over() last_mon  from( select from_unixtime(star_time,'%m&(30713)#39;) m,department_name count(1)/count(distinct auditor_staff_id) avg_audit form audit_log join staff_info on audit_log.auditor_staff_id=staff_info.staff_id group by m,department_id having m in(7,8)) t1) t2

学到了，感谢分享啊

后面那个题用pandas循环遍历就行了，比较简单。

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)