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

SELECT DISTINCT a.device_id,a.university,
coalesce(count(b.result) OVER (PARTITION BY device_id),0) AS question_cnt,
coalesce(sum(CASE WHEN b.result = 'right' THEN 1 ELSE 0 END) OVER (PARTITION BY device_id),0) AS right_question_cnt
FROM(   
    SELECT device_id,university
    FROM user_profile
    WHERE university = '复旦大学'
) AS a
LEFT JOIN question_practice_detail AS b ON a.device_id = b.device_id AND b.date BETWEEN '2021-08-01' AND '2021-08-31'

好多题解啊，随便看看吧