Content progress for a whole class list with Brightspace Data Sets

-- Edit the role names on the RoleName IN line to choose who counts as a learner.
WITH learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleName IN ('Student')
), ClassList AS (
    SELECT DISTINCT UserEnrollments.UserId, UserEnrollments.OrgUnitId
    FROM UserEnrollments
    JOIN learner_roles ON learner_roles.RoleId = UserEnrollments.RoleId
    JOIN OrganizationalUnits ON OrganizationalUnits.OrgUnitId = UserEnrollments.OrgUnitId
    WHERE OrganizationalUnits.Type = 'Course Offering'
), Topics AS (
    SELECT ContentObjectId, OrgUnitId
    FROM ContentObjects
    WHERE ContentObjectType = 'Topic'
      AND COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
      AND COALESCE(TRY_CAST(IsHidden AS BOOLEAN), false) = false
), TopicCounts AS (
    SELECT OrgUnitId, COUNT(DISTINCT ContentObjectId) AS Topics
    FROM Topics
    GROUP BY OrgUnitId
), Visits AS (
    SELECT P.UserId, Topics.OrgUnitId,
           COUNT(DISTINCT CASE WHEN COALESCE(TRY_CAST(P.IsVisited AS BOOLEAN), false)
               THEN P.ContentObjectId END) AS TopicsVisited,
           MAX(CASE WHEN COALESCE(TRY_CAST(P.IsVisited AS BOOLEAN), false)
               THEN P.LastVisited END) AS LastContentVisit
    FROM ContentUserProgress P
    JOIN Topics ON Topics.ContentObjectId = P.ContentObjectId
    GROUP BY P.UserId, Topics.OrgUnitId
), Completions AS (
    SELECT C.UserId, Topics.OrgUnitId, COUNT(DISTINCT C.ContentObjectId) AS TopicsCompleted
    FROM ContentUserCompletion C
    JOIN Topics ON Topics.ContentObjectId = C.ContentObjectId AND Topics.OrgUnitId = C.OrgUnitId
    WHERE C.DateCompleted IS NOT NULL
      AND COALESCE(TRY_CAST(C.IsDeleted AS BOOLEAN), false) = false
    GROUP BY C.UserId, Topics.OrgUnitId
)
SELECT DISTINCT ClassList.UserId, Users.FirstName, Users.LastName, ClassList.OrgUnitId,
       Courses.Name AS CourseName, COALESCE(TopicCounts.Topics, 0) AS Topics,
       COALESCE(Visits.TopicsVisited, 0) AS TopicsVisited,
       COALESCE(Completions.TopicsCompleted, 0) AS TopicsCompleted,
       ROUND(100.0 * COALESCE(Completions.TopicsCompleted, 0)
           / NULLIF(TopicCounts.Topics, 0)) AS PercentCompleted,
       Visits.LastContentVisit
FROM ClassList
JOIN Users ON Users.UserId = ClassList.UserId
JOIN OrganizationalUnits Courses ON Courses.OrgUnitId = ClassList.OrgUnitId
LEFT JOIN TopicCounts ON TopicCounts.OrgUnitId = ClassList.OrgUnitId
LEFT JOIN Visits ON Visits.UserId = ClassList.UserId AND Visits.OrgUnitId = ClassList.OrgUnitId
LEFT JOIN Completions ON Completions.UserId = ClassList.UserId AND Completions.OrgUnitId = ClassList.OrgUnitId
ORDER BY ClassList.OrgUnitId, ClassList.UserId;