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

Timestamp:

02/10/25 01:15:46 (13 days ago)

Author:

206046

Comment:

--

Analytical and statistical querying

@@ -10 +10 @@
 WHERE sub.progress_score < (SELECT AVG(progress_score) FROM Submission);
 }}}
+
+== Find Students Who Attended Less Than 50% of Their Classes ==
+
+{{{#!sql
+SELECT s.id_student, u.username, 
+       (COUNT(a.attended) FILTER (WHERE a.attended = TRUE) * 100.0 / COUNT(*)) AS attendance_rate
+FROM Attendance a
+JOIN Student s ON a.id_student = s.id_student
+JOIN User u ON s.id_user = u.id_user
+GROUP BY s.id_student, u.username
+HAVING (COUNT(a.attended) FILTER (WHERE a.attended = TRUE) * 100.0 / COUNT(*)) < 50;
+}}}
+
+== Find the Most Enrolled Course ==
+
+{{{#!sql
+SELECT c.id_Course, s.name AS subject_name, COUNT(e.id_enrollment) AS total_enrollments
+FROM Enrollment e
+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
+ORDER BY total_enrollments DESC
+LIMIT 1;
+}}}
+
+== Get the Percentage of Students Who Completed a Course ==
+{{{#!sql
+SELECT c.id_Course, s.name AS subject_name,
+       COUNT(CASE WHEN e.status = 'Completed' THEN 1 END) * 100.0 / COUNT(*) AS completion_rate
+FROM Enrollment e
+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;
+}}}
+