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

# course_completions、content_reviews
# user_id
# 在2025年3月完成了 “数据科学进阶” (course_id= 'DS-102') 课程的所有学员
# 请计算他们对该课程 (DS-102) 所有评价的平均星级，并找出他们最近一次提交评价的日期（不限课程）。同时，根据平均星级将学员分为两类：平均星级大于等于4.0的为 "优质反馈学员"，否则为 "普通反馈学员"。
with user_r as (
select user_id,
       # 2\ 计算对该课程 (DS-102) 所有评价的平均星级
       round(avg(rating),2) as average_rating
from content_reviews 
where user_id in (
# 1\ 找出2025年3月完成了 “数据科学进阶” (course_id= 'DS-102') 课程的所有学员
        select user_id
        from course_completions
        where course_id= 'DS-102' and 
        completion_date  between '2025-03-01' and '2025-03-31') and 
        course_id= 'DS-102'
group by user_id),
max_date as (
    select user_id,max(review_date) as latest_review_date
           #row_number() (partition by user_id order by review_date desc ) as rk
    from content_reviews 
    group by user_id
)

select u.user_id,
       u.average_rating,
       (case when u.average_rating >= 4 then '优质反馈学员' else '普通反馈学员' end) as feedback_type,
       date(m.latest_review_date) as latest_review_date
from user_r u
join max_date m on m.user_id =u.user_id
order by  convert(feedback_type using gbk) asc,u.average_rating desc,u.user_id asc