Changes between Version 2 and Version 3 of ComplexReports

Timestamp:

03/14/26 13:43:50 (7 days ago)

Author:

221296

Comment:

--

ComplexReports

@@ -52 +52 @@
 
 
-== Instructor performance analysis by number of courses, students, modules and lessons 
-
-{{{
-SELECT
-    i.id AS Instructor_ID,
-    ue.first_name || ' ' || ue.last_name AS Instructor_Name,
+== Monthly analysis of instructor activity by courses, students, modules, and lessons 
+
+{{{
+SELECT
+    EXTRACT(YEAR FROM e.enroll_date) AS Year,
+    EXTRACT(MONTH FROM e.enroll_date) AS Month,
+    COUNT(DISTINCT i.id) AS Total_Instructors,
     COUNT(DISTINCT c.course_id) AS Total_Courses,
     COUNT(DISTINCT e.user_id) AS Total_Students,
@@ -63 +64 @@
     COUNT(DISTINCT l.lesson_id) AS Total_Lessons,
     ROUND(AVG(c.price), 2) AS Avg_Course_Price
-FROM instructors i
-JOIN user_entity ue
-    ON ue.id = i.id
+FROM enrollment e
 JOIN course c
-    ON c.instructor_id = i.id
-LEFT JOIN enrollment e
-    ON e.course_id = c.course_id
+    ON c.course_id = e.course_id
+JOIN instructors i
+    ON i.id = c.instructor_id
 LEFT JOIN module m
     ON m.course_id = c.course_id
@@ -75 +74 @@
     ON l.module_id = m.module_id
 GROUP BY
-    i.id,
-    ue.first_name,
-    ue.last_name
+    EXTRACT(YEAR FROM e.enroll_date),
+    EXTRACT(MONTH FROM e.enroll_date)
 ORDER BY
-    Total_Students DESC,
-    Total_Courses DESC;
-}}}
-
-==== Solution Relational Algebra
-
+    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)
 - I(id)
-- U(id, first_name, last_name, ...)
-- C(course_id, name, price, status, instructor_id)
-- E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
 - M(module_id, course_id, title, description)
 - L(lesson_id, module_id, title, material)
@@ -94 +91 @@
 **JOIN on all tables:**
 
-J1 ← I ⋈ I.id = U.id U
-
-J2 ← J1 ⋈ I.id = C.instructor_id C
-
-J3 ← J2 ⋈ C.course_id = E.course_id E
-
-J4 ← J3 ⋈ C.course_id = M.course_id M
-
-J5 ← J4 ⋈ M.module_id = L.module_id L
+J1 ← E ⋈ E.course_id = C.course_id C
+
+J2 ← J1 ⋈ C.instructor_id = I.id I
+
+J3 ← J2 ⟕ C.course_id = M.course_id M
+
+J4 ← J3 ⟕ M.module_id = L.module_id L
 
 **Projection with instructor full name**
 
-F1 ← π id, CONCAT(first_name, ' ', last_name) → Instructor_Name, course_id, user_id, module_id, lesson_id, price (J5)
-
-**Grouping and aggregate calculations**
-
-G ← id, Instructor_Name γ COUNT(DISTINCT course_id) → Total_Courses, COUNT(DISTINCT user_id) → 
-Total_Students, COUNT(DISTINCT module_id) → Total_Modules, COUNT(DISTINCT lesson_id) → 
-Total_Lessons, AVG(price) → Avg_Course_Price (F1)
+F1 ← π user_id, course_id, id, module_id, lesson_id, price, YEAR(enroll_date) → Year, MONTH(enroll_date) → Month (J4)
+
+**Grouping and aggregate calculations**
+
+G ← Year, Month γ
+COUNT(DISTINCT id) → Total_Instructors,
+COUNT(DISTINCT course_id) → Total_Courses,
+COUNT(DISTINCT user_id) → Total_Students,
+COUNT(DISTINCT module_id) → Total_Modules,
+COUNT(DISTINCT lesson_id) → Total_Lessons,
+AVG(price) → Avg_Course_Price
+(F1)
 
 **Ordering by number of students and courses**
 
-R_final ← τ Total_Students DESC, Total_Courses DESC (G)
+R_final ← τ Year, Month (G)
 
 
@@ -175 +175 @@
 
 
-== Course quiz success analysis by attempts, average score and pass rate
-
-{{{
-SELECT
-    c.course_id,
-    c.name AS Course_Name,
+== Yearly category analysis by attempts, students, average score, and pass rate
+
+{{{
+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_Attempted,
+    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,
+    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,
+        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 course c
+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.course_id = c.course_id
-JOIN lesson l
-    ON l.module_id = m.module_id
-JOIN quiz q
-    ON q.lesson_id = l.lesson_id
-LEFT JOIN quiz_attempt qa
-    ON qa.quiz_id = q.quiz_id
+    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
-    c.course_id,
-    c.name
+    EXTRACT(YEAR FROM qa.attempt_date),
+    cat.name
 ORDER BY
-    Pass_Rate_Percentage DESC,
-    Avg_Score DESC;
-
-}}}
-
-
-==== Solution Relational Algebra
-
+    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)
-- 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)
+- CC(course_id, category_id)
+- CAT(category_id, name, description)
+
 
 **JOIN on all tables:**
 
-J1 ← C ⋈ C.course_id = M.course_id M
-
-J2 ← J1 ⋈ M.module_id = L.module_id L
-
-J3 ← J2 ⋈ L.lesson_id = Q.lesson_id Q
-
-J4 ← J3 ⋈ Q.quiz_id = QA.quiz_id QA
+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 ← π course_id, name, attempt_id, user_id, score, passing_score (J4)
-
-F2 ← ρ Course_Name/name (F1)
-
-**Grouping and aggregate calculations**
-
-G ← course_id, Course_Name γ COUNT(DISTINCT attempt_id) → Total_Attempts, COUNT(DISTINCT user_id) 
-→ Total_Students_Attempted, AVG(score) → Avg_Score, COUNT(DISTINCT [score >= passing_score] 
-attempt_id) → Passed_Attempts (F2)
+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 ← π course_id, Course_Name, Total_Attempts, Total_Students_Attempted, Avg_Score, Passed_Attempts, 
+P ← π Year, Category_Name, Total_Attempts, Total_Students, Avg_Score, Passed_Attempts,
+
 (Passed_Attempts / Total_Attempts) * 100 → Pass_Rate_Percentage (G)
 
-**Ordering by pass rate and average score**
-
-R_final ← τ Pass_Rate_Percentage DESC, Avg_Score DESC (P)
-
+
+**Chronological ordering by year**
+
+R_final ← τ Year, Pass_Rate_Percentage DESC (P)
+