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

select
cc.user_id
,round( sum( if( cr.course_id = 'DS-102', rating, 0) ) / sum( if( cr.course_id = 'DS-102', 1, 0) ), 2 ) as average_rating
,if( round( sum( if( cr.course_id = 'DS-102', rating, 0) ) / sum( if( cr.course_id = 'DS-102', 1, 0) ), 2 ) >= 4.0, '优质反馈学员', '普通反馈学员' ) as feedback_type
,date( max( review_date ) ) as latest_review_date
from course_completions cc join content_reviews cr on cc.user_id = cr.user_id 
where cc.course_id = 'DS-102' 
and completion_date >= '2025-03-01' and completion_date < '2025-04-01'
group by user_id
order by feedback_type desc, average_rating desc, user_id asc;

一个查询直接搞定