SELECT 
   course_dim.name,
   account_dim.name AS School,
   assignment_dim.title AS "Assessment Name",
   user_dim.NAME AS "Student Name",
   ES.workflow_state AS "Course Enrollment Status",
   pseudonym_dim.sis_user_id AS "SIS ID",
   pseudonym_dim.unique_name AS "Email",
   pseudonym_dim.last_request_at AS "Last Login",
   CASE WHEN submission_dim.grade is NULL THEN 'First Submission' WHEN submission_dim.grade = 'Not Yet Satisfactory' THEN 'ReSubmission' END,
   submission_dim.submitted_at AS "Submitted At",
   DATE_PART('day', (Now() - submission_dim.submitted_at)) AS "Days since Submitted",
   assignment_dim.due_at AS "Assessment Due Date",
   DATE_PART('day',(assignment_dim.due_at - submission_dim.submitted_at)) AS "Days since/to Due Date",
   CONCAT('https://YOURINSITUTION.instructure.com/courses/', course_dim.canvas_id, '/gradebook/speed_grader?assignment_id=', assignment_dim.canvas_id, '&student_id=', user_dim.canvas_id) AS "SpeedGrader Link",
   (SELECT string_agg(DISTINCT User_dim.name, ', ')
FROM (Enrollment_dim INNER JOIN Course_dim as cd ON Enrollment_dim.course_id = cd.id) INNER JOIN User_dim ON Enrollment_dim.user_id = User_dim.id
WHERE Enrollment_dim.type = 'TeacherEnrollment' and cd.name = course_dim.name
GROUP BY cd.name)

FROM
   enrollment_dim as ES
   INNER JOIN
      (
         pseudonym_dim 
         INNER JOIN
            (
(((course_dim 
               INNER JOIN
                  submission_fact 
                  ON course_dim.id = submission_fact.course_id) 
               INNER JOIN
                  assignment_dim 
                  ON submission_fact.assignment_id = assignment_dim.id) 
               INNER JOIN
                  submission_dim 
                  ON submission_fact.submission_id = submission_dim.id) 
               INNER JOIN
                  user_dim 
                  ON submission_fact.user_id = user_dim.id
            )
            ON pseudonym_dim.user_id = user_dim.id
      )
      ON ( ES.course_id = course_dim.id ) 
      AND 
      (
         ES.user_id = user_dim.id 
      )
	  INNER JOIN account_dim ON course_dim.account_id = account_dim.id 
WHERE
   ES.type = 'StudentEnrollment' 
   AND
 	ES.workflow_state != 'completed'
    AND
   pseudonym_dim.sis_user_id IS NOT NULL
   AND 
   (
      submission_dim.workflow_state = 'submitted' 
      AND NOT submission_dim.workflow_state = 'deleted'
	  AND NOT submission_dim.workflow_state = 'graded'
   )