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

#先确定数据（from)和条件(where)
select 
    user_profile.device_id as device_id,
    user_profile.university as university,
    count(question_id) as question_cnt,
    sum(
        case 
            when question_practice_detail.result = "right" then 1
            else 0
        end
    ) as right_question_cnt
from user_profile
left join question_practice_detail
on user_profile.device_id = question_practice_detail.device_id
where university = "复旦大学" 
and (month(date) = "08" or month(date) is null)
group by device_id

1.确定where后的条件（不要忘记八月没答题的数据——result有null 值）

2.因为要保留全部复旦学生八月相关数据，采用左连接

3.确定右表包含的数据，将左表与右表左连接，注意连接条件

4.补充目标列，列名称不能模糊，选择count/sum计数