Analytics and reporting

I needed the same report - and I needed all the sub categories too. Here's the SQL i used:

#active users in a category, including its sub categores

SELECT COUNT(DISTINCT l.userid) as active
FROM mdl_course as c
JOIN mdl_context AS ctx ON ctx.instanceid=c.id
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user_lastaccess as l ON ra.userid = l.userid
JOIN mdl_course_categories AS cats ON c.category = cats.id
WHERE c.category=cats.id AND ( 
 cats.path LIKE '%/$NUM/%'
 OR cats.path LIKE '%/$NUM'
) 
AND ra.roleid=5 AND ctx.contextlevel=50 #change role to 3 for teachers, 4 for non editing teachers and 5 for students
AND l.timeaccess > (unix_timestamp() - ((60*60*24)*$NUM_OF_DAYS)) 

Replace '$NUM' with the id of your category
Replace '$NUM_OF_DAYS with a figure (eg: 28) to see how many users have been active over that time.

You cna also change: 

ra.roleid=

To another number - 3 is TEACHERS, 4 is NON-EDITING TEACHERS & 5 is STUDENTS

Your filter will need to incorporate courses just in your course category. Something along these lines:

mdl_course AS c

.

.

.

JOIN mdl_category AS cat ON cat.id=c.category

WHERE cat.id=277