Analytical and statistical querying

Identify At-Risk Students Based on Attendance and Grades (multi-metric scoring system)

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
ORDER BY risk_level DESC, avg_score ASC;

Rolling Average of Assignment Scores per Student

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 Classes with Low Attendance and Underperforming Scores

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;

Time Series Report: Avg Score Per Week

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;

Teacher Effectiveness Score

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;

Identify Students with Sharp Score Drop Between Assignments

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

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;