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

WITH base AS(
    SELECT
     a.user_id,
     a.course_id,
     ROUND(AVG(b.rating),2) AS average_rating,
     CASE 
      WHEN ROUND(AVG(b.rating),2)>=4.0 THEN '优质反馈学员'
      ELSE '普通反馈学员' 
    END AS feedback_type
    FROM course_completions a
    JOIN content_reviews b ON a.user_id=b.user_id
    WHERE b.review_date>='2025-03-01' AND b.review_date<'2025-04-01' AND a.course_id='DS-102'
    GROUP BY a.user_id
),
base1 AS(
    SELECT user_id,
           MAX(review_date) as latest_review_date
    FROM content_reviews
    GROUP BY user_id
)
SELECT 
  base.user_id,
  base.average_rating,
  base.feedback_type,
  DATE(base1.latest_review_date) AS latest_review_date
FROM base
JOIN base1 ON base.user_id=base1.user_id
ORDER BY base.feedback_type DESC, base.average_rating DESC, base.user_id ASC