Query to get approximate time spent in a course based on the difference between the first view and the last submission recorded in the log

gistfile1.sql

#
# IMPORTANT:
# This query is formatted to work with Moodle's Configurable Reports plugin
# https://moodle.org/plugins/view.php?plugin=block_configurable_reports
#

select 
	u.username "User",  
	c.shortname "Course",
	from_unixtime(viewed.first) "First View",
	from_unixtime(completed.last) "Last Submission",
	round(((completed.last - viewed.first) /60 /60 /24), 2) "Difference (in days)",
	gr.finalgrade "Course Grade"
from 
	( select 	
		userid,
		course courseid,
		min(time) first
		from {log} 
		where action in ("view") 
		group by userid,course 
	) viewed 
	join (select 
		g.userid, 
		i.courseid, 
		max( if(
			g.timemodified is not null, 
			g.timemodified, 
			g.timecreated
		)) last 
		from 
			{grade_items} i 
			join {grade_grades} g on i.id = g.itemid 
		where g.timecreated is not null 
		group by i.courseid, g.userid
	) completed on viewed.userid = completed.userid
		and viewed.courseid = completed.courseid
	join {user} u on u.id = viewed.userid
	join {course} c on c.id = viewed.courseid
	join {grade_items} gi on gi.itemtype="course" 
		and gi.courseid = c.id
	left outer join {grade_grades} gr on gr.itemid = gi.id
		and gr.userid=u.id
	
	# Limit to users assigned the 'student' role in a course
	join ( select 
		x.instanceid courseid,
		ra.userid userid
		from 
			{role_assignments} ra
			join {role} r on r.id = ra.roleid 
			join {context} x on x.id = ra.contextid
		where
			x.contextlevel=50
			and r.name="Student"
		group by ra.userid, x.instanceid
	) course_students on course_students.userid=u.id 
		and course_students.courseid=c.id
		
	# ...and not assigned anything else (NULL here)
	left outer join ( select
		x.instanceid courseid,
		ra.userid userid
		from 
			{role_assignments} ra
			join {role} r on r.id = ra.roleid 
			join {context} x on x.id = ra.contextid
		where
			x.contextlevel=50
			and r.name!="Student"
		group by ra.userid,x.instanceid		
	) course_students_plus on course_students_plus.userid=u.id 
		and course_students_plus.courseid=c.id
		
	# ...and not assigned a system-level role (NULL here)
	left outer join ( select 
		ra.userid userid
		from 
			{role_assignments} ra
			join {role} r on r.id = ra.roleid 
			join {context} x on x.id = ra.contextid
		where
			x.contextlevel=10
	) system_role_users on system_role_users.userid = u.id
where
	completed.last is not NULL 
	and course_students_plus.userid is NULL
	and system_role_users.userid is NULL
	and gr.finalgrade is not NULL
	
	# Moodle interactive filters
	%%FILTER_COURSES:c.id%% 
	%%FILTER_STARTTIME:viewed.first:>%% 
	%%FILTER_ENDTIME:completed.last:<%% 
order by u.username, c.shortname