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

select
    user_id,
    round(sum(if(course_id = 'DS-102', rating, 0)) / nullif(sum(if(course_id = 'DS-102', 1, 0)), 0), 2) as average_rating,
    case 
        when avg(rating) >= 4.0 then '优质反馈学员'
        else "普通反馈学员" end as feedback_type,
    date(max(review_date)) as latest_review_date
from
    content_reviews
where
    user_id in (
        select
            user_id
        from
            course_completions
        where
            course_id = 'DS-102' and completion_date like '%2025-03%'
    )
group by
    user_id
order by
    if(feedback_type='普通反馈学员', 0, 1), average_rating desc, user_id;