题解 | #平均活跃天数和月活人数#
满足条件的用户的试卷完成数和题目练习数
http://www.nowcoder.com/practice/5c03f761b36046649ee71f05e1ceecbf
#1. 先找出满足SQL,hard, level为7 的用户id，作为筛选userid的条件
#2. 定位满足1 的uid后，计算出完成的考试数，注意需要满足2021年的约束，否则计算出的结果不对
#3. 左连接练习问题表，计算出完成问题，输出
SELECT b.uid,b.exam_cnt,count(p.question_id)as question_cnt
FROM
(
select r1.uid, count(r1.submit_time)as exam_cnt
from exam_record r1
where r1.uid in
(select r.uid from exam_record r
left join examination_info i
on r.exam_id = i.exam_id
left join user_info u
on r.uid = u.uid and YEAR(r.submit_time) = 2021
where i.difficulty = 'hard'and i.tag ='SQL'
and u.level = 7
group by 1
having avg(r.score) > '80'
)
and year(submit_time) = 2021
group by r1.uid
)b
left join practice_record p
on b.uid = p.uid and YEAR(p.submit_time) = 2021
group by b.uid
order by 2 , 3 desc
数据库刷题题解 文章被收录于专栏
数据分析数据库题目练习题解
莉莉丝游戏公司福利 699人发布