题解 | #所有用户对高难度题目的刷题数#

select 
t1.device_id,
convert(sum(if(difficult_level='hard',1,0)),signed) as question_cnt
from
user_profile t1
left join 
question_practice_detail t2
on t1.device_id =t2.device_id 
left join question_detail t3
on t2.question_id =t3.question_id 
# where t3.difficult_level ='hard'
group by t1.device_id
order by question_cnt asc

这题的难点在于，需要统计没有做过的用户，所以不能将difficult_level放在where过滤条件里面，需要在统计的时候进行判断。