Analytical and statistical querying

Find the Students Who Scored Below Average on Assignments

SELECT s.id_student, u.username, sub.progress_score
FROM Submission sub
JOIN Student s ON sub.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);

Find Students Who Attended Less Than 50% of Their Classes

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

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

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;