mkolb

# percent posts and total for instructors in a course

select ROUND(100.0 * (SUM(CASE WHEN r.shortname IN ('teacher', 'editingteacher') THEN 1 ELSE 0 END) / (1.0 * COUNT(p.id))), 1) as percent_total, count (p.id) as total
from mdl_user u
left join mdl_role_assignments a on a.userid=u.id
left join mdl_role r on r.id=a.roleid
left join mdl_context x on x.id=a.contextid AND x.contextlevel=50
left join mdl_course c on c.id=x.instanceid
left join mdl_forum_discussions d on d.course=c.id
left join mdl_forum_posts p on p.discussion=d.id