题解 | #0级用户高难度试卷的平均用时和平均得分#

with t1 as (
select uid, score, duration, submit_time,start_time
from user_info 
join exam_record using(uid)
join examination_info using(exam_id)
where level = 0 and difficulty = 'hard'
),
t2 as(
select uid, case when submit_time is null then 0
when submit_time is not null then score end as score_1,
case when submit_time is null then duration
when submit_time is not null then timestampdiff(minute, start_time, submit_time) end as time_took
from t1)
select uid, round(avg(score_1),0) as avg_score, round(avg(time_took),1) as avg_time_took
from t2 
group by uid

问题分解

第一步 找出level是0 ，并且高难度

第二步，用case when 来返回未完成试卷的时间和分数

第三步，查询用group by