-- 第一步 做出所有人的两个指标
with t as
(
select t.uid,level,sum(start_time is not null and submit_time is null) as incomplete_cnt,
round(sum(start_time is not null and submit_time is null)/count(1),3) as incomplete_rate,
count(exam_id) as num
from user_info t
left join exam_record t1 on t.uid = t1.uid
group by t.uid
)
-- 第二步 冲
select uid,incomplete_cnt,incomplete_rate
from t
where EXISTS (
select uid from t where level=0 and incomplete_cnt>2
) and level=0
union ALL
select uid,incomplete_cnt,incomplete_rate
from t
where not EXISTS (
select uid from t where level=0 and incomplete_cnt>2
) and num>0
order by incomplete_rate