Last date of attendance for withdrawn students 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')
),
Settings AS (
    SELECT DATE '2025-09-01' AS FirstDay,
           DATE '2025-12-31' AS LastDay
), Actions AS (
    SELECT UserId, OrgUnitId, Action, EnrollmentDate,
           ROW_NUMBER() OVER (PARTITION BY UserId, OrgUnitId
               ORDER BY EnrollmentDate DESC,
                        CASE WHEN Action = 'Enroll' THEN 0 ELSE 1 END, LogId DESC) AS Position
    FROM EnrollmentsAndWithdrawals
), Enrollments AS (
    SELECT UserId, OrgUnitId, RoleId,
           ROW_NUMBER() OVER (PARTITION BY UserId, OrgUnitId
               ORDER BY EnrollmentDate DESC, LogId DESC) AS Position
    FROM EnrollmentsAndWithdrawals
    WHERE Action = 'Enroll'
), Withdrawals AS (
    SELECT Actions.UserId, Actions.OrgUnitId, Actions.EnrollmentDate AS WithdrawnOn
    FROM Actions
    JOIN Enrollments ON Enrollments.UserId = Actions.UserId AND Enrollments.OrgUnitId = Actions.OrgUnitId
    JOIN learner_roles ON learner_roles.RoleId = Enrollments.RoleId
    CROSS JOIN Settings
    WHERE Actions.Position = 1 AND Enrollments.Position = 1 AND Actions.Action = 'Unenroll'
      AND CAST(Actions.EnrollmentDate AS DATE) BETWEEN Settings.FirstDay AND Settings.LastDay
), CourseVisits AS (
    SELECT UserId, OrgUnitId, CAST(MAX(DayAccessed) AS DATE) AS LastCourseAccess
    FROM CourseAccess
    GROUP BY UserId, OrgUnitId
), Submissions AS (
    SELECT SubmitterId AS UserId, OrgUnitId, MAX(LastSubmissionDate) AS LastSubmission
    FROM AssignmentSubmissions
    WHERE SubmitterType = 'User'
      AND COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
    GROUP BY SubmitterId, OrgUnitId
), Quizzes AS (
    SELECT UserId, OrgUnitId, MAX(TimeCompleted) AS LastQuizAttempt
    FROM QuizAttempts
    WHERE COALESCE(TRY_CAST(IsDeleted AS BOOLEAN), false) = false
    GROUP BY UserId, OrgUnitId
)
SELECT DISTINCT W.UserId, Users.FirstName, Users.LastName, W.OrgUnitId,
       Courses.Name AS CourseName, W.WithdrawnOn,
       CourseVisits.LastCourseAccess, Submissions.LastSubmission, Quizzes.LastQuizAttempt
FROM Withdrawals W
JOIN Users ON Users.UserId = W.UserId
JOIN OrganizationalUnits Courses ON Courses.OrgUnitId = W.OrgUnitId
LEFT JOIN CourseVisits ON CourseVisits.UserId = W.UserId AND CourseVisits.OrgUnitId = W.OrgUnitId
LEFT JOIN Submissions ON Submissions.UserId = W.UserId AND Submissions.OrgUnitId = W.OrgUnitId
LEFT JOIN Quizzes ON Quizzes.UserId = W.UserId AND Quizzes.OrgUnitId = W.OrgUnitId
WHERE Courses.Type = 'Course Offering'
ORDER BY W.UserId, W.OrgUnitId;