题解 | #满足条件的用户的试卷完成数和题目练习数#

-- 1.统计用户不同试卷平均分及总的试卷完成数

with t as(
select ui.uid,ui.level,er.exam_id,er.submit_time,er.score from user_info ui JOIN exam_record er on ui.uid = er.uid)

SELECT pp.*,pr.question_cnt FROM 
(select *,
ROUND(avg(score) over (PARTITION by uid,tag,difficulty),1) as uid_tag_difficulty,
sum(IF(score>=60,1,0)) over (PARTITION by uid) as exam_cnt
from (select t.*,ei.tag,ei.difficulty from t join examination_info ei on t.exam_id = ei.exam_id

-- 2.统计用户2021练习记录数

select *, sum(IF(score>0,1,0)) over (PARTITION by uid) as question_cnt from  practice_record pr 
where DATE_FORMAT(submit_time,'%Y') = 2021

--3.统计7级用户在2021年 hard难度的sql且平均值大于80 ，结果按试卷完成数升序，作答记录数降序。

where tag= 'SQL'
AND difficulty = 'hard'
AND uid_tag_difficulty>80
AND level = 7
AND DATE_FORMAT(submit_time,'%Y') = 2021
ORDER BY exam_cnt ASC,question_cnt DESC;

完整代码：

with t as(
select ui.uid,ui.level,er.exam_id,er.submit_time,er.score from user_info ui JOIN exam_record er on ui.uid = er.uid)

select DISTINCT(q.uid),q.exam_cnt,
IFNULL(q.question_cnt,0) question_cnt from(
SELECT pp.*,pr.question_cnt FROM 
(select *,
ROUND(avg(score) over (PARTITION by uid,tag,difficulty),1) as uid_tag_difficulty,
sum(IF(score>=60,1,0)) over (PARTITION by uid) as exam_cnt
from (select t.*,ei.tag,ei.difficulty from t join examination_info ei on t.exam_id = ei.exam_id) p
) pp
left JOIN (select *, sum(IF(score>0,1,0)) over (PARTITION by uid) as question_cnt from  practice_record pr 
where DATE_FORMAT(submit_time,'%Y') = 2021) pr on pp.uid = pr.uid) q

where tag= 'SQL'
AND difficulty = 'hard'
AND uid_tag_difficulty>80
AND level = 7
AND DATE_FORMAT(submit_time,'%Y') = 2021
ORDER BY exam_cnt ASC,question_cnt DESC;