Analytical and statistical querying

dentify 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;

Find Students Who Attended Less Than 50% of Their Classes

Find the Most Enrolled Course

Get the Percentage of Students Who Completed a Course

Find the Average Progress Score for Each Course, and Compare It to Overall Average

Rank Professors by Subject Based on Average Rating