题解 | #某乎问答高质量的回答中用户属于各级别的数量#

-- 回答字数大于等于100字的认为是高质量回答，请你统计某乎问答高质量的回答中用户属于1-2级、3-4级、5-6级的数量分别是多少，按数量降序排列

-- 1  筛选高质量回答
# select
#     ant.author_id,
#     case when author_level in (1,2) then '1-2级' 
#         when author_level in (3,4) then '3-4级'
#         else '5-6级' end level
# from answer_tb ant left join author_tb aut on ant.author_id=aut.author_id
# where char_len>=100

-- 2  对第1步结果进行group by,count和order by
# select 
#     level as level_cut,
#     count(*) as num
# from t1
# group by level
# order by num desc

-- 合并
select 
    level as level_cut,
    count(*) as num
from (
    select
        ant.author_id,
        case when author_level in (1,2) then '1-2级' 
            when author_level in (3,4) then '3-4级'
            else '5-6级' end level
    from answer_tb ant left join author_tb aut on ant.author_id=aut.author_id
    where char_len>=100
)t1
group by level
order by num desc