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

select device_id,university, sum(cnt_total) as question_cnt, sum(cnt_total*cnt_right) as
right_question_cnt from (select user_profile.device_id,university,case when date >= '2021-08-01' and date <='2021-08-31' then 1 else 0 end as cnt_total,
case when result ='right' then 1 else 0 end as cnt_right
 from user_profile left join question_practice_detail
on user_profile.device_id = question_practice_detail.device_id where university='复旦大学') c group by device_id,university