题解 | 所有用户对高难度题目的刷题数

SELECT 
    a.device_id,
    IFNULL(b.question_cnt,0) question_cnt
FROM user_profile a
LEFT JOIN (
    SELECT t1.device_id,COUNT(t2.question_id) question_cnt
    FROM user_profile t1
    LEFT JOIN question_practice_detail t2
    ON t1.device_id = t2.device_id
    LEFT JOIN question_detail t3
    ON t2.question_id = t3.question_id
    WHERE t3.difficult_level = 'hard'
    GROUP BY t1.device_id
) b
ON a.device_id = b.device_id
GROUP BY a.device_id
order by question_cnt