Changes between Version 9 and Version 10 of OtherDevelopments

Timestamp:

06/15/26 01:29:40 (32 hours ago)

Author:

221296

Comment:

--

OtherDevelopments

@@ -324 +324 @@
 # After adding indexes, the execution time decreased from 944.483 ms to 171.114 ms. This shows that the added indexes significantly improved the execution of the yearly analysis query. The improvement is especially visible because the query joins multiple tables and processes 20000 quiz_attempt records.
 
-=== Scenario 2: Monthly Analysis of Course Activity, Completions, Instructor Involvement and Student Success ===
+=== Scenario 2: Monthly analysis of course activity, completions, instructor involvement, and student success within a given year ===
+
+{{{
+WITH student_course_month_performance AS (
+    SELECT
+        EXTRACT(YEAR FROM qa.attempt_date) AS year,
+        EXTRACT(MONTH FROM qa.attempt_date) AS month,
+        c.course_id,
+        qa.user_id,
+        ROUND(AVG(qa.score), 2) AS avg_student_score
+    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),
+        EXTRACT(MONTH FROM qa.attempt_date),
+        c.course_id,
+        qa.user_id
+),
+course_month_performance AS (
+    SELECT
+        year,
+        month,
+        course_id,
+        ROUND(AVG(avg_student_score), 2) AS avg_course_success
+    FROM student_course_month_performance
+    GROUP BY
+        year,
+        month,
+        course_id
+),
+month_performance_summary AS (
+    SELECT
+        year,
+        month,
+        ROUND(AVG(avg_course_success), 2) AS final_month_success
+    FROM course_month_performance
+    GROUP BY
+        year,
+        month
+)
+SELECT
+    EXTRACT(YEAR FROM e.enroll_date) AS year,
+    EXTRACT(MONTH FROM e.enroll_date) AS month,
+    COUNT(DISTINCT e.enrollment_id) AS total_enrollments,
+    COUNT(DISTINCT CASE
+        WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id
+    END) AS completed_enrollments,
+    COUNT(DISTINCT c.instructor_id) AS active_instructors,
+    ROUND(AVG(scmp.avg_student_score), 2) AS avg_student_success,
+    ROUND(AVG(cmp.avg_course_success), 2) AS avg_course_success,
+    mps.final_month_success
+FROM enrollment e
+JOIN course c
+    ON c.course_id = e.course_id
+LEFT JOIN student_course_month_performance scmp
+    ON scmp.year = EXTRACT(YEAR FROM e.enroll_date)
+    AND scmp.month = EXTRACT(MONTH FROM e.enroll_date)
+    AND scmp.course_id = e.course_id
+    AND scmp.user_id = e.user_id
+LEFT JOIN course_month_performance cmp
+    ON cmp.year = EXTRACT(YEAR FROM e.enroll_date)
+    AND cmp.month = EXTRACT(MONTH FROM e.enroll_date)
+    AND cmp.course_id = e.course_id
+LEFT JOIN month_performance_summary mps
+    ON mps.year = EXTRACT(YEAR FROM e.enroll_date)
+    AND mps.month = EXTRACT(MONTH FROM e.enroll_date)
+GROUP BY
+    EXTRACT(YEAR FROM e.enroll_date),
+    EXTRACT(MONTH FROM e.enroll_date),
+    mps.final_month_success
+ORDER BY
+    year,
+    month;
+
+}}}
+
 
 This query provides a month-by-month analytical overview of the platform for a selected year.