у меня есть SQL запрос, проанализируй его, и если требуется, оптимизируй
при этом сохрани комментарии

WITH CategoryHierarchy AS (
   /* Anchor member */
    SELECT
        id,
        code,
        name,
        parent_role_id,
        catalog_name,
        0 AS level,
        CAST(NULL AS BIGINT) AS role_with_current_parent_id,
        CAST(NULL AS NVARCHAR(255)) AS role_with_current_parent_name,
        CAST(NULL AS NVARCHAR(255)) AS role_with_current_parent_code
    FROM
        roles
    WHERE
        id = 7379164496433276122

    UNION ALL

    /* Recursive member */
    SELECT
        r.id,
        r.code,
        r.name,
        r.parent_role_id,
        r.catalog_name,
        ch.level + 1,
        CASE WHEN ch.role_with_current_parent_id IS NULL AND r.parent_role_id = 7379164496433276122 THEN r.id ELSE ch.role_with_current_parent_id END,
        CASE WHEN ch.role_with_current_parent_name IS NULL AND r.parent_role_id = 7379164496433276122 THEN CAST(r.name AS NVARCHAR(255)) ELSE ch.role_with_current_parent_name END,
        CASE WHEN ch.role_with_current_parent_code IS NULL AND r.parent_role_id = 7379164496433276122 THEN CAST(r.code AS NVARCHAR(255)) ELSE ch.role_with_current_parent_code END
    FROM
        roles r
        INNER JOIN CategoryHierarchy ch ON r.parent_role_id = ch.id
),
catalog_courses AS (
    SELECT
        ch.id AS role_id,
        ch.code AS role_code,
        ch.name AS role_name,
        ch.parent_role_id,
        ch.catalog_name,
        ch.level,
        ch.role_with_current_parent_id,
        ch.role_with_current_parent_name,
        ch.role_with_current_parent_code,
        course_from_catalog.id AS course_id
    FROM
        CategoryHierarchy ch
        OUTER APPLY (
            SELECT course_from_catalog.id
            FROM courses course_from_catalog
            WHERE course_from_catalog.role_id.value('.', 'NVARCHAR(MAX)') LIKE '%' + CAST(ch.id AS NVARCHAR(MAX)) + '%'
        ) course_from_catalog
    WHERE
        /* ch.id <> 7379164496433276122 AND */ /* если включить этот блок, то не отобразятся курсы из корня данной категории */
        course_from_catalog.id IS NOT NULL
),
group_roles AS (
    SELECT group_from_catalog.id
    FROM group_collaborators gr_coll
    INNER JOIN groups group_from_catalog ON gr_coll.group_id = group_from_catalog.id
    WHERE gr_coll.collaborator_id = 7090686344175026698
        AND group_from_catalog.role_id.value('.', 'NVARCHAR(MAX)') LIKE '%7379165199785226216%'
),
role_courses AS (
    SELECT
        roles.id as role_id,
        roles.code as role_code
    FROM roles
    WHERE roles.parent_role_id = 7379165938627324979
        AND roles.catalog_name = 'course'
        AND ISNUMERIC(roles.code) = 1
        AND CAST(roles.code AS BIGINT) IS NOT NULL
)
SELECT
    course_from_catalog.id as course_id,
    course_from_catalog.name as course_name,
    course_from_catalog.resource_id as course_pict,
    catalog_courses.role_id as folder_in_course_catalog_role_id,
    catalog_courses.role_name as folder_in_course_catalog_role_name,
    catalog_courses.role_code as folder_in_course_catalog_role_pict,
    catalog_courses.role_with_current_parent_id,
    catalog_courses.role_with_current_parent_name,
    catalog_courses.role_with_current_parent_code
FROM
    group_roles
    INNER JOIN role_courses ON group_roles.id = role_courses.role_code
    OUTER APPLY (
        SELECT
            course_from_catalog.id,
            course_from_catalog.name,
            course_from_catalog.resource_id
        FROM courses course_from_catalog
        WHERE course_from_catalog.role_id.value('.', 'NVARCHAR(MAX)') LIKE '%' + CAST(role_courses.role_id AS NVARCHAR(MAX)) + '%'
    ) course_from_catalog
    INNER JOIN catalog_courses ON course_from_catalog.id = catalog_courses.course_id
WHERE
    course_from_catalog.id IS NOT NULL;