Activity report in Moodle for a course shows Activity, Views and Last access. We require this data to be broken down into groups present in the course. How many views were made to an activity by the unique users present in a particular group.
For this we have managed to write the below query. The problem here is that the users which are not present in any course groups are not accounted for! why is that? there is a Left join which should instruct SQL to also pick records which are not present in any groups.
SELECT ug.groupid, grps.name, slog.contextinstanceid as cmid, COUNT('x') AS numviews, COUNT(DISTINCT slog.userid) AS distinctusers, MAX(slog.timecreated) AS lasttime
FROM prefix_logstore_standard_log AS slog
LEFT JOIN prefix_groups_members AS ug ON ug.userid = slog.userid
INNER JOIN prefix_groups AS grps ON grps.id=ug.groupid
WHERE grps.courseid = 2156 AND slog.courseid = 2156 AND slog.anonymous = 0 AND slog.crud = 'r' AND slog.contextlevel = 70
GROUP BY slog.contextinstanceid, ug.groupid