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

-- 先找到特殊的学员id
with temp as (
    select 
    cc.user_id
    from course_completions cc
    where cc.course_id= 'DS-102'
    and date_format(cc.completion_date , '%Y-%m') = '2025-03'
),

-- 查找计算他们的平均星级
temp1 as (
select 
tp.user_id,
round(avg(case when cr.course_id= 'DS-102' then cr.rating end),2) as average_rating,
case 
    when round(avg(case when cr.course_id= 'DS-102' then cr.rating end),2) >= 4.0 then '优质反馈学员'
     else '普通反馈学员' 
     end as feedback_type,
    date_format(max(cr.review_date),'%Y-%m-%d') as latest_review_date
from temp tp
inner join content_reviews cr
on cr.user_id = tp.user_id
group by tp.user_id
)
select * from temp1
order by feedback_type desc , average_rating desc , user_id asc;