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

with tab as(
# 统计在2025年3月完成了102课程的所有学员的id
select user_id,course_id,completion_date
from course_completions 
where year(completion_date) = 2025 and month(completion_date) = 3 and course_id = 'DS-102'
)
# 依据平均评分标记反馈类型并调整顺序
select user_id,average_rating,case when average_rating >=4.0 then '优质反馈学员' else '普通反馈学员' end as feedback_type,latest_review_date
from(
# 统计学员对102课程的平均评分
select user_id,course_id,round(avg(rating),2) as average_rating, date(latest_date) as latest_review_date
from(
# 统计每个学员最近一次提交评价的日期，不限课程
select cr.review_id,t.user_id,cr.course_id,cr.rating,cr.review_date,max(cr.review_date) over(partition by user_id) as latest_date
from tab as t
left join content_reviews as cr on t.user_id = cr.user_id
) as temp
group by user_id,course_id,latest_date
having course_id = 'DS-102'
) as temp2
order by feedback_type desc,average_rating desc,user_id asc

题目说明有误，应该是按照反馈类型降序排序