题解 | #统计复旦用户8月练题情况#
统计复旦用户8月练题情况
http://www.nowcoder.com/practice/53235096538a456b9220fce120c062b3
知识点:left join
select
a.device_id,university,count(case when result is not null then result else null end) as question_cnt,
count(case when result='right' then result when result is null then null end) as right_question_cnt
from
(
select device_id,university
from user_profile
where university='复旦大学'
)a
left join
(
select device_id,
question_id,
result
from question_practice_detail
where SUBSTR(date,1,7)='2021-08'
)b
on a.device_id=b.device_id
group by a.device_id,university