grade_export_report.sql

SELECT
  students.last_name AS "Student Last Name",
  students.first_name AS "Student First Name",
  students.user_canvas_id AS "Student Canvas ID",
  students.user_sis_id AS "Student SIS ID",
  course_dim.name AS "Course Name",
  course_dim.canvas_id AS "Course Canvas ID",
  course_dim.sis_source_id AS "Course SIS ID",
  course_section_dim.name AS "Section",
  course_section_dim.canvas_id AS "Section Canvas ID",
  course_section_dim.sis_source_id AS "Section SIS ID",
  enrollment_term_dim.name AS "Term",
  enrollment_term_dim.sis_source_id AS "Term SIS ID",
  COALESCE(scores.current_score, 0) AS "Current Score",
  COALESCE(scores.final_score, 0) AS "Final Score",
  account_dim.name AS "Account",
  account_dim.canvas_id AS "Account Canvas ID",
  students.enrollment_state AS "Enrollment State"
FROM
  course_dim
  INNER JOIN course_section_dim ON course_section_dim.course_id = course_dim.id
    AND course_dim.workflow_state != 'deleted'
    AND course_section_dim.workflow_state != 'deleted'
  INNER JOIN enrollment_term_dim ON enrollment_term_dim.id = course_dim.enrollment_term_id
  INNER JOIN account_dim ON account_dim.id = course_dim.account_id
  INNER JOIN ( SELECT DISTINCT
      user_dim.id AS user_id,
      user_dim.canvas_id AS user_canvas_id,
      TRIM(SPLIT_PART(user_dim.sortable_name, ',', 2)) AS first_name,
      TRIM(SPLIT_PART(user_dim.sortable_name, ',', 1)) AS last_name,
      pseudonym_dim.sis_user_id AS user_sis_id,
      enrollment_dim.id AS enrollment_id,
      enrollment_dim.user_id AS enrollment_user_id,
      enrollment_dim.course_id AS enrollment_course_id,
      enrollment_dim.workflow_state AS enrollment_state
    FROM
      user_dim
      INNER JOIN enrollment_rollup_dim ON user_dim.id = enrollment_rollup_dim.user_id
      INNER JOIN enrollment_dim ON enrollment_rollup_dim.id = enrollment_dim.id
      LEFT JOIN pseudonym_dim ON user_dim.id = pseudonym_dim.user_id
        AND pseudonym_dim.workflow_state = 'active'
        AND pseudonym_dim.sis_user_id IS NOT NULL
    WHERE
      enrollment_dim.type = 'StudentEnrollment'
      AND enrollment_dim.workflow_state NOT IN ('deleted')) AS students ON students.enrollment_course_id = course_dim.id
  LEFT JOIN (
    SELECT
      course_score_fact.enrollment_id AS enrollment_id,
      course_score_fact.course_id AS course_id,
      course_score_fact.current_score AS current_score,
      course_score_fact.final_score AS final_score
    FROM
      course_score_fact) AS scores ON students.enrollment_id = scores.enrollment_id
ORDER BY
  account_dim.name,
  enrollment_term_dim.name,
  course_dim.name