题解 | #统计复旦用户8月练题情况#

with t1 as(select
    device_id,
    count(question_id) as question_cnt,
    sum(case when result='right' then 1 else 0 end) as right_question_cnt
from question_practice_detail
where date_format(`date`,'%Y-%m')='2021-08'
group by device_id
)
select
    t2.device_id,
    university,
    ifnull(t1.question_cnt,0),
    ifnull(t1.right_question_cnt,0)
from user_profile as t2
left join t1
on t1.device_id=t2.device_id
where university='复旦大学'