Changes between Version 3 and Version 4 of Analytical and statistical querying

Timestamp:

02/10/25 01:21:03 (9 months ago)

Author:

206046

Comment:

--

Analytical and statistical querying

@@ -47 +47 @@
 }}}
 
+= Find the Average Progress Score for Each Course, and Compare It to Overall Average =
+
+{{{#!sql
+SELECT c.id_Course, s.name AS subject_name, 
+       AVG(sub.progress_score) AS course_avg_score,
+       (SELECT AVG(progress_score) FROM Submission) AS overall_avg_score
+FROM Submission sub
+JOIN Enrollment e ON sub.id_student = e.id_student
+JOIN Course c ON e.id_Course = c.id_Course
+JOIN Subject_Course sc ON c.id_Course = sc.id_Course
+JOIN Subject s ON sc.id_subject = s.id_subject
+GROUP BY c.id_Course, s.name;
+}}}
+
+= Rank Professors by Subject Based on Average Rating =
+
+{{{#!sql
+SELECT 
+    t.id_teacher,
+    u.username AS teacher_name,
+    s.id_subject,
+    s.name AS subject_name,
+    AVG(r.rating) AS avg_rating,
+    RANK() OVER (PARTITION BY s.id_subject ORDER BY AVG(r.rating) DESC) AS rank
+FROM Rating r
+JOIN Teacher t ON r.id_teacher = t.id_teacher
+JOIN User u ON t.id_user = u.id_user
+JOIN Course c ON t.id_teacher = c.created_by
+JOIN Subject_Course sc ON c.id_Course = sc.id_Course
+JOIN Subject s ON sc.id_subject = s.id_subject
+GROUP BY t.id_teacher, u.username, s.id_subject, s.name
+ORDER BY avg_rating DESC;
+}}}
+
+
+