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

select
    case
        when author_level = 1
        or author_level = 2 then '1-2级'
        when author_level = 3
        or author_level = 4 then '3-4级'
        when author_level = 5
        or author_level = 6 then '5-6级'
        else -1
    end as level_cut,
    count(concat (ans.author_id, ans.issue_id)) num
from
    answer_tb ans
    left join author_tb aut on ans.author_id = aut.author_id
where
    char_len >= 100
group by
    case
        when author_level = 1
        or author_level = 2 then '1-2级'
        when author_level = 3
        or author_level = 4 then '3-4级'
        when author_level = 5
        or author_level = 6 then '5-6级'
        else -1
    end
order by num desc

核心：

1. 使用case when给author的等级打上标签，分成1-2级，3-4级，5-6级；

2. answer表是事实表，author表是维度表，维度表左连接到事实表上，根据回答字数筛选出高质量回答；

3. 用打好的等级标签作为聚合依据，聚合答题数量（一个author_id加一个issue_id为一个answer）