Changes between Initial Version and Version 1 of ComplexReports

Timestamp:

03/07/26 15:30:04 (2 weeks ago)

Author:

221296

Comment:

--

ComplexReports

@@ +1 @@
+== Annual analysis of course enrollments, completions and certificates
+{{{
+SELECT
+    EXTRACT(YEAR FROM e.enroll_date) AS Year,
+    COUNT(DISTINCT e.user_id) AS Total_Students,
+    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 cert.certificate_id) AS Total_Certificates,
+    ROUND(
+        COUNT(DISTINCT CASE WHEN e.completion_status = 'COMPLETED' THEN e.enrollment_id END)::DECIMAL
+        / NULLIF(COUNT(DISTINCT e.enrollment_id), 0) * 100, 2) AS Completion_Rate_Percentage,
+    ROUND(AVG(e.progress_percentage), 2) AS Avg_Progress_Percentage
+FROM enrollment e
+LEFT JOIN certificate cert
+    ON cert.enrollment_id = e.enrollment_id
+GROUP BY EXTRACT(YEAR FROM e.enroll_date)
+ORDER BY Year;
+}}}
+
+==== Solution Relational Algebra
+
+- E(enrollment_id, user_id, course_id, enroll_date, completion_status, progress_percentage)
+- CERT(certificate_id, enrollment_id, issue_date, certificate_code, status)
+
+
+**JOIN on all tables**
+
+J1 ← E ⟕ E.enrollment_id = CERT.enrollment_id CERT
+
+**Projection with date transformation**
+
+E' ← π enrollment_id, user_id, completion_status, progress_percentage, certificate_id, YEAR(enroll_date) → Year (J1)
+
+**Grouping and aggregate calculations**
+
+G ← Year γ
+COUNT(DISTINCT user_id) → Total_Students,
+COUNT(DISTINCT enrollment_id) → Total_Enrollments,
+COUNT(DISTINCT [completion_status = 'COMPLETED'] enrollment_id) → Completed_Enrollments,
+COUNT(DISTINCT certificate_id) → Total_Certificates,
+AVG(progress_percentage) → Avg_Progress_Percentage
+(E')
+
+**Final projection and percentage calculation**
+
+P ← π Year, Total_Students, Total_Enrollments, Completed_Enrollments, Total_Certificates,
+(Completed_Enrollments / Total_Enrollments) * 100 → Completion_Rate_Percentage,
+Avg_Progress_Percentage (G)
+
+**Chronological ordering by year**
+R_final ← τ Year (P)
+
+
+== 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,
+    COUNT(DISTINCT c.course_id) AS Total_Courses,
+    COUNT(DISTINCT e.user_id) AS Total_Students,
+    COUNT(DISTINCT m.module_id) AS Total_Modules,
+    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
+JOIN course c
+    ON c.instructor_id = i.id
+LEFT JOIN enrollment e
+    ON e.course_id = c.course_id
+LEFT JOIN module m
+    ON m.course_id = c.course_id
+LEFT JOIN lesson l
+    ON l.module_id = m.module_id
+GROUP BY
+    i.id,
+    ue.first_name,
+    ue.last_name
+ORDER BY
+    Total_Students DESC,
+    Total_Courses DESC;
+}}}
+
+==== Solution Relational Algebra
+
+- 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)
+
+**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
+
+**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)
+
+**Ordering by number of students and courses**
+
+R_final ← τ Total_Students DESC, Total_Courses DESC (G)
+
+
+== Annual subscription and revenue analysis by subscription plan
+
+{{{
+SELECT
+    EXTRACT(YEAR FROM us.start_date) AS Year,
+    sp.name AS Plan_Name,
+    COUNT(DISTINCT us.subscription_id) AS Total_Subscriptions,
+    COUNT(DISTINCT us.user_id) AS Total_Users,
+    COUNT(DISTINCT p.payment_id) AS Total_Payments,
+    ROUND(SUM(COALESCE(p.amount, 0)), 2) AS Total_Revenue,
+    ROUND(AVG(COALESCE(p.amount, 0)), 2) AS Avg_Payment_Amount
+FROM user_subscription us
+JOIN subscription_plan sp
+    ON sp.plan_id = us.plan_id
+LEFT JOIN payment p
+    ON p.subscription_id = us.subscription_id
+GROUP BY
+    EXTRACT(YEAR FROM us.start_date),
+    sp.name
+ORDER BY
+    Year,
+    Total_Revenue DESC;
+}}}
+
+==== Solution Relational Algebra
+
+- US(subscription_id, user_id, plan_id, start_date, end_date, status)
+- SP(plan_id, name, price, duration_months, description, access_type)
+- P(payment_id, user_id, subscription_id, amount)
+
+
+**JOIN on all tables**
+
+J1 ← US ⋈ US.plan_id = SP.plan_id SP
+
+J2 ← J1 ⋈ US.subscription_id = P.subscription_id P
+
+**Projection with date transformation**
+
+F1 ← π subscription_id, user_id, payment_id, amount, name, YEAR(start_date) → Year (J2)
+
+**Renaming attribute**
+
+F2 ← ρ Plan_Name/name (F1)
+
+**Grouping and aggregate calculations**
+
+G ← Year, Plan_Name γ COUNT(DISTINCT subscription_id) → Total_Subscriptions, COUNT(DISTINCT user_id)
+→ Total_Users, COUNT(DISTINCT payment_id) → Total_Payments, SUM(amount) → Total_Revenue, 
+AVG(amount) → Avg_Payment_Amount (F2)
+
+**Chronological ordering and descending revenue ordering**
+
+R_final ← τ Year, Total_Revenue DESC (G)
+
+
+== Course quiz success analysis by attempts, average score and pass rate
+
+{{{
+SELECT
+    c.course_id,
+    c.name AS Course_Name,
+    COUNT(DISTINCT qa.attempt_id) AS Total_Attempts,
+    COUNT(DISTINCT qa.user_id) AS Total_Students_Attempted,
+    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 course c
+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
+GROUP BY
+    c.course_id,
+    c.name
+ORDER BY
+    Pass_Rate_Percentage DESC,
+    Avg_Score DESC;
+
+==== Solution Relational Algebra
+
+- 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)
+
+**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
+
+**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)
+
+**Final projection and percentage calculation**
+
+P ← π course_id, Course_Name, Total_Attempts, Total_Students_Attempted, 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)
+