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