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

with avg_table as (
    select 
        c1.user_id,
        round(avg(rating),2) as average_rating
    from content_reviews c1 join course_completions c2 using(user_id)
    where c1.course_id= 'DS-102' and completion_date between '2025-03-01' and '2025-03-31'
    group by user_id
),
reviews_table as(
    select 
        user_id,
        date(max(review_date)) as latest_review_date
    from content_reviews
    where user_id in (select user_id from avg_table)
    group by user_id
)
select 
    user_id,
    average_rating,
    if(average_rating>=4.0,'优质反馈学员','普通反馈学员') as feedback_type,
    latest_review_date
from avg_table join reviews_table using(user_id)
order by (case when feedback_type='优质反馈学员' then 1 else 0 end),average_rating desc,user_id;