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

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

SELECT
    u.device_id,
    u.university,
    SUM(IF(result IS NOT NULL, 1, 0)) AS question_cnt,
    SUM(IF(result = "right", 1, 0)) AS right_question_cnt
FROM
    user_profile u
    LEFT JOIN question_practice_detail q ON u.device_id = q.device_id
    AND MONTH(q.`date`) = "08"
WHERE 
    university = "复旦大学"
GROUP BY
    u.device_id,
    u.university