Brightspace accreditation reporting: ABET student outcomes data

WITH Settings AS (
  SELECT 100 AS CourseId
)
SELECT c.Name AS Course,c.OrgUnitId AS CourseId,o.Notation,o.OutcomeId,o.Description,
       a.ObjectType,a.ObjectId
FROM OutcomesSetCourse s
JOIN OrganizationalUnits c ON c.OrgUnitId=s.OrgUnitId
JOIN Settings p ON p.CourseId=c.OrgUnitId
JOIN OutcomesInRegistries r ON r.RegistryId=s.RegistryId
JOIN OutcomeDetails o ON o.OutcomeId=r.OutcomeId
LEFT JOIN OutcomesAlignedToToolObjects a
  ON a.RegistryId=r.RegistryId AND a.OutcomeId=r.OutcomeId
  AND TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
WHERE TRY_CAST(s.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(c.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(r.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(o.IsDeleted AS BOOLEAN) IS NOT TRUE
ORDER BY c.Name,c.OrgUnitId,o.Notation,o.OutcomeId,a.ObjectType,a.ObjectId

WITH Settings AS (
  SELECT TIMESTAMPTZ '2026-09-01 00:00:00+00' AS StartUTC,
         TIMESTAMPTZ '2027-01-01 00:00:00+00' AS EndUTC,
         100 AS CourseId,
         ['Proficient', 'Advanced'] AS TargetLevels
), Evaluations AS (
  SELECT d.*,s.OrgUnitId,c.Name AS Course,o.Notation,
         l.Name AS Level,l.ScaleLevelId
  FROM OutcomesDemonstrations d
  JOIN OutcomesSetCourse s ON s.RegistryId=d.RegistryId
  JOIN OrganizationalUnits c ON c.OrgUnitId=s.OrgUnitId
  JOIN Settings p ON p.CourseId=c.OrgUnitId
  JOIN OutcomesInRegistries r ON r.RegistryId=d.RegistryId AND r.OutcomeId=d.OutcomeId
  JOIN OutcomeDetails o ON o.OutcomeId=r.OutcomeId
  JOIN OutcomesScaleLevelDefinition l
    ON l.ScaleLevelId=COALESCE(d.ExplicitlyEnteredScaleLevelId,d.AutomaticallyGeneratedScaleLevelId)
  JOIN OutcomesScaleDefinition z ON z.ScaleId=l.ScaleId
  WHERE d.AlignedObjectType IN (1, 2, 3)
    AND d.AssessedDate>=p.StartUTC AND d.AssessedDate<p.EndUTC
    AND TRY_CAST(d.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(s.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(c.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(r.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(o.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(z.IsDeleted AS BOOLEAN) IS NOT TRUE
), NamedEvaluations AS (
  SELECT e.*,a.Name AS Assessment,a.DropboxId AS AssessmentId
  FROM Evaluations e JOIN AssignmentSummary a
    ON e.AlignedObjectType=2 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=a.DropboxId
    AND e.OrgUnitId=a.OrgUnitId
  WHERE TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
  UNION ALL
  SELECT e.*,t.Name AS Assessment,t.TopicId AS AssessmentId
  FROM Evaluations e JOIN DiscussionTopics t
    ON e.AlignedObjectType=1 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=t.TopicId
    AND e.OrgUnitId=t.OrgUnitId
  WHERE TRY_CAST(t.IsDeleted AS BOOLEAN) IS NOT TRUE
  UNION ALL
  SELECT e.*,q.QuizName AS Assessment,q.QuizId AS AssessmentId
  FROM Evaluations e JOIN QuizAttempts a
    ON e.AlignedObjectType=3 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=a.AttemptId
    AND e.OrgUnitId=a.OrgUnitId AND e.AssessedUserId=a.UserId
  JOIN QuizObjects q ON q.QuizId=a.QuizId AND q.OrgUnitId=a.OrgUnitId
  WHERE TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
)
SELECT MIN(e.Notation) AS Notation,e.OutcomeId, MIN(e.Course) AS Course,e.OrgUnitId AS CourseId,
       MIN(e.Assessment) AS Assessment,e.AlignedObjectType AS AssessmentType,e.AssessmentId,
       MIN(e.Level) AS Level,e.ScaleLevelId,
       COUNT(DISTINCT e.AssessedUserId) AS Learners,
       COUNT(*) AS Evaluations,
       COUNT(DISTINCT CASE WHEN list_contains(p.TargetLevels,e.Level) THEN e.AssessedUserId END) AS MeetingTarget
FROM NamedEvaluations e CROSS JOIN Settings p
GROUP BY e.OutcomeId,e.OrgUnitId,e.AlignedObjectType,e.AssessmentId,e.ScaleLevelId
ORDER BY Notation, OutcomeId, Course, CourseId,
         Assessment, AssessmentType, AssessmentId, Level, ScaleLevelId

WITH Settings AS (
  SELECT TIMESTAMPTZ '2026-09-01 00:00:00+00' AS StartUTC,
         TIMESTAMPTZ '2027-01-01 00:00:00+00' AS EndUTC,
         100 AS CourseId
)
SELECT MIN(o.Notation) AS Notation,o.OutcomeId, MIN(c.Name) AS Course,c.OrgUnitId AS CourseId,
       MIN(l.Name) AS Level,l.ScaleLevelId,
       COUNT(DISTINCT d.AssessedUserId) AS Learners,
       COUNT(*) AS Checkpoints,
       COUNT(DISTINCT CASE WHEN TRY_CAST(k.HasMetAchievementThreshold AS BOOLEAN) IS TRUE THEN d.AssessedUserId END) AS ThresholdMet
FROM OutcomesDemonstrations d
JOIN OutcomesAssessedCheckpoints k
  ON CAST(d.AlignedObjectId AS VARCHAR)=CAST(k.CheckpointId AS VARCHAR)
  AND CAST(d.DemonstrationId AS VARCHAR)=CAST(k.DemonstrationId AS VARCHAR)
JOIN OutcomesSetCourse s ON s.RegistryId=d.RegistryId
JOIN OrganizationalUnits c ON c.OrgUnitId=s.OrgUnitId
JOIN Settings p ON p.CourseId=c.OrgUnitId
JOIN OutcomesInRegistries r ON r.RegistryId=d.RegistryId AND r.OutcomeId=d.OutcomeId
JOIN OutcomeDetails o ON o.OutcomeId=r.OutcomeId
JOIN OutcomesScaleLevelDefinition l
  ON l.ScaleLevelId=COALESCE(d.ExplicitlyEnteredScaleLevelId,d.AutomaticallyGeneratedScaleLevelId)
JOIN OutcomesScaleDefinition z ON z.ScaleId=l.ScaleId
WHERE d.AlignedObjectType=4
  AND d.AssessedDate>=p.StartUTC AND d.AssessedDate<p.EndUTC
  AND TRY_CAST(d.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(k.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(s.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(c.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(r.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(o.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(z.IsDeleted AS BOOLEAN) IS NOT TRUE
GROUP BY o.OutcomeId,c.OrgUnitId,l.ScaleLevelId
ORDER BY Notation, OutcomeId, Course, CourseId, Level, ScaleLevelId

WITH Settings AS (
  SELECT TIMESTAMPTZ '2026-09-01 00:00:00+00' AS StartUTC,
         TIMESTAMPTZ '2027-01-01 00:00:00+00' AS EndUTC
), Completed AS (
  SELECT a.* FROM RubricAssessment a
  WHERE TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(a.IsCompleted AS BOOLEAN) IS TRUE
), Matches AS (
  SELECT a.AssessmentId,a.RubricId, COUNT(*) AS Matches
  FROM Completed a GROUP BY a.AssessmentId,a.RubricId
)
SELECT MIN(c.Name) AS Course,c.OrgUnitId AS CourseId, MIN(o.Notation) AS Notation,o.OutcomeId,
       MIN(b.Name) AS Criterion,b.CriterionId, MIN(l.Name) AS RubricLevel,l.LevelId,
       COUNT(DISTINCT a.UserId) AS Learners, COUNT(DISTINCT a.AssessmentId) AS Assessments,
       CAST(AVG(x.Score) AS DOUBLE) AS MeanScore
FROM Completed a
JOIN Matches m ON m.AssessmentId=a.AssessmentId AND m.RubricId=a.RubricId AND m.Matches=1
JOIN RubricAssessmentCriteria x ON x.AssessmentId=a.AssessmentId AND x.RubricId=a.RubricId
JOIN RubricObjects z ON z.RubricId=a.RubricId
JOIN RubricObjectCriteria b ON b.RubricId=x.RubricId AND b.CriterionId=x.CriterionId
JOIN RubricObjectLevels l ON l.RubricId=x.RubricId AND l.LevelId=x.LevelAchievedId
JOIN OutcomesRubricAlignments v ON v.RubricId=x.RubricId AND v.CriterionId=x.CriterionId
JOIN OutcomesSetCourse s ON s.RegistryId=v.RegistryId AND s.OrgUnitId=a.OrgUnitId
JOIN OutcomesInRegistries r ON r.RegistryId=s.RegistryId AND r.OutcomeId=v.OutcomeId
JOIN OutcomeDetails o ON o.OutcomeId=r.OutcomeId
JOIN OrganizationalUnits c ON c.OrgUnitId=a.OrgUnitId
CROSS JOIN Settings p
WHERE a.AssessmentDate>=p.StartUTC AND a.AssessmentDate<p.EndUTC
  AND TRY_CAST(z.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(b.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(l.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(v.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(s.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(r.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(o.IsDeleted AS BOOLEAN) IS NOT TRUE
  AND TRY_CAST(c.IsDeleted AS BOOLEAN) IS NOT TRUE
GROUP BY c.OrgUnitId,o.OutcomeId,b.CriterionId,l.LevelId
ORDER BY Course, CourseId, Notation, OutcomeId, Criterion, CriterionId, RubricLevel, LevelId

WITH Settings AS (
  SELECT TIMESTAMPTZ '2026-09-01 00:00:00+00' AS StartUTC,
         TIMESTAMPTZ '2027-01-01 00:00:00+00' AS EndUTC,
         [100, 101] AS ProgramCourseIds
), Evaluations AS (
  SELECT d.*,s.OrgUnitId,o.Notation,l.Name AS Level
  FROM OutcomesDemonstrations d
  JOIN OutcomesSetCourse s ON s.RegistryId=d.RegistryId
  JOIN OrganizationalUnits c ON c.OrgUnitId=s.OrgUnitId
  JOIN Settings p ON list_contains(p.ProgramCourseIds,c.OrgUnitId)
  JOIN OutcomesInRegistries r ON r.RegistryId=d.RegistryId AND r.OutcomeId=d.OutcomeId
  JOIN OutcomeDetails o ON o.OutcomeId=r.OutcomeId
  JOIN OutcomesScaleLevelDefinition l
    ON l.ScaleLevelId=COALESCE(d.ExplicitlyEnteredScaleLevelId,d.AutomaticallyGeneratedScaleLevelId)
  JOIN OutcomesScaleDefinition z ON z.ScaleId=l.ScaleId
  WHERE d.AlignedObjectType IN (1, 2, 3)
    AND d.AssessedDate>=p.StartUTC AND d.AssessedDate<p.EndUTC
    AND TRY_CAST(d.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(s.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(c.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(r.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(o.IsDeleted AS BOOLEAN) IS NOT TRUE
    AND TRY_CAST(z.IsDeleted AS BOOLEAN) IS NOT TRUE
), NamedEvaluations AS (
  SELECT e.*
  FROM Evaluations e JOIN AssignmentSummary a
    ON e.AlignedObjectType=2 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=a.DropboxId
    AND e.OrgUnitId=a.OrgUnitId
  WHERE TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
  UNION ALL
  SELECT e.*
  FROM Evaluations e JOIN DiscussionTopics t
    ON e.AlignedObjectType=1 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=t.TopicId
    AND e.OrgUnitId=t.OrgUnitId
  WHERE TRY_CAST(t.IsDeleted AS BOOLEAN) IS NOT TRUE
  UNION ALL
  SELECT e.*
  FROM Evaluations e JOIN QuizAttempts a
    ON e.AlignedObjectType=3 AND TRY_CAST(e.AlignedObjectId AS BIGINT)=a.AttemptId
    AND e.OrgUnitId=a.OrgUnitId AND e.AssessedUserId=a.UserId
  JOIN QuizObjects q ON q.QuizId=a.QuizId AND q.OrgUnitId=a.OrgUnitId
  WHERE TRY_CAST(a.IsDeleted AS BOOLEAN) IS NOT TRUE
)
SELECT u.UserId, MIN(u.FirstName || ' ' || u.LastName) AS Learner,
       e.OutcomeId, MIN(e.Notation) AS Notation,
       COUNT(DISTINCT e.OrgUnitId) AS Courses, COUNT(*) AS Demonstrations,
       arg_max(e.Level, (e.AssessedDate,e.DemonstrationId)) AS LatestLevel
FROM NamedEvaluations e JOIN Users u ON u.UserId=e.AssessedUserId
WHERE u.DateDeleted IS NULL
GROUP BY u.UserId,e.OutcomeId
ORDER BY u.UserId,e.OutcomeId