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

with t1 as (
    select a.uid, a.exam_id, a.score, b.tag
    from exam_record as a
    left join examination_info as b
    on a.exam_id = b.exam_id
),
t2 as (
    select d.uid, d.exam_id, d.score, c.level
    from user_info as c
    right join (
        select uid, exam_id, score
        from t1
        where tag = 'SQL' and score IS NOT NULL
    ) as d
    on c.uid = d.uid
),
t3 as (
    select uid, exam_id, score
    from t2
    where level > 5
)
select exam_id, count(distinct uid) as uv, round(AVG(score), 1) as avg_score
from t3
group by exam_id
order by uv desc, avg_score;