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

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