Failing students across Brightspace courses: final grades below a pass mark

-- Change 50 below to use a different passing percentage.
-- Adjusted final grades take priority over calculated final grades when valid.
WITH Settings AS (
    SELECT 50 AS PassMark
), learner_roles AS (
    SELECT DISTINCT RoleId
    FROM RoleDetails
    WHERE RoleId IS NOT NULL
      AND NULLIF(trim(CAST(DeletedBy AS VARCHAR)), '') IS NULL
      AND COALESCE(lower(trim(CAST(ShowInGrades AS VARCHAR))) IN ('true', '1'), false)
), Learners AS (
    SELECT DISTINCT UserEnrollments.OrgUnitId, UserEnrollments.UserId
    FROM UserEnrollments
    JOIN learner_roles ON learner_roles.RoleId = UserEnrollments.RoleId
), Candidates AS (
    SELECT GradeResults.OrgUnitId, GradeResults.UserId, GradeResults.GradeObjectId,
           GradeResults.PointsNumerator, GradeResults.PointsDenominator,
           ROW_NUMBER() OVER (
               PARTITION BY GradeResults.OrgUnitId, GradeResults.UserId
               ORDER BY GradeObjects.GradeObjectTypeId DESC, GradeObjects.GradeObjectId
           ) AS FinalRank
    FROM GradeResults
    JOIN GradeObjects ON GradeObjects.GradeObjectId = GradeResults.GradeObjectId
                     AND GradeObjects.OrgUnitId = GradeResults.OrgUnitId
    WHERE GradeObjects.GradeObjectTypeId IN (7, 8)
      AND COALESCE(TRY_CAST(GradeObjects.IsDeleted AS BOOLEAN), false) = false
      AND COALESCE(TRY_CAST(GradeResults.IsDeleted AS BOOLEAN), false) = false
      AND GradeResults.PointsDenominator IS NOT NULL AND GradeResults.PointsDenominator > 0
)
SELECT Courses.OrgUnitId AS CourseId, Courses.Name AS CourseName,
       Users.UserId, Users.FirstName, Users.LastName, Candidates.GradeObjectId,
       Candidates.PointsNumerator * 100.0 / Candidates.PointsDenominator AS GradePercent
FROM Candidates
JOIN Learners ON Learners.OrgUnitId = Candidates.OrgUnitId AND Learners.UserId = Candidates.UserId
JOIN Users ON Users.UserId = Candidates.UserId
JOIN OrganizationalUnits AS Courses ON Courses.OrgUnitId = Candidates.OrgUnitId
CROSS JOIN Settings
WHERE Courses.Type = 'Course Offering'
  AND COALESCE(TRY_CAST(Courses.IsDeleted AS BOOLEAN), false) = false
  AND Users.DateDeleted IS NULL AND Candidates.FinalRank = 1
  AND Candidates.PointsNumerator * 100.0 / Candidates.PointsDenominator < Settings.PassMark
ORDER BY CourseId, Users.UserId, Candidates.GradeObjectId;