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

WITH filtered_student_202503 AS(
    SELECT user_id, course_id, completion_date
    FROM course_completions
    WHERE course_id = 'DS-102' AND completion_date >= '2025-03-01' AND completion_date <  '2025-04-01'
),
avg_rating AS (
    SELECT cr.user_id, ROUND(AVG(cr.rating), 2) AS average_rating
    FROM content_reviews cr JOIN filtered_student_202503 fs USING(user_id)
    WHERE cr.course_id = 'DS-102'
    GROUP BY cr.user_id
),
latest_review AS (
    SELECT user_id, DATE(MAX(review_date)) AS latest_review_date
    FROM content_reviews
    GROUP BY user_id
)
SELECT fs.user_id, ar.average_rating,
    CASE
        WHEN ar.average_rating >= 4.0 THEN '优质反馈学员'
        ELSE '普通反馈学员'
    END AS feedback_type,
    lr.latest_review_date
FROM filtered_student_202503 fs 
JOIN avg_rating ar ON fs.user_id = ar.user_id
JOIN latest_review lr ON fs.user_id = lr.user_id
ORDER BY
    CASE
        WHEN feedback_type = '普通反馈学员' THEN 1
        WHEN feedback_type = '优质反馈学员' THEN 2
    END,
    average_rating DESC,
    user_id ASC;