select 
gender,university,
count(device_id) user_num,
round(sum(active_days_within_30)/count(device_id),1)   avg_active_day,
round(sum(question_cnt)/count(device_id),1) avg_question_cnt
from user_profile
group by university,gender

先根据学校分桶然后再根据性别分桶，利用聚合函数count统计用户数，利用聚合函数sum分别统计30天内总活跃天数以及总发帖数除以用户数即为平均30天内活跃天数和平均发帖数。