题解 | #统计每个学校各难度的用户平均刷题数#

方法一：

select t2.university,
t3.difficult_level,
round(count(t1.device_id)/count(distinct t1.device_id),4) as avg_answer_cnt
from question_practice_detail t1 
left join user_profile t2 
on t1.device_id=t2.device_id
left join question_detail t3 on t3.question_id=t1.question_id
group by t2.university,t3.difficult_level;

方法二：

with a1 as(select t1.device_id,t1.question_id,t2.university,t2.answer_cnt from question_practice_detail t1 left join user_profile t2 on t1.device_id=t2.device_id)
select a1.university,t3.difficult_level,round(count(a1.device_id)/count(distinct a1.device_id),4) as avg_answer_cnt from a1 left join question_detail t3 on a1.question_id=t3.question_id
group by a1.university,t3.difficult_level;