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

# 1、查询完成了 “数据科学进阶” (course_id= 'DS-102') 课程的所有学员2025-03
with t1 as (
select distinct user_id from course_completions
where completion_date between '2025-03-01' and '2025-03-31'
and course_id = 'DS-102'
),

# 2、计算平均星级
t2 as (
    select 
    cr.user_id,
    round(avg(rating), 2) as average_rating
    from content_reviews cr
    inner join t1
    on t1.user_id = cr.user_id
    where cr.course_id = 'DS-102'
    group by 1
)

# 3、合并所有信息
select
t2.user_id,
average_rating,
case when average_rating >= 4.0 then '优质反馈学员' 
else '普通反馈学员'
end as feedback_type,
t3.latest_review_date
from t2
left join(
    # 获取最新评价日期
    select user_id,
    date_format(max(review_date), '%Y-%m-%d') as latest_review_date
    from content_reviews
    group by user_id # 有max窗口函数，所以需要group
) t3
on t2.user_id = t3.user_id
order by 
case when feedback_type = '优质反馈学员' then 2
when feedback_type = '普通反馈学员' then 1 end asc, 
average_rating desc, 
t2.user_id asc
;