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

select
    c.user_id,
    round(avg(cr.rating),2) as average_rating,
    case when avg(cr.rating)>=4 then '优质反馈学员' else '普通反馈学员' end as feedback_type,
    date(t.max_date) as latest_review_date
from
    course_completions c 
join
    content_reviews cr on c.user_id = cr.user_id
join(
    select
        user_id,
        max(review_date) as max_date
    from
        content_reviews
    group by user_id
) t on t.user_id=c.user_id
where
    c.course_id= 'DS-102' and cr.course_id= 'DS-102'and year(c.completion_date)=2025 and month(c.completion_date)= 03
group by
    c.user_id
order by 
    feedback_type desc,average_rating desc,c.user_id