Speedgrader and rubrics are probably tied more to assignments than courses. Some assignments might use rubrics, some assignments might be easier graded by speed grader. Some assignments are auto graded or can be. Other assignments might be peer reviewed.
I may have to edit that statement in a bit...
...down the rabbit hole I go...
update:
Apparently rubric assignments must be graded in Speedgrader, but not every assignment graded with Speedgrader has a rubric.
I know you don't have Canvas Data yet, but these conversations end up bubbling up over time and it's nice when they seem to get somewhere, especially for those reviewing them later.
This may or may not answer your questions.
Here's a query that gets the course name, and any instructor who grades an assignment. Since a course could have multiple instructors grading I thought it would be best to show that. I've also added the total number of course_assignments.
The number of rubric_assignments, the graded_assignments and the assignments graded with speedgrader, sg_assignments.
Note, that if you the only way in Canvas data to get the assignments with a rubric seems to be on submission_dim.has_rubric_assessment = true, which means if you run this query during an active term you will only be assessing rubrics against submitted assignments. If you want to assignments that have rubrics, you'll probably have have to use the API.
Likewise, graded_assignments, and sg_assigments columns are based on submissions_dim and requests_table respectively, meaning they are only available once the action has been performed. Again, if you run this query during an active term, your data is current up to the data of your data, and the activity of your users. If you run this on a previous term, you'd get more 1:1 data... however, an instructor may choose not to grade an assignment, or students might miss one, some assignments being optional as well.
Additional note about requests table.
I wrote this against our production database, which only has the requests table for the current school year.
It took 9.6 seconds, and we're just finishing up first quarter.
I also ran it against the test database where I have requests since May 2016, and ran this query for last years terms.
That one took 14 minutes.
I'm also going to invite @James , who may be the Full Stack Master of Rubrics and Data, in case he has something to add/correct.
This is slightly MSSQL flavored. I try to leave things as simple as possible, and use features that have commonly transposable functions for community examples.
Line 88 for Term IDs, you would need the full Shard/Instance ID ex: 990000000000123
SELECT
name AS course_name,
x.*
FROM
dbo.course_dim
LEFT JOIN (
SELECT
sg.course_id,
sortable_name AS instructor,
user_id,
course_assignments,
rubric_assignments,
graded_assignments,
count(url) sg_assignments
FROM (
SELECT
DISTINCT sortable_name,
user_id,
course_id,
web_application_controller,
web_application_action,
url
FROM
dbo.requests
LEFT JOIN dbo.user_dim ON (requests.user_id = user_dim.id)
WHERE
user_id IS NOT NULL
AND web_application_controller = 'gradebooks'
AND web_application_action = 'speed_grader'
AND web_application_context_type = 'Course'
AND PATINDEX('%.json?%', url) = 0
) sg
LEFT JOIN (
SELECT
course_id,
count(assignment_id) rubric_assignments
FROM (
SELECT
DISTINCT course_id,
assignment_id
FROM
dbo.submission_dim
LEFT JOIN dbo.assignment_dim ON (
submission_dim.assignment_id = assignment_dim.id
)
WHERE
submission_dim.has_rubric_assessment = 'true'
) ra
GROUP BY
course_id
) ra ON (ra.course_id = sg.course_id)
LEFT JOIN (
SELECT
course_id,
count(assignment_id) graded_assignments
FROM (
SELECT
DISTINCT course_id,
assignment_id
FROM
dbo.submission_dim
LEFT JOIN dbo.assignment_dim ON (
submission_dim.assignment_id = assignment_dim.id
)
WHERE
submission_dim.workflow_state = 'graded'
) ga
GROUP BY
course_id
) ga ON (ga.course_id = sg.course_id)
JOIN (
SELECT
course_id,
count(id) course_assignments
FROM
dbo.assignment_dim
GROUP BY
course_id
) ca ON (ca.course_id = sg.course_id)
GROUP BY
sortable_name,
user_id,
sg.course_id,
course_assignments,
rubric_assignments,
graded_assignments
) x ON (course_dim.id = x.course_id)
WHERE course_dim.enrollment_term_id IN (123,456,789)
AND x.instructor IS NOT NULL
ORDER BY
course_name,
instructor;
Sample result, current term:
course_name | course_id | instructor | user_id | course_assignments | rubric_assignments | graded_assignments | sg_assignments |
Course Name 1 | 100000001234567 | Teacher 1 | 1 | 196 | 57 | 107 | 62 |
Course Name 2 | 100000001234568 | Teacher 2 | 2 | 80 | 48 | 47 | 42 |
Course Name 3 | 100000001234569 | Teacher 3 | 3 | 184 | 46 | 82 | 53 |
Course Name 4 | 100000001234510 | Teacher 4 | 4 | 80 | 40 | 55 | 47 |
Course Name 4 | 100000001234510 | Teacher 5 | 5 | 80 | 40 | 55 | 1 |
Course Name 4 | 100000001234510 | Teacher 6 | 6 | 80 | 40 | 55 | 3 |
Course Name 5 | 100000001234511 | Teacher 7 | 7 | 182 | NULL | 55 | 83 |
Course Name 6 | 100000001234512 | Teacher 8 | 8 | 167 | NULL | 79 | 23 |
Course Name 7 | 100000001234513 | Teacher 9 | 9 | 182 | NULL | 55 | 83 |
Course Name 8 | 100000001234514 | Teacher 10 | 10 | 172 | 33 | 69 | 39 |