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

Timestamp:

09/30/25 21:48:17 (13 days ago)

Author:

206046

Comment:

--

Analytical and statistical querying

@@ -1 +1 @@
 = Analytical and statistical querying =
 
-== dentify At-Risk Students Based on Attendance and Grades (multi-metric scoring system) ==
+== Identify At-Risk Students Based on Attendance and Grades (multi-metric scoring system) ==
 
 {{{#!sql
@@ -45 +45 @@
 }}}
 
-== Find Students Who Attended Less Than 50% of Their Classes ==
+== Rolling Average of Assignment Scores per Student ==
 
 {{{#!sql
+SELECT 
+    s.id_student,
+    u.username,
+    a.id_assignment,
+    sub.progress_score,
+    AVG(sub.progress_score) OVER (PARTITION BY s.id_student ORDER BY sub.submitted_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg_score
+FROM Submits sub
+JOIN Student s ON sub.id_student = s.id_student
+JOIN User u ON s.id_user = u.id_user
+JOIN Assignment a ON sub.id_assignment = a.id_assignment
+ORDER BY s.id_student, sub.submitted_at;
 
 }}}
 
-== Find the Most Enrolled Course ==
+== Find Classes with Low Attendance and Underperforming Scores ==
 
 {{{#!sql
+WITH low_attendance_classes AS (
+    SELECT 
+        a.id_class,
+        COUNT(*) FILTER (WHERE a.attended = TRUE) * 100.0 / COUNT(*) AS attendance_rate
+    FROM Attendance a
+    GROUP BY a.id_class
+    HAVING COUNT(*) > 5 AND COUNT(*) FILTER (WHERE a.attended = TRUE) * 100.0 / COUNT(*) < 60
+),
+low_score_classes AS (
+    SELECT 
+        c.id_class,
+        AVG(sub.progress_score) AS avg_score
+    FROM Course_Class cc
+    JOIN Class c ON cc.id_class = c.id_class
+    JOIN Course_Assignment ca ON cc.id_Course = ca.id_Course
+    JOIN Assignment a ON ca.id_assignment = a.id_assignment
+    JOIN Submits sub ON a.id_assignment = sub.id_assignment
+    GROUP BY c.id_class
+    HAVING AVG(sub.progress_score) < 60
+)
+SELECT DISTINCT
+    la.id_class,
+    cl.start_time,
+    cl.end_time,
+    la.attendance_rate,
+    ls.avg_score
+FROM low_attendance_classes la
+JOIN low_score_classes ls ON la.id_class = ls.id_class
+JOIN Class cl ON cl.id_class = la.id_class;
 
 }}}
 
-== Get the Percentage of Students Who Completed a Course ==
+== Time Series Report: Avg Score Per Week ==
 {{{#!sql
+SELECT 
+    DATE_TRUNC('week', submitted_at) AS week,
+    AVG(progress_score) AS avg_score
+FROM Submits
+GROUP BY DATE_TRUNC('week', submitted_at)
+ORDER BY week;
 
 }}}
 
-= Find the Average Progress Score for Each Course, and Compare It to Overall Average =
+== Teacher Effectiveness Score ==
 
 {{{#!sql
+WITH teacher_ratings AS (
+    SELECT 
+        id_teacher,
+        AVG(rating) AS avg_rating
+    FROM Rates
+    GROUP BY id_teacher
+),
+teacher_scores AS (
+    SELECT 
+        c.created_by AS id_teacher,
+        AVG(sub.progress_score) AS avg_student_score
+    FROM Course c
+    JOIN Course_Assignment ca ON c.id_Course = ca.id_Course
+    JOIN Assignment a ON ca.id_assignment = a.id_assignment
+    JOIN Submits sub ON sub.id_assignment = a.id_assignment
+    GROUP BY c.created_by
+)
+SELECT 
+    t.id_teacher,
+    u.username,
+    r.avg_rating,
+    s.avg_student_score,
+    ROUND((r.avg_rating * 0.5 + s.avg_student_score * 0.5), 2) AS effectiveness_score
+FROM Teacher t
+JOIN User u ON t.id_user = u.id_user
+JOIN teacher_ratings r ON t.id_teacher = r.id_teacher
+JOIN teacher_scores s ON t.id_teacher = s.id_teacher
+ORDER BY effectiveness_score DESC;
 
 }}}
 
-= Rank Professors by Subject Based on Average Rating =
+== Identify Students with Sharp Score Drop Between Assignments ==
 
 {{{#!sql
+WITH score_changes AS (
+    SELECT 
+        id_student,
+        id_assignment,
+        progress_score,
+        progress_score - LAG(progress_score) OVER (PARTITION BY id_student ORDER BY submitted_at) AS score_diff
+    FROM Submits
+)
+SELECT 
+    sc.id_student,
+    u.username,
+    sc.id_assignment,
+    sc.progress_score,
+    sc.score_diff
+FROM score_changes sc
+JOIN Student s ON sc.id_student = s.id_student
+JOIN User u ON s.id_user = u.id_user
+WHERE sc.score_diff < -20
+ORDER BY sc.score_diff ASC;
 
 }}}
 
+== Detect Duplicate Submissions or Possible Cheating Behavior ==
 
+{{{#!sql
+SELECT 
+    s1.id_student,
+    s2.id_student AS other_student,
+    a.id_assignment,
+    s1.progress_score,
+    s2.progress_score,
+    ABS(s1.progress_score - s2.progress_score) AS score_diff
+FROM Submits s1
+JOIN Submits s2 
+  ON s1.id_assignment = s2.id_assignment 
+ AND s1.id_student <> s2.id_student
+ AND ABS(s1.progress_score - s2.progress_score) < 1
+ORDER BY score_diff ASC;
 
+}}}