percent_instructor_posts.sql

# 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
where c.shortname='DELTAK_AS_MEETING' and p.userid=a.userid;

# percent posts and total for a specified instructor in a course

select ROUND(100.0 * (SUM(CASE WHEN lower(u.email)=lower('[email protected]') 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 AND r.shortname in ('teacher','editingteacher')
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
where c.shortname='DELTAK_AS_MEETING' and p.userid=a.userid;



# Moodle same stuff in Moodle ad-hoc format

# 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 prefix_user u
left join prefix_role_assignments a on a.userid=u.id
left join prefix_role r on r.id=a.roleid
left join prefix_context x on x.id=a.contextid AND x.contextlevel=50
left join prefix_course c on c.id=x.instanceid