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

with t1 as(
    select u1.uid, e1.exam_id,score,tag from exam_record e1
    left join examination_info e2
    on e1.exam_id =e2.exam_id
    left join user_info u1
    on u1.uid =e1.uid
    where tag = 'SQL' and level>5 and date(release_time)=date(submit_time)
)

select exam_id ,count(distinct uid)  uv,round(avg(score),1) avg_score from t1
group by exam_id
order by uv desc,avg_score asc