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

WITH TargetUsers AS (
    -- 第一步：筛选出2025年3月完成 DS-102 的目标学员
    SELECT DISTINCT user_id
    FROM course_completions
    WHERE course_id = 'DS-102'
      AND left(completion_date,7) >= '2025-03' 
      
),
DS102_Ratings AS (
    -- 第二步：计算学员对 DS-102 这一门课的平均分
    SELECT user_id, AVG(rating) as avg_r
    FROM content_reviews
    WHERE course_id = 'DS-102'
    GROUP BY user_id
),
GlobalLatestReview AS (
    -- 第三步：计算学员在全球（不限课程）的最近评价日期
    SELECT user_id, MAX(review_date) as max_date
    FROM content_reviews
    GROUP BY user_id
)
-- 第四步：汇总结果并进行分类排序
SELECT 
    t.user_id,
    ROUND(COALESCE(r.avg_r, 0), 2) AS average_rating,
    CASE 
        WHEN COALESCE(r.avg_r, 0) >= 4.0 THEN '优质反馈学员'
        ELSE '普通反馈学员' 
    END AS feedback_type,
    DATE_FORMAT(l.max_date, '%Y-%m-%d') AS latest_review_date
FROM TargetUsers t
 JOIN DS102_Ratings r ON t.user_id = r.user_id
 JOIN GlobalLatestReview l ON t.user_id = l.user_id
ORDER BY 
    feedback_type desc, 
    average_rating DESC, 
    user_id ASC;

主要难点在于需要对齐颗粒度，颗粒度不同的需要cte分步骤解决，不限课程的最近评价日期和学员对 DS-102 这一门课的平均分颗粒度不一致，需要分别进行计算