SQL: List All Courses Accessed by a System Role in the Last 7 Days

Summary

This query lists all courses and organizations that have been accessed by a user with a specific system role in the last 7 days.

Data Source: Blackboard DDA on SaaS

Code

SELECT DISTINCT u.lastname, u.firstname, cm.course_name, 
 CASE
    WHEN cm.service_level = 'C' THEN 'Organization'
    WHEN cm.service_level = 'F' THEN 'Course'
    ELSE 'Other'
 END as item_type
FROM PUBLIC.activity_accumulator aa
LEFT JOIN PUBLIC.users u
ON aa.user_pk1 = u.pk1
LEFT JOIN PUBLIC.course_main cm
ON aa.course_pk1 = cm.pk1 
WHERE u.system_role = 'HELPDESK_SUPPORT' --Enter Your System Role Here
AND u.row_status = '0' --Filters Only Active Users
AND aa.event_type = 'COURSE_ACCESS' --Filters Only Course Access
AND aa.timestamp < current_date - interval '7' day --Filters Last 7 Days
ORDER BY u.lastname, u.firstname