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

WITH completed_users AS (
    -- 2025年3月完成课程 DS-102 的学员（去重）
    SELECT DISTINCT user_id
    FROM course_completions
    WHERE course_id = 'DS-102'
      AND completion_date BETWEEN '2025-03-01' AND '2025-03-31'
),
course_avg_rating AS (
    -- 计算这些学员对课程 DS-102 的平均星级及分类
    SELECT 
        cr.user_id,
        ROUND(AVG(cr.rating), 2) AS average_rating,
        CASE 
            WHEN AVG(cr.rating) >= 4.0 THEN '优质反馈学员'
            ELSE '普通反馈学员'
        END AS feedback_type
    FROM content_reviews cr
    JOIN completed_users cu ON cr.user_id = cu.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
    WHERE user_id IN (SELECT user_id FROM completed_users)
    GROUP BY user_id
)
SELECT 
    ca.user_id,
    ca.average_rating,
    ca.feedback_type,
    lr.latest_review_date
FROM course_avg_rating ca
JOIN latest_review lr ON ca.user_id = lr.user_id
ORDER BY 
    FIELD(feedback_type, '普通反馈学员', '优质反馈学员'),          -- 普通反馈学员在前
    ca.average_rating DESC,
    ca.user_id ASC;