Changes between Version 6 and Version 7 of ComplexReports

Timestamp:

03/30/26 11:06:03 (12 days ago)

Author:

221296

Comment:

--

ComplexReports

@@ -434 +434 @@
 
 R_final ← τ Year (P)
+
+
+== 6# Monthly analysis of course activity, completions, instructor involvement, and student success within a given year
+
+This SQL query provides a month-by-month analysis of the platform within a selected year, combining course activity data with student performance indicators.
+The report evaluates what happens in each month through:
+- The total number of course enrollments.
+- The total number of completed courses
+- The number of instructors whose courses had enrollments
+- The average student score achieved through quiz attempts in active courses
+- The average success at the course level
+- The final average success at the month level across all active courses
+
+This query:
+- Groups platform activity month by month within a given year
+- Combines enrollment, completion, instructor, and quiz performance data
+- Calculates student success using average quiz score
+- Derives course-level average success and final monthly average success
+- Provides a broader analytical picture of platform activity and learning outcomes over time
+
+{{{
+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;
+
+}}}
+
+
+==== Solution Relational Algebra
+
+- E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
+- C(course_id, name, price, status, instructor_id)
+- Q(quiz_id, total_points, passing_score, lesson_id)
+- QA(attempt_id, score, attempt_date, user_id, quiz_id)
+- L(lesson_id, module_id, title, material)
+- M(module_id, course_id, title, description)
+
+
+**JOIN on all tables:**
+
+J1 ← QA ⋈ QA.quiz_id = Q.quiz_id Q
+
+J2 ← J1 ⋈ Q.lesson_id = L.lesson_id L
+
+J3 ← J2 ⋈ L.module_id = M.module_id M
+
+J4 ← J3 ⋈ M.course_id = C.course_id C
+
+J5 ← E ⋈ E.course_id = C.course_id C
+
+**Projection with date transformation for student-course-month performance**
+
+F1 ← π QA.user_id, C.course_id, QA.score, YEAR(QA.attempt_date) → Year, MONTH(QA.attempt_date) → Month (J4)
+
+**Grouping and aggregate calculations at student-course-month level**
+
+G1 ← Year, Month, course_id, user_id γ
+AVG(score) → Avg_Student_Score (F1)
+
+**Grouping and aggregate calculations at course-month level**
+
+G2 ← Year, Month, course_id γ
+AVG(Avg_Student_Score) → Avg_Course_Success (G1)
+
+**Grouping and aggregate calculations at the month level**
+G3 ← Year, Month γ
+AVG(Avg_Course_Success) → Final_Month_Success (G2)
+
+**Projection with date transformation for enrollment activity**
+F2 ← π enrollment_id, user_id, course_id, instructor_id, completion_status, YEAR(enroll_date) → Year, MONTH(enroll_date) → Month (J5)
+
+**Grouping and aggregate calculations for monthly platform activity**
+G4 ← Year, Month γ
+COUNT(DISTINCT enrollment_id) → Total_Enrollments,
+COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments,
+COUNT(DISTINCT instructor_id) → Active_Instructors (F2)
+
+**Joining activity data with performance data**
+R1 ← G4 ⋈ G4.Year = G3.Year ∧ G4.Month = G3.Month G3
+
+
+**Final projection**
+
+P ← π Year, Month, Total_Enrollments, Completed_Enrollments, Active_Instructors, Final_Month_Success (R1)
+
+
+**Chronological ordering by year**
+
+R_final ← τ Year, Month (P)