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

WITH tem AS (
    SELECT
        u.device_id AS did,
        u.university,
        q.question_id AS qid,
        q.result AS qr
    FROM
        user_profile u
        LEFT JOIN question_practice_detail q
            ON u.device_id = q.device_id
            AND q.date BETWEEN '2021-08-01' AND '2021-08-31'
)
SELECT
    did AS device_id,
    university,
    COUNT(qid) AS question_cnt,
    SUM(CASE WHEN qr = 'right' THEN 1 ELSE 0 END) AS right_question_cnt
FROM tem
WHERE university = '复旦大学'
GROUP BY did, university
ORDER BY did;