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

# 每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score
select 
    exam_id 
    ,count(distinct uid) uv
    ,round(avg(score),1) avg_score
from (
    select 
        t1.exam_id
        ,t1.uid
        ,t1.score
        # ,row_number() over(partition by uid order by submit_time) ranking
    from exam_record t1 
    inner join examination_info t2 
    on t1.exam_id = t2.exam_id
    inner join user_info t3
    on t1.uid = t3.uid 
    where t2.tag = 'SQL'
    and t3.level > 5
) a 
# where ranking = 1
group by exam_id
order by count(distinct uid) desc ,round(avg(score),1)

补充一下题目没有表述完全的要求：1.平均分取1位小数 2.如果一个人完成了SQL测试两次，那么uv算1，但两次结果都参与平均分计算（如果题目变为不需要参与计算，那么使用我注释掉的窗口函数即可）