题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
https://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
# 复旦大学,每个用户,8月,总题数和回答正确的题目数
# 8月无练习，0
# 答题数，sum(case when 是8月 then 1 else 0 end)
# 答对个数：sum(case when result='right' then 1 else 0 end)
select u.device_id,u.university,ifnull(q.question_cnt,0),ifnull(q.right_question_cnt,0)
from user_profile u
left join
(select device_id,
sum(case when date_format(date,'%Y-%m')='2021-08' then 1
else 0 end) question_cnt,
sum(case when result='right' then 1 else 0 end) right_question_cnt
from question_practice_detail
group by device_id
) q using(device_id)
where u.university='复旦大学'
