题解 | #平均绩点大于3.6且总人数大于2的学校学生情况#

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