I've been trying to create a custom SQL query using the configurable reports plugin but im stuck.
I want this info:
- Student name
- Distinct days the student conected to the course
- First access day to the course
- Total time connected to course
- Scorm packages completed
- Course final score
I was able to get some of the info with chatgpt help:
WITH UserLogs AS (
SELECT
u.id AS userid,
u.firstname AS first_name,
u.lastname AS last_name,
l1.timecreated AS log_time,
COALESCE(l2.timecreated, 0) AS previous_log_time,
CASE
WHEN l2.timecreated IS NULL THEN 0
ELSE TIME_TO_SEC(TIMEDIFF(l1.timecreated, l2.timecreated))
END AS time_diff
FROM
mdl_user u
LEFT JOIN
mdl_logstore_standard_log l1 ON u.id = l1.userid
LEFT JOIN
mdl_logstore_standard_log l2 ON u.id = l2.userid
AND l1.id > l2.id
WHERE
l1.courseid IS NOT NULL
),
SCORMActivities AS (
SELECT
c.userid AS userid,
cm.instance AS scorm_id
FROM
mdl_course_modules_completion c
JOIN
mdl_course_modules cm ON c.coursemoduleid = cm.id
JOIN
mdl_modules m ON cm.module = m.id
WHERE
m.name = 'scorm'
)
SELECT
U.userid,
U.first_name,
U.last_name,
COUNT(DISTINCT DATE(FROM_UNIXTIME(U.log_time))) AS distinct_days_connected,
SEC_TO_TIME(SUM(CASE WHEN U.time_diff <= 900 THEN U.time_diff ELSE 0 END)) AS total_time_connected,
COALESCE(SUM(CASE WHEN SC.scorm_id IS NOT NULL THEN 1 ELSE 0 END), 0) AS scorm_activities_count,
ROUND(AVG(Q.grade), 2) AS average_quiz_grade
FROM
UserLogs U
LEFT JOIN
SCORMActivities SC ON U.userid = SC.userid
LEFT JOIN
mdl_course_modules CM ON U.log_time >= U.previous_log_time + 900
LEFT JOIN
mdl_quiz Q ON CM.instance = Q.course
GROUP BY
U.userid, U.first_name, U.last_name
ORDER BY
U.last_name, U.first_name;
I added a timediff maximum of 15 mins as a threshold for inactivity but it doesn't work and I don't really know how to get logs info into the sql query.
Thanks in advance for any tips
