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

select
  a.device_id,
  a.university,
  coalesce(count(b.question_id),0) as question_cnt,
  coalesce(count(if(b.result = 'right', b.question_id, null)),0) as right_question_cnt
from
  user_profile a
  left join question_practice_detail b on a.device_id = b.device_id
where
  a.university = '复旦大学'
  and (b.date between '2021-08-01' and '2021-08-31' or b.date is null)
group by
  a.device_id,
  a.university

注意：如果没有or b.date is null 这个条件，8月份没有刷过题的用户不会显示。也可以在count里加条件。