Changes between Version 4 and Version 5 of ComplexReports

Timestamp:

03/30/26 10:07:01 (12 days ago)

Author:

221296

Comment:

--

ComplexReports

@@ -52 +52 @@
 
 
-== Monthly analysis of instructor activity by courses, students, modules, and lessons 
+== 2# Monthly analysis of instructor activity by courses, students, modules, and lessons 
 
 {{{
@@ -119 +119 @@
 
 
-== Annual subscription and revenue analysis by subscription plan
+== 3# Annual subscription and revenue analysis by subscription plan
 
 {{{
@@ -175 +175 @@
 
 
-== Yearly category analysis by attempts, students, average score, and pass rate
+== 4# Yearly category analysis by attempts, students, average score, and pass rate
 
 {{{
@@ -276 +276 @@
 R_final ← τ Year, Pass_Rate_Percentage DESC (P)
 
+
+== 4# 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.
+Active platform performance is evaluated through:
+- Instructor activity based on courses with enrollments.
+- Student participation through enrollments and quiz attempts
+- Course completion status and issued certificates
+- Student success is measured through progress percentage
+- Quiz performance based on average scores and pass rate
+- Course-level success calculated as average quiz score per course
+- Overall yearly success calculated as the average performance across all courses
+This query:
+- Aggregates data year by year to observe platform growth and trends
+- Combines multiple entities, including instructors, students, courses, enrollments, quizzes, and certificates
+- Calculates both activity-based and performance-based metrics
+- Derives course-level and overall yearly success indicators
+- Provides a multi-dimensional analytical view of the platform
+
+{{{
+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
+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
+GROUP BY
+    EXTRACT(YEAR FROM qa.attempt_date),
+    cat.name
+ORDER BY
+    Year,
+    Pass_Rate_Percentage DESC;
+
+}}}
+
+
+==== Solution Relational Algebra
+
+- 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)
+
+
+**Grouping and aggregate calculations**
+
+G ← Year, Category_Name γ
+
+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)
+
+
+**Chronological ordering by year**
+
+R_final ← τ Year, Pass_Rate_Percentage DESC (P)