题解 | #满足条件的用户的试卷完成数和题目练习数#
满足条件的用户的试卷完成数和题目练习数
http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
with target_user as
(#先是筛选特定的用户
select
b.uid,
avg(b.score)
from examination_info a
left join exam_record b on a.exam_id=b.exam_id
inner join user_info c on b.uid=c.uid and c.level=7
where a.tag='SQL' and a.difficulty='hard'
group by b.uid
having avg(b.score)>80
)
#接下来连接三个表，因为exam_record和practice_record 的主键是id,
#所以计算次数时可以用dsitinct id
select
c.uid,
count(distinct a.id) as exam_cnt,
count(distinct b.id) as question_cnt
from target_user c
left join exam_record a on a.uid =c.uid and year(a.submit_time)=2021
left join practice_record b on b.uid=c.uid and year(b.submit_time)=2021
group by c.uid
order by exam_cnt,question_cnt desc
投递华为等公司10个岗位 >
查看3道真题和解析
合合信息公司福利 235人发布