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

Timestamp:

09/30/25 21:44:04 (13 days ago)

Author:

206046

Comment:

--

Analytical and statistical querying

@@ -1 +1 @@
 = Analytical and statistical querying =
 
-== Find the Students Who Scored Below Average on Assignments ==
+== dentify At-Risk Students Based on Attendance and Grades (multi-metric scoring system) ==
 
 {{{#!sql
-SELECT s.id_student, u.username, sub.progress_score
-FROM Submission sub
-JOIN Student s ON sub.id_student = s.id_student
+WITH student_attendance AS (
+    SELECT 
+        id_student,
+        COUNT(*) AS total_classes,
+        COUNT(*) FILTER (WHERE attended = TRUE) AS attended_classes
+    FROM Attendance
+    GROUP BY id_student
+),
+student_grades AS (
+    SELECT 
+        id_student,
+        AVG(progress_score) AS avg_score
+    FROM Submits
+    GROUP BY id_student
+),
+student_risk_score AS (
+    SELECT 
+        a.id_student,
+        (attended_classes * 1.0 / NULLIF(total_classes, 0)) AS attendance_rate,
+        g.avg_score,
+        CASE 
+            WHEN (attended_classes * 1.0 / NULLIF(total_classes, 0)) < 0.5 AND g.avg_score < 60 THEN 'High Risk'
+            WHEN (attended_classes * 1.0 / NULLIF(total_classes, 0)) < 0.7 OR g.avg_score < 70 THEN 'Medium Risk'
+            ELSE 'Low Risk'
+        END AS risk_level
+    FROM student_attendance a
+    JOIN student_grades g ON a.id_student = g.id_student
+)
+SELECT 
+    s.id_student,
+    u.username,
+    attendance_rate,
+    avg_score,
+    risk_level
+FROM student_risk_score r
+JOIN Student s ON r.id_student = s.id_student
 JOIN User u ON s.id_user = u.id_user
-WHERE sub.progress_score < (SELECT AVG(progress_score) FROM Submission);
+ORDER BY risk_level DESC, avg_score ASC;
+
 }}}
 
@@ -14 +48 @@
 
 {{{#!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;
+
 }}}
 
@@ -26 +54 @@
 
 {{{#!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;
+
 }}}
 
@@ -50 +65 @@
 
 {{{#!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;
+
 }}}
 
@@ -64 +71 @@
 
 {{{#!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;
+
 }}}