WITH
cmem AS (
SELECT course_id, 'Premium' AS member_type
FROM courses
WHERE
(difficulty_level = 'Advanced' AND category = 'Business') OR
(difficulty_level IN ('Intermediate', 'Advanced') AND category = 'Programming')
UNION ALL
SELECT course_id, 'Basic' AS member_type
FROM courses
WHERE
difficulty_level IN ('Intermediate', 'Advanced') AND category = 'Programming'
UNION ALL
SELECT course_id, 'Enterprise' AS member_type
FROM courses
WHERE
difficulty_level = 'Beginner' AND category = 'Programming'
UNION ALL
SELECT course_id, 'Free' AS member_type
FROM courses
WHERE
difficulty_level = 'Beginner' AND category = 'Programming'
),
cmet AS (
SELECT
c.course_id,
AVG(e.progress_percentage) AS avg_prog,
AVG(e.exercises_completed * 1.0 / NULLIF(c.total_lectures, 0)) AS avg_ex_ratio,
AVG(e.time_spent_hours / NULLIF(c.duration_hours, 0)) AS avg_time_ratio
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id
),
escore AS (
SELECT
c.difficulty_level,
c.course_id,
c.course_name,
c.category,
m.member_type,
COALESCE(t.avg_prog, 0) AS avg_prog,
COALESCE(t.avg_ex_ratio, 0) AS avg_ex_ratio,
COALESCE(t.avg_time_ratio, 0) AS avg_time_ratio,
(
COALESCE(t.avg_prog, 0) +
(COALESCE(t.avg_ex_ratio, 0) * 100) +
(COALESCE(t.avg_time_ratio, 0) * 100)
) / 3.0 AS score
FROM cmem m
JOIN courses c ON m.course_id = c.course_id
LEFT JOIN cmet t ON m.course_id = t.course_id
)
SELECT
difficulty_level,
course_id,
course_name,
category,
member_type,
ROUND(avg_prog, 2) AS avg_prog,
ROUND(avg_ex_ratio, 2) AS avg_ex_ratio,
ROUND(avg_time_ratio, 2) AS avg_time_ratio,
ROUND(score, 2) AS engagement_score,
RANK() OVER (
PARTITION BY difficulty_level
ORDER BY score DESC
) AS rank_in_level
FROM escore
ORDER BY
CASE difficulty_level
WHEN 'Advanced' THEN 1
WHEN 'Beginner' THEN 2
WHEN 'Intermediate' THEN 3
END,
course_id ASC,
CASE member_type
WHEN 'Premium' THEN 1
WHEN 'Enterprise' THEN 2
WHEN 'Free' THEN 3
WHEN 'Basic' THEN 4
END;
