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

select up.device_id,
       up.university,
       count(*)                                          as question_cnt,
       sum(case when result = 'right' then 1 else 0 end) as right_question_cnt
from question_practice_detail qpd
         left join (select *
                    from user_profile up
                    where up.university = '复旦大学') up on qpd.device_id = up.device_id
where date_format(qpd.date, '%Y-%m') = '2021-08'
  and up.university = '复旦大学'
group by up.device_id
union
select up.device_id, up.university, 0 as question_cnt, 0 as right_question_cnt
from user_profile up
where up.device_id not in (select up.device_id
                           from user_profile up
                                    left join question_practice_detail qpd on up.device_id = qpd.device_id
                           where up.university = '复旦大学'
                             and date_format(qpd.date, '%Y-%m') = '2021-08'
                           group by up.device_id)
  and up.university = '复旦大学';