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

with user as (
    select distinct(device_id) d_id, university
    from user_profile where university='复旦大学'
),
que_list as (
    select device_id, count(*) question_cnt, sum(if(result='right',1,0)) right_question_cnt
    from question_practice_detail
    where month(date)=8 and year(date)=2021
    group by device_id
)
select user.d_id device_id, user.university, if(que_list.question_cnt is not null,que_list.question_cnt,0) question_cnt, if(que_list.right_question_cnt is not null,que_list.right_question_cnt,0) right_question_cnt
from user 
left join que_list on user.d_id =que_list.device_id and user.university = '复旦大学'