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

with
    date_table as (
        select
            t2.user_id,
            date_format(Max(t2.review_date), "%Y-%m-%d") AS latest_review_date
        from
            course_completions t1
            left join content_reviews t2 on t1.user_id = t2.user_id
            and t1.course_id = t2.course_id
        group by
            t2.user_id
    ),
    rating as (
        SELECT
            t1.user_id,
            round(AVG(t2.rating), 2) AS average_rating,
            (
                case
                    when avg(t2.rating) >= 4 then "优质反馈学员"
                    else "普通反馈学员"
                end
            ) as feedback_type
        FROM
            course_completions t1
            LEFT JOIN content_reviews t2 ON t1.user_id = t2.user_id
            AND t1.course_id = t2.course_id
        WHERE
            t1.course_id = 'DS-102'
            AND DATE_FORMAT(t1.completion_date, '%Y%m') = '202503'
        GROUP BY
            t1.user_id
        order by
            CASE
                WHEN feedback_type = '普通反馈学员' THEN 0
                ELSE 1
            END ASC,
            average_rating desc,
            t1.user_id
    )
select
    t3.user_id,
    average_rating,
    feedback_type,
    latest_review_date
from
    rating t3
    left join date_table t4 on t3.user_id = t4.user_id