题解 | 在线教育平台活跃学员课程评价分析

with latest_review as (
    select  
        user_id,
        date(max(review_date)) as latest_review_date
    from content_reviews
    group by user_id
)
select
    t3.user_id,
    average_rating,
    feedback_type,
    latest_review_date
from (
    select
        t1.user_id,
        round(avg(rating), 2)as average_rating,
        (
            case
                when avg(rating) >= 4.0 then '优质反馈学员'
                else '普通反馈学员'
            end
        ) as feedback_type
    from content_reviews t1
    left join course_completions t2 on t1.user_id=t2.user_id and t1.course_id=t2.course_id
    where t1.user_id in (
        select user_id
        from course_completions
        where course_id= 'DS-102' and date_format(completion_date, '%Y-%m')='2025-03'
    ) and t1.course_id= 'DS-102'
    group by t1.user_id
)as t3
left join latest_review t4 on t3.user_id=t4.user_id
order by convert(feedback_type using gbk) asc, average_rating desc, t3.user_id asc

查询结果请先按feedback_type升序排列（即 "普通反馈学员" 在前）

如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

order by convert(feedback_type using gbk) asc

但此时我们发现并不能按照拼音首字母进行排序，因此字符集采用的是 utf8，需要先对字段进行转码然后排序：

order by convert(feedback_type using gbk) asc