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

with tiaojian as (
select 
uid
from user_info
where
level>5
),tiaojian1 as (
select 
exam_id,
release_time
from examination_info
where
tag="SQL"
)


select 
t1.exam_id,
count(distinct t.uid) as uv,
round(avg(score),1) as avg_score
from exam_record ed inner join tiaojian t on 
ed.uid=t.uid inner join tiaojian1 t1 on t1.exam_id=ed.exam_id
where
date(ed.submit_time)=date(release_time)
group by exam_id
order by uv desc,avg_score asc