Assistance with SQL Query Extracting Student Scores For Assignments and Courses in Canvas Data 2

[MichaelBealATSU]

The goal is to create a SQL query to extract the student (prefer the student ID), the course, the assignment, and the associated grade.  I have the following query but it is not what I want or need.  I am purposefully limiting it to a particular term but need all of the courses, assignments, scores by student.   

SELECT
u.sortable_name AS student_name
,etd.name AS term_name
,cd.name AS course_name
,cd.course_code AS course_code
,count(a.title)
,sf.score AS assignment_score
FROM enrollments e
JOIN courses cd ON e.course_id = cd.id
JOIN enrollment_terms etd ON cd.enrollment_term_id = etd.id
JOIN assignments a ON cd.id = a.context_id
JOIN users u ON e.user_id = u.id
LEFT OUTER JOIN attachments b ON a.id = b.context_id
LEFT OUTER JOIN quiz_submissions sf ON b.context_id = sf.id
WHERE 1=1
AND (e.type = 'StudentEnrollment'
AND e.workflow_state = 'active')
AND (a.workflow_state = 'published')
AND (cd.course_code like '%CCPA%')
and (etd.name like '%23-24FA-MSPA1%')
group by u.sortable_name
,etd.name
,cd.name
,cd.course_code
,sf.score
 

Has anyone done something like this and if so what did your query look like?  Do you have any examples?

[stimme]

Hello @MichaelBealATSU !

The submissions table is where I'd recommend starting when you want the scores students have on each of their assignments (the quiz_submission table will not include all assignment types). Grouping the results should not be necessary, because a submissions table record represents the current attempt only. Joining the enrollments table may not be necessary, because only student users have records on the submissions table. (If you want to exclude inactive enrollments, then it probably is necessary.) To get the Student ID value, join the pseudonyms table. I also recommend selecting the points possible from the assignments table in order to calculate percentage for each score. (I'm not putting that calculation in the SQL - I save that step for Tableau.) I hope this rework of the query you shared is something you can build on.

SELECT p.sis_user_id /* "Student ID" in People tool */
, e.user_id
, s.score
, a.points_possible
, a.title
, a.id AS assignment_id
, c.id AS course_id
, c.course_code
, c.name AS course_name
, t.name AS term_name
FROM canvas.submissions s
JOIN canvas.assignments a ON a.id = s.assignment_id
JOIN canvas.users u ON u.id = s.user_id
JOIN canvas.pseudonyms p ON p.user_id = u.id
JOIN canvas.courses c ON c.id = a.context_id
JOIN canvas.enrollment_terms t ON t.id = c.enrollment_term_id
WHERE t.name LIKE '%23-24FA-MSPA1%'
AND c.course_code LIKE '%CCPA%'
AND a.workflow_state = 'published'
AND s.workflow_state != 'deleted' /* Exclude submissions associated with deleted enrollments */