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

select 
distinct device_id, university, SUM(CASE WHEN result=0 THEN 0 ELSE 1 END) over(partition by device_id) as question_cnt, SUM(CASE WHEN result=2 THEN 1 ELSE 0 END) over(partition by device_id) as right_question_cnt
from
(
    select  device_id, 
            university, 
            CASE
                WHEN state_question_cnt = "right" THEN 2
                WHEN state_question_cnt = "wrong" THEN 1
                WHEN state_question_cnt = "0" THEN 0
                ELSE 0 
            END AS result
    from
    (
        select   device_id, 
                university,
                ifnull(result, 0) as state_question_cnt

        from 
        (
            select *
            from 
            (
                select up.device_id, university, result, Year(date) as Y, Month(date) as M
                from 
                user_profile as up left join
                question_practice_detail as qpd
                on up.device_id=qpd.device_id
                where up.university="复旦大学"
            ) as t
            where 
            (case when Y=2021 and M=8 then 1 else 0 end) + 
            (case when Y is NULL then 1 else 0 end)
        ) as t1
    ) as t2
) as t3