Changes between Version 5 and Version 6 of ComplexReports

Timestamp:

03/30/26 10:21:15 (12 days ago)

Author:

221296

Comment:

--

ComplexReports

@@ -277 +277 @@
 
 
-== 4# Yearly analysis of overall platform activity and student success across enrollments, quiz attempts, completions, certificates, and course performance
+== 5# Yearly analysis of overall platform activity and student success across enrollments, quiz attempts, completions, certificates, and course performance
 
 This SQL query provides a comprehensive yearly analysis of the platform by combining multiple aspects of user activity, instructor engagement, and student performance.
@@ -296 +296 @@
 
 {{{
+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 qa.attempt_date) AS Year,
-    cat.name AS Category_Name,
-    COUNT(DISTINCT qa.attempt_id) AS Total_Attempts,
-    COUNT(DISTINCT qa.user_id) AS Total_Students,
-    ROUND(AVG(qa.score), 2) AS Avg_Score,
-    COUNT(
-        DISTINCT CASE
-            WHEN qa.score >= q.passing_score
-            THEN qa.attempt_id
-        END
-    ) AS Passed_Attempts,
-    ROUND(
-        COUNT(
-            DISTINCT CASE
-                WHEN qa.score >= q.passing_score
-                THEN qa.attempt_id
-            END
-        )::DECIMAL /
-        NULLIF(COUNT(DISTINCT qa.attempt_id),0) * 100,
-        2
-    ) AS Pass_Rate_Percentage
-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
+    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 = m.course_id
-JOIN course_category cc
-    ON cc.course_id = c.course_id
-JOIN category cat
-    ON cat.category_id = cc.category_id
+    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 qa.attempt_date),
-    cat.name
-ORDER BY
-    Year,
-    Pass_Rate_Percentage DESC;
+    EXTRACT(YEAR FROM e.enroll_date), ycs.final_year_success
+ORDER BY year;
 
 }}}
@@ -343 +358 @@
 ==== Solution Relational Algebra
 
+- E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
+- C(course_id, name, price, status, instructor_id)
+- CERT(certificate_id, enrollment_id, issue_date, certificate_code, status)
+- M(module_id, course_id, title, description)
+- L(lesson_id, module_id, title, material)
+- Q(quiz_id, total_points, passing_score, lesson_id)
 - QA(attempt_id, score, attempt_date, user_id, quiz_id)
-- Q(quiz_id, total_points, passing_score, lesson_id)
-- L(lesson_id, module_id, title, material)
-- M(module_id, course_id, title, description)
-- C(course_id, name, price, status, instructor_id)
-- CC(course_id, category_id)
-- CAT(category_id, name, 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 ← J4 ⋈ C.course_id = CC.course_id CC
-
-J6 ← J5 ⋈ CC.category_id = CAT.category_id CAT
-
-**Projection and renaming**
-
-F1 ← π attempt_id, user_id, score, passing_score, CAT.name → Category_Name, YEAR(attempt_date) → Year (J6)
-
+J1 ← E ⋈ E.course_id = C.course_id C
+
+J2 ← J1 ⟕ E.enrollment_id = CERT.enrollment_id CERT
+
+J3 ← J2 ⟕ C.course_id = M.course_id M
+
+J4 ← J3 ⟕ M.module_id = L.module_id L
+
+J5 ← J4 ⟕ L.lesson_id = Q.lesson_id Q
+
+J6 ← J5 ⟕ (Q.quiz_id = QA.quiz_id ∧ QA.user_id = E.user_id) QA
+
+**Projection with date transformation**
+
+F1 ← π enrollment_id, user_id, instructor_id, completion_status, progress_percentage, certificate_id, attempt_id, score, course_id,
+YEAR(enroll_date) → Year (J6)
 
 **Grouping and aggregate calculations**
 
-G ← Year, Category_Name γ
+G ← Year γ
+
+COUNT(DISTINCT instructor_id) → Total_Instructor_Activity,
+
+COUNT(DISTINCT user_id) → Total_Students,
+
+COUNT(DISTINCT enrollment_id) → Total_Enrollments,
 
 COUNT(DISTINCT attempt_id) → Total_Attempts,
 
-COUNT(DISTINCT user_id) → Total_Students,
-
-AVG(score) → Avg_Score,
-
-COUNT(DISTINCT [score ≥ passing_score] attempt_id) → Passed_Attempts (F1)
-
-**Final projection and percentage calculation**
-
-P ← π Year, Category_Name, Total_Attempts, Total_Students, Avg_Score, Passed_Attempts,
-
-(Passed_Attempts / Total_Attempts) * 100 → Pass_Rate_Percentage (G)
+COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments,
+
+COUNT(DISTINCT certificate_id) → Total_Certificates,
+
+AVG(progress_percentage) → Avg_Student_Success_Within_Courses,
+
+AVG(score) → Avg_Final_Grade_Per_Course (F1)
+
+**Course-level yearly performance**
+
+CP1 ← QA ⋈ QA.quiz_id = Q.quiz_id Q
+
+CP2 ← CP1 ⋈ Q.lesson_id = L.lesson_id L
+
+CP3 ← CP2 ⋈ L.module_id = M.module_id M
+
+CP4 ← CP3 ⋈ M.course_id = C.course_id C
+
+CF ← π course_id, score, YEAR(attempt_date) → Year (CP4)
+
+CG ← Year, course_id γ AVG(score) → Final_Grade_Per_Course (CF)
+
+**Final yearly course success**
+Y ← Year γ AVG(Final_Grade_Per_Course) → Final_Year_Success (CG)
+
+
+**Final join and projection**
+
+R1 ← G ⋈ G.Year = Y.Year Y
+
+P ← π Year, Total_Instructor_Activity, Total_Students, Total_Enrollments, Total_Attempts, Completed_Enrollments, Total_Certificates,  Avg_Student_Success_Within_Courses, Avg_Final_Grade_Per_Course, Final_Year_Success (R1)
 
 
 **Chronological ordering by year**
 
-R_final ← τ Year, Pass_Rate_Percentage DESC (P)
+R_final ← τ Year (P)