CD2: Tables holding scores or points for each student

[MichaelBealATSU]

I am attempting to use SQL to query the tables to identify the current score or points/points possible by student for each class and for each assignment as well as any quizzes (etc...).

If there is a specific table that maintains the current value for this that would be great however if it is derived on the fly can you provide specifics about which tables contain the individual aspects that result in the gradebook score for each student?

[stimme]

@MichaelBealATSU For the student's scores in the course, see the canvas.scores table where course_score is TRUE. For assignment points, see canvas.submissions.score. (The submissions table should also include all graded quizzes, classic and new.) For assignment points possible, see canvas.assignments.points_possible.

Note: to reproduce Canvas's calculations for the course score, you'll also need to look at assignment group drop rules & weights, as well as the excused field for submissions.

[Pete5484]

I have just started this myself today! My beginner script is below.

The inability to easily extract the final course grade is a real frustration. 

Anyway, if it's of any use I've just pulled in info up to the assignment_groups level as a first pass to figure out the relationships. As @stimme said, can then pull in assignments and submissions.

SELECT
  "canvas"."users"."id" AS "id",
  "canvas"."users"."workflow_state" AS "workflow_state",
  "canvas"."users"."sortable_name" AS "sortable_name",
  "Enrollments"."id" AS "Enrollments__id",
  "Enrollments"."workflow_state" AS "Enrollments__workflow_state",
  "Enrollments"."role_id" AS "Enrollments__role_id",
  "Enrollments"."start_at" AS "Enrollments__start_at",
  "Enrollments"."end_at" AS "Enrollments__end_at",
  "Enrollments"."course_id" AS "Enrollments__course_id",
  "Enrollments"."completed_at" AS "Enrollments__completed_at",
  "Enrollments"."course_section_id" AS "Enrollments__course_section_id",
  "Enrollments"."last_attended_at" AS "Enrollments__last_attended_at",
  "Enrollments"."type" AS "Enrollments__type",
  "Courses"."id" AS "Courses__id",
  "Courses"."workflow_state" AS "Courses__workflow_state",
  "Courses"."grading_standard_id" AS "Courses__grading_standard_id",
  "Courses"."course_code" AS "Courses__course_code",
  "Courses"."enrollment_term_id" AS "Courses__enrollment_term_id",
  "Courses"."name" AS "Courses__name",
  "Grading Standards"."version" AS "Grading Standards__version",
  "Grading Standards"."workflow_state" AS "Grading Standards__workflow_state",
  "Grading Standards"."title" AS "Grading Standards__title",
  "Grading Standards"."data" AS "Grading Standards__data",
  "Scores"."id" AS "Scores__id",
  "Scores"."updated_at" AS "Scores__updated_at",
  "Scores"."workflow_state" AS "Scores__workflow_state",
  "Scores"."assignment_group_id" AS "Scores__assignment_group_id",
  "Scores"."enrollment_id" AS "Scores__enrollment_id",
  "Scores"."current_score" AS "Scores__current_score",
  "Scores"."final_score" AS "Scores__final_score",
  "Scores"."course_score" AS "Scores__course_score",
  "Scores"."unposted_current_score" AS "Scores__unposted_current_score",
  "Scores"."unposted_final_score" AS "Scores__unposted_final_score",
  "Scores"."override_score" AS "Scores__override_score",
  "Assignment Groups"."id" AS "Assignment Groups__id",
  "Assignment Groups"."name" AS "Assignment Groups__name",
  "Assignment Groups"."workflow_state" AS "Assignment Groups__workflow_state",
  "Assignment Groups"."group_weight" AS "Assignment Groups__group_weight",
  "Assignment Groups"."position" AS "Assignment Groups__position",
  "Assignment Groups"."rules" AS "Assignment Groups__rules",
  "Course Sections"."id" AS "Course Sections__id",
  "Course Sections"."name" AS "Course Sections__name",
  "Course Sections"."workflow_state" AS "Course Sections__workflow_state",
  "Grading Standards_2"."id" AS "Grading Standards_2__id",
  "Grading Standards_2"."version" AS "Grading Standards_2__version",
  "Grading Standards_2"."context_code" AS "Grading Standards_2__context_code",
  "Grading Standards_2"."user_id" AS "Grading Standards_2__user_id",
  "Grading Standards_2"."created_at" AS "Grading Standards_2__created_at",
  "Grading Standards_2"."updated_at" AS "Grading Standards_2__updated_at",
  "Grading Standards_2"."workflow_state" AS "Grading Standards_2__workflow_state",
  "Grading Standards_2"."context_id" AS "Grading Standards_2__context_id",
  "Grading Standards_2"."context_type" AS "Grading Standards_2__context_type",
  "Grading Standards_2"."migration_id" AS "Grading Standards_2__migration_id",
  "Grading Standards_2"."title" AS "Grading Standards_2__title",
  "Grading Standards_2"."data" AS "Grading Standards_2__data"
FROM
  "canvas"."users"
 
LEFT JOIN "canvas"."enrollments" AS "Enrollments" ON "canvas"."users"."id" = "Enrollments"."user_id"
  LEFT JOIN "canvas"."courses" AS "Courses" ON "Enrollments"."course_id" = "Courses"."id"
  LEFT JOIN "canvas"."grading_standards" AS "Grading Standards" ON "Courses"."grading_standard_id" = "Grading Standards"."id"
  LEFT JOIN "canvas"."scores" AS "Scores" ON "Enrollments"."id" = "Scores"."enrollment_id"
  LEFT JOIN "canvas"."assignment_groups" AS "Assignment Groups" ON "Scores"."assignment_group_id" = "Assignment Groups"."id"
  LEFT JOIN "canvas"."course_sections" AS "Course Sections" ON "Enrollments"."course_section_id" = "Course Sections"."id"
  LEFT JOIN "canvas"."grading_standards" AS "Grading Standards_2" ON "canvas"."users"."id" = "Grading Standards_2"."user_id"
WHERE
  (
    "Courses"."workflow_state" = CAST('available' AS "canvas"."courses__workflow_state")
  )
 
   AND (
    "Enrollments"."type" = CAST(
      'StudentEnrollment' AS "canvas"."enrollments__type"
    )
  )
  AND (
    (
      "Enrollments"."workflow_state" = CAST('active' AS "canvas"."enrollments__workflow_state")
    )
   
    OR (
      "Enrollments"."workflow_state" = CAST(
        'completed' AS "canvas"."enrollments__workflow_state"
      )
    )
    OR (
      "Enrollments"."workflow_state" = CAST(
        'inactive' AS "canvas"."enrollments__workflow_state"
      )
    )
  )
  AND (
    "Scores"."workflow_state" = CAST('active' AS "canvas"."scores__workflow_state")
  )
  AND (
    ("Assignment Groups"."group_weight" <> 0)
    OR ("Assignment Groups"."group_weight" IS NULL)
  )
ORDER BY
  "canvas"."users"."sortable_name" ASC,
  "Courses"."name" ASC,
  "Enrollments"."id" ASC,
  "Course Sections"."name" DESC,
  "Scores"."course_score" DESC,
  "Assignment Groups"."position" ASC