SQL: Students Who Have Not Logged In Within 7 Days

Summary

With our recent move to remote instruction as a result of COVID-19, we needed a report to identify students who had not logged into Blackboard in the past 7 days. Using Microsoft Power BI, we run this report daily and share access via a Dashboard.

Data Source: Blackboard DDA on SaaS

Code

SELECT u.firstname, u.lastname, u.student_id, u.email, LEFT(cm.course_id, 5) as course_crn, cm.course_name, cm.course_id, cu.last_access_date
FROM PUBLIC.course_users cu
INNER JOIN users u
ON u.pk1 = cu.users_pk1
INNER JOIN course_main cm
ON cm.pk1 = cu.crsmain_pk1
WHERE cm.course_id LIKE '%.202020' -- Filters by Course ID
AND cu.role = 'S' -- Only Students
AND cu.row_status = '0' -- Only Active Enrollments
AND cm.row_status = '0' -- Only Active Courses
AND (cu.last_access_date is null OR cu.last_access_date < now() - interval '1 week') -- Filters never accessed and accessed more than 7 days
AND u.user_id NOT LIKE '%_previewuser' -- Removes Preview Users 
AND cu.data_src_pk1 = '986' -- Only users enrolled via the snapshot
ORDER BY u.lastname, u.firstname, u.student_id