How do I obtain a student total activity?

Oskarp
Community Member

I am trying to get the total activity time of the students on the platform using the Canvas tables.
I have thought of obtaining all the student's actions from the REQUEST table, and calculating the interval between the first and last access at the session_id level

SELECT session_id, min(timestamp) minAccess, max(timestamp) maxAccess, datediff(minute, min(timestamp), max(timestamp)) interval
FROM requests r
WHERE user_id='XXXXXXXXXXXXXXXXXX'
AND r.course_id='XXXXXXXXXXXXXXXXXX'
GROUP BY session_id

But the total hours and minutes that I get for a student in a particular course is quite different from the total that appears in the People option of Canvas (Capture.jpg). 

Is the query I am using correct? Should I take into account other fields in the filter?
Or, do I have to use other tables?

Thank you very much in advance.

Regards.

Labels (1)
0 Likes