题解 | 根据指定记录是否存在输出不同情况

#user_info uid
#exam_record uid+exam_id+start_time

#任意一个0级用户未完成试卷数大于2
#输出每个0级用户的试卷未完成数和未完成率（保留3位小数）
#若不存在这样的用户，则输出所有有作答记录的用户的这两个指标

with t1 as (
    select
    a.uid
    ,level
    ,count(start_time)-count(submit_time) as incomplete_cnt
    ,round((count(start_time)-count(submit_time)) /count(*),3) as incomplete_rate
    ,count(start_time) as num
    from user_info a
    left join exam_record b
    on a.uid = b.uid
    group by 1,2)

select
uid
,incomplete_cnt
,incomplete_rate
from t1
where exists(select uid from t1 where level = 0 and incomplete_cnt >2)
and level = 0

union all

select
uid
,incomplete_cnt
,incomplete_rate
from t1
where not exists(select uid from t1 where level = 0 and incomplete_cnt >2)
and num>0 #有作答记录的用户
order by incomplete_rate ;