Filtering courses by last activity date

[molshausen]

I'm working on an archiving scheme for our past courses. As part of this, I'm interested in finding a way to generate a report featuring all the courses that have had no user access in five or more years. How might one find this out using Canvas data?

[ColinMurtaugh · Accepted solution]

The enrollments table has a last_activity_at value for each enrollee in a course, so you could use that to determine the last time anyone was active in each course and filter for courses that were not accessed in the last 5 years. 

For example: 

SELECT c.id, MAX(e.last_activity_at) AS last_activity
FROM courses c
JOIN enrollments e ON c.id = e.course_id
WHERE c.workflow_state = 'available'
GROUP BY c.id
HAVING MAX(e.last_activity_at) < now() - interval '5 years'
ORDER BY last_activity;

 --Colin

[molshausen]

Thank you, Colin!