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.LECTURERCODE IN (
    SELECT
        LECTURERCODE
    FROM
        LECTURER
    WHERE
        EMPLOYEEID = '21920018-3')
ORDER BY
    PERSENTASE DESC;