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;

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

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;

Rank Professors by Subject Based on Average Rating

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;