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

排序规则有点模糊不清 容易出错

with data_1 as 
(
    select user_id,round(avg(rating),2) average_rating,
    if(round(avg(rating),2)>=4.0,'优质反馈学员','普通反馈学员') feedback_type
    from (
        select cc.user_id,rating,review_date
        from course_completions cc left join content_reviews cr on cc.user_id = cr.user_id
        where completion_date like '2025-03%' and cc.course_id= 'DS-102' and cr.course_id='DS-102'
    ) t1
    group by user_id
),
rk_data as (
    select user_id,date(review_date) latest_review_date 
    from(
        select cc.user_id,rating,review_date,
        rank()over(partition by cc.user_id order by review_date desc) rk 
        from course_completions cc left join content_reviews cr on cc.user_id = cr.user_id
        where completion_date like '2025-03%'
    ) t2
    where t2.rk=1
)
select data_1.user_id,average_rating,feedback_type,latest_review_date
from data_1 left join rk_data on data_1.user_id = rk_data.user_id
order by feedback_type desc,average_rating desc,user_id