SELECT
    C.LECTURERCODE,
    L.EMPLOYEESNAME LECTURERNAME,
    L.TIPEDOSEN,
    C.SUBJECTCODE,
    C.COURSEID,
    C."CLASS",
    ST.STUDYPROGRAMNAME,
    S.SUBJECTNAME,
    C.SUBJECTCODE || '-' || S.SUBJECTNAME || '-' || C."CLASS" COURSESUBJECT,
    NVL( ROUND( ( TOTALDONE / NVL( TOTAL, 1 ) ) * 100, 2 ), 0 ) PERSENTASE,
    TOTALDONE,
    TOTAL
FROM
    COURSE C
    JOIN SUBJECTS S ON ( S.SUBJECTID = C.SUBJECTID )
    LEFT JOIN LECTURERVIEW L ON ( L.LECTURERCODE = C.LECTURERCODE )
    JOIN STUDYPROGRAM ST ON ( C.STUDYPROGRAMID = ST.STUDYPROGRAMID )
    LEFT JOIN (
    SELECT
        SUBJECTID,
        SCHOOLYEAR,
        SEMESTER,
        COUNT( B.ID ) TOTAL
    FROM
        CLO_MATERIAL_TEACHING A
        JOIN CLO_TEACHING_PLANS B ON ( CLO_LEARNING_PLAN_ID = B.ID )
    WHERE
        SCHOOLYEAR = '2324'
        AND SEMESTER = '1'
        AND A.ACTIVE_STATUS = '1'
        AND B.ACTIVE_STATUS = '1'
        AND B.APPROVAL_STATUS = 'A'
    GROUP BY
        SUBJECTID,
        SCHOOLYEAR,
        SEMESTER
    ) SL ON ( SL.SUBJECTID = C.SUBJECTID AND SL.SCHOOLYEAR = C.SCHOOLYEAR AND SL.SEMESTER = C.SEMESTER )
    LEFT JOIN (
    SELECT
        LECTURES.COURSEID,
        COUNT( DISTINCT CLO_MATERIAL_TEACHING_ID ) TOTALDONE
    FROM
        LECTURES_CLO_MATERIAL_TEACHING
        JOIN LECTURES USING ( LECTURESID )
        JOIN CLO_MATERIAL_TEACHING A ON ( CLO_MATERIAL_TEACHING_ID = A.ID )
        JOIN CLO_TEACHING_PLANS B ON ( CLO_LEARNING_PLAN_ID = B.ID )
    WHERE
        LECTURES.SCHOOLYEAR = '2324'
        AND LECTURES.SEMESTER = '1'
        AND A.ACTIVE_STATUS = '1'
        AND B.ACTIVE_STATUS = '1'
        AND B.APPROVAL_STATUS = 'A'
    GROUP BY
        LECTURES.COURSEID
    ) SD ON ( SD.COURSEID = C.COURSEID )
WHERE
    C.SCHOOLYEAR = '2324'
    AND C.SEMESTER = '1'
    AND C.STUDYPROGRAMID = '31'
ORDER BY
    PERSENTASE DESC