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

select
    case 
        when t1.author_level = 1 or t1.author_level = 2 then '1-2级'
        when t1.author_level = 3 or t1.author_level = 4 then '3-4级'
        else '5-6级'
    end as level_cut,
    sum(
        case 
            when t2.char_len >= 100 then 1
            else 0
        end
    ) as num
from author_tb t1
inner join (
    select 
        author_id,
        char_len
    from answer_tb
    where char_len >= 100
) t2
on t1.author_id = t2.author_id
group by level_cut
order by num desc