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

with a1 as (
    select user_id,
    date_format(max(review_date),'%Y-%m-%d') as latest_review_date
    from content_reviews
    group by user_id)

select a.user_id,
round(avg(rating),2) as average_rating,
case when avg(rating)>=4 then '优质反馈学员' else '普通反馈学员' end as feedback_type,
max(latest_review_date) as latest_review_date
from course_completions a 
left join content_reviews b 
on a.user_id=b.user_id 
and a.course_id=b.course_id
left join a1
on a.user_id=a1.user_id
where a.course_id='DS-102'
and date_format(completion_date,'%Y-%m')='2025-03'
group by a.user_id
order by feedback_type desc,average_rating desc,a.user_id