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

with t1 as(
    select cr.user_id,
        cr.course_id,
        avg(rating) as average_rating,
        case when avg(rating)>=4 then '优质反馈学员' else '普通反馈学员' end as feedback_type
    from content_reviews cr
    join course_completions cc on cr.user_id=cc.user_id and cr.course_id=cc.course_id
    where cc.course_id='DS-102' and date(completion_date) between '2025-03-01' and '2025-03-31'
    group by cr.user_id,cr.course_id
)

select t1.user_id,
    round(average_rating,2) as average_rating,
    feedback_type,
    date_format(max(review_date),'%Y-%m-%d') as latest_review_date
from t1
join content_reviews cr on t1.user_id=cr.user_id
group by t1.user_id,feedback_type
order by feedback_type desc,average_rating desc,t1.user_id