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

with a as
(select cc.user_id,round(avg(rating),2) average_rating,if(round(avg(rating),2)>=4.0,'优质反馈学员','普通反馈学员') feedback_type
from course_completions cc 
join content_reviews cr using(user_id,course_id)
where cc.course_id='DS-102' and date_format(completion_date,'%Y%m')='202503'
group by 1),
b as
(select user_id,date_format(max(review_date),'%Y-%m-%d') latest_review_date
from content_reviews cr
group by user_id)
select user_id,average_rating,feedback_type,latest_review_date
from a 
join b using(user_id)
order by feedback_type desc,average_rating desc,user_id