This is the final Query I ended up with, it works but had to be adjusted. The main reason for the error messages, as I understand it, was the time it took the server to reply. So essentially the query returned too large a set, the server timed out and returned an error. A workaround was to break it into separate groups using courses ids. We took all 300+ courses and divided them into groups of about 40, and the server started to return results.
This query also contains a 'hits per student' column which uses a subquery to divide the number of hits by the number of students on the course., It makes the query a bit large and it would be great to optimise that. Nevertheless, the query works well and returns results almost identical to the ones that come from Moodle's native Statistics and Reports.
#################################
SELECT category, cc.description AS 'Description', l.courseid AS 'courseId', c.fullname AS 'Course Name', DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m') 'Month', COUNT( l.userid) AS UsersHits, (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra
JOIN mdl_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students, (COUNT( l.userid) DIV (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra
JOIN mdl_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) +1) as 'Hits per student'
FROM mdl_logstore_standard_log AS l
INNER JOIN mdl_course AS c ON l.courseid = c.id
JOIN mdl_role_assignments AS ra on ra.userid = l.userid
JOIN mdl_context AS ctx on ra.contextid = ctx.id
JOIN mdl_course_categories AS cc on cc.id = c.category
WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2019-04-23 00:00:00' AND '2019-05-25 00:00:00' #-- half term dates ----
AND ra.roleid = 5
AND ctx.contextlevel = '50'
AND ctx.instanceid = c.id
AND c.id IN (886, 451, 309, 1111, 803, 834, 570, 616, 318, 1039, 261, 937, 1129, 938, 296, 939, 896, 310, 1114, 805, 1062, 1063, 566, 942, 1124, 940, 313, 941, 1087, 252, 1146, 1094, 954, 850, 319, 777, 636, 678, 928, 55, 1206, 776, 773, 775) #--sample courses id's here
GROUP BY courseId
ORDER by 'Hits per student' ASC