数据库——SQL21 试卷发布当天作答人数和平均分（牛客网）

-- 每张SQL类别试卷发布后，
-- 当天5级以上的用户作答的人数uv和平均分avg_score，
-- 按人数降序，相同人数的按平均分升序
-- exam_id	 uv 	avg_score
select ei.exam_id,count(distinct uid) uv,round(avg(score),1) avg_score
from examination_info ei join exam_record er on ei.exam_id=er.exam_id
where year(release_time)=year(submit_time)
and score is not null
and ei.exam_id in(select exam_id from examination_info where tag='SQL')
and uid in (select uid from user_info where level>5)
group by ei.exam_id
order by uv desc,avg_score 

做这些题就是先将问题分解，细化成一个个小问题，然后再拼接起来，三表查询，题目答案不唯一，所以可以自由发挥啦。

附题目来源：https://www.nowcoder.com/practice/5b58e89556dc4153a79d8cf8c08ba499?tpId=240&tqId=2183023&ru=/exam/oj&qru=/ta/sql-advanced/question-ranking&sourceUrl=%2Fexam%2Foj%3Ftab%3DSQL%25E7%25AF%2587%26topicId%3D240