题解 | #平均绩点大于3.6且总人数大于2的学校学生情况#
平均绩点大于3.6且总人数大于2的学校里学生情况
https://www.nowcoder.com/practice/5efb7d06f3e944ec81c40193b22c4b5f
select a.university, count(a.device_id) as total_cnt, sum(if(a.gender='male',1 ,0)) as male_cnt, sum(if(a.gender='female',1 ,0)) as female_cnt, sum(if(a.age<23,1,0)) as lt23_cnt from( select device_id, gender, age, university, avg(gpa) over(partition by university) as avg_gpa from user_profile ) as a where a.avg_gpa>3.6 group by university having total_cnt>2