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

-- 重点1：限定条件->8月份，根据年月日对表格筛选
-- 重点2：每个人练习题目的数量，和回答正确数量
-- 重点3: 8月份没有练习的用户，要返回0:
--          
--          
--          返回结果的处理可以考虑if或者case语句。
# 错误解法
# select
#     t1.device_id,
#     t1.university,
#     t3.question_cnt,
#     case
#         when t3.question_cnt is not null then t3.question_cnt
#         else 0
#     end as question_cnt
#     case
#         when t2.right_question_cnt is not null then t2.right_question_cnt
#         else 0
#     end as right_question_cnt
        
# from user_profile as t1
# left join
# (
#     # 获取8月份参与答题的用户，答题正确的数量
#     select device_id,count(result) as right_question_cnt
#     from question_practice_detail
#     where YEAR(date)=2021 and MONTH(date)=8 and result in ("right")
#     group by device_id
# ) as t2
# on t1.device_id=t2.device_id
# left join
# (
#     # 获取8月份参与答题的用户，答题总数
#     select device_id,count(question_id) as question_cnt
#     from question_practice_detail
#     where YEAR(date)=2021 and MONTH(date)=8
#     group by device_id
# ) as t3
# on t3.device_id=t1.device_id
# where university="复旦大学"

select
    up.device_id,
    up.university,
    count(question_id) as question_cnt,
    sum(if(qpd.result="right",1,0)) as right_question_cnt
from user_profile as up
left join question_practice_detail as qpd
on qpd.device_id=up.device_id and month(qpd.date)=8
where up.university="复旦大学"
group by up.device_id;