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

WITH t1 AS (
    SELECT *
    FROM content_reviews
    WHERE course_id = 'DS-102'
)
SELECT 
    t1.user_id,
    ROUND(AVG(t1.rating), 2) AS average_rating,
    CASE 
        WHEN AVG(t1.rating) >= 4.0 THEN '优质反馈学员' 
        ELSE '普通反馈学员' 
    END AS feedback_type,
    DATE_FORMAT(MAX(cr_all.review_date), '%Y-%m-%d') AS latest_review_date
FROM t1
JOIN course_completions c ON t1.user_id = c.user_id
LEFT JOIN content_reviews cr_all ON t1.user_id = cr_all.user_id
WHERE c.course_id = 'DS-102' 
  AND c.completion_date BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY t1.user_id
ORDER BY feedback_type DESC, average_rating DESC, t1.user_id ASC;

SELECT user_id, ROUND(avg_r, 2) AS average_rating, CASE WHEN avg_r >= 4.0 THEN '优质反馈学员' ELSE '普通反馈学员' END AS feedback_type, DATE_FORMAT(latest_date, '%Y-%m-%d') AS latest_review_date FROM ( SELECT t.user_id, COALESCE(AVG(CASE WHEN cr.course_id = 'DS-102' THEN cr.rating END), 0) AS avg_r, MAX(cr.review_date) AS latest_date FROM course_completions t JOIN content_reviews cr ON t.user_id = cr.user_id WHERE t.course_id = 'DS-102' AND t.completion_date BETWEEN '2025-03-01' AND '2025-03-31' GROUP BY t.user_id ) tmp ORDER BY feedback_type DESC, average_rating DESC, user_id ASC

精减版