Changes between Version 5 and Version 6 of OtherDevelopments

Timestamp:

06/15/26 00:35:39 (31 hours ago)

Author:

221296

Comment:

--

OtherDevelopments

@@ -16 +16 @@
 
 === Scenario 1: Yearly Analysis of Overall Platform Activity and Student Success ===
+
+{{{
+WITH course_year_performance AS (
+    SELECT
+        EXTRACT(YEAR FROM qa.attempt_date) AS year,
+        c.course_id,
+        ROUND(AVG(qa.score), 2) AS final_grade_per_course
+    FROM quiz_attempt qa
+    JOIN quiz q
+        ON q.quiz_id = qa.quiz_id
+    JOIN lesson l
+        ON l.lesson_id = q.lesson_id
+    JOIN module m
+        ON m.module_id = l.module_id
+    JOIN course c
+        ON c.course_id = m.course_id
+    GROUP BY
+        EXTRACT(YEAR FROM qa.attempt_date),
+        c.course_id
+),
+year_course_summary AS (
+    SELECT
+        year,
+        ROUND(AVG(final_grade_per_course), 2) AS final_year_success
+    FROM course_year_performance
+    GROUP BY year
+)
+SELECT
+    EXTRACT(YEAR FROM e.enroll_date) AS year,
+    COUNT(DISTINCT c.instructor_id) AS total_instructor_activity,
+    COUNT(DISTINCT e.user_id) AS total_students,
+    COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
+    COUNT(DISTINCT qa.attempt_id) AS total_attempts,
+    COUNT(DISTINCT CASE
+        WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
+    END) AS completed_enrollments,
+    COUNT(DISTINCT cert.certificate_id) AS total_certificates,
+    ROUND(AVG(e.progress_percentage), 2) AS avg_student_success_within_courses,
+    ROUND(AVG(qa.score), 2) AS avg_final_grade_per_course, ycs.final_year_success
+FROM enrollment e
+JOIN course c
+    ON c.course_id = e.course_id
+LEFT JOIN certificate cert
+    ON cert.enrollment_id = e.enrollment_id
+LEFT JOIN module m
+    ON m.course_id = c.course_id
+LEFT JOIN lesson l
+    ON l.module_id = m.module_id
+LEFT JOIN quiz q
+    ON q.lesson_id = l.lesson_id
+LEFT JOIN quiz_attempt qa
+    ON qa.quiz_id = q.quiz_id
+    AND qa.user_id = e.user_id
+LEFT JOIN year_course_summary ycs
+    ON ycs.year = EXTRACT(YEAR FROM e.enroll_date)
+GROUP BY
+    EXTRACT(YEAR FROM e.enroll_date), ycs.final_year_success
+ORDER BY year;
+
+}}}
 
 This query provides a yearly analytical overview of the platform.