SELECT u.name AS university_name, f.name AS faculty_name, COUNT(DISTINCT p.id) AS total_professors, COUNT(DISTINCT s.id) AS total_subjects
FROM university u JOIN faculty f ON u.id = f.university_id LEFT JOIN professor p ON f.id = p.facultyid LEFT JOIN subject s ON f.id = s.facultyid
GROUP BY u.name, f.name
ORDER BY total_professors DESC;

SELECT s.name || ' ' || s.surname AS student_name, s.studentindex, f.name AS faculty_name, COUNT(DISTINCT ss.subject_id) AS enrolled_subjects, COUNT(DISTINCT a.student_id) AS advice_requests
FROM student s JOIN faculty f ON s.facultyid = f.id LEFT JOIN student_subject ss ON s.id = ss.student_id LEFT JOIN advice a ON s.id = a.student_id
GROUP BY s.id, s.name, s.surname, s.studentindex, f.name
ORDER BY enrolled_subjects DESC;

SELECT s.studentindex, s.name || ' ' || s.surname AS student_name, AVG(ss.final_grade) AS semester_gpa, COUNT(ss.subject_id) AS completed_courses_count
FROM student s
         JOIN student_subject ss ON s.id = ss.student_id
         JOIN subject sub ON ss.subject_id = sub.id
WHERE sub.semester = 2
  AND ss.status = 'Completed'
  AND ss.final_grade IS NOT NULL
GROUP BY s.id, s.studentindex, s.name, s.surname
HAVING COUNT(ss.subject_id) = (
    SELECT COUNT(*)
    FROM student_subject ss2
    WHERE ss2.student_id = s.id
)
ORDER BY semester_gpa DESC;

INSERT INTO subject (id, name, semester, facultyid)
VALUES (999, 'Test Advanced Database', 2, 1);

INSERT INTO student (id, name, surname, location, studentindex, facultyid)
VALUES (999, 'Petar', 'Petrovski', 'Skopje', 123456, 1);

INSERT INTO student_subject (student_id, subject_id, status, final_grade, enrollment_date, professor_id)
VALUES (999, 999, 'Completed', 10, '2026-02-15', 1);


WITH StudentGPA AS (
    SELECT
        s.studentindex,
        s.name || ' ' || s.surname AS student_name,
        EXTRACT(YEAR FROM ss.enrollment_date) AS enrollment_year,
        AVG(ss.final_grade) AS cumulative_gpa,
        RANK() OVER (
            PARTITION BY EXTRACT(YEAR FROM ss.enrollment_date)
            ORDER BY AVG(ss.final_grade) DESC
            ) as gpa_rank
    FROM student s
             JOIN student_subject ss ON s.id = ss.student_id
    WHERE ss.final_grade IS NOT NULL
    GROUP BY s.studentindex, s.name, s.surname, EXTRACT(YEAR FROM ss.enrollment_date)
)
SELECT
    enrollment_year,
    studentindex,
    student_name,
    cumulative_gpa
FROM StudentGPA
WHERE gpa_rank = 1
ORDER BY enrollment_year DESC;