题解 | #试卷发布当天作答人数和平均分#

with cte_get_uid as (
    select exam_id,uid,submit_time,score
    from user_info
    join exam_record using(uid)
    join examination_info using(exam_id)
    where level>5 and tag='SQL' and date(submit_time) = date(release_time)
)
# 使用with子查询获取符合条件的基本数据。感觉题目说的不清楚，因为是现根据SQL类别筛选之后要展示所有类型的试卷的数据，所以必须现在子查询里面筛选出所有需要的数据
select exam_id,
count(distinct uid) as uv,
round(avg(score),1) as avg_score
# 对已知数据聚合处理
from cte_get_uid
group by exam_id
order by uv desc,avg_score asc
# 排序