canvas lms score

assignment-group-score.sql

with 
course as (
	select id, name, sis_source_id from courses where name like '251R%'and name like '%1학년세미나%'
),
enroll as (
	select e.id, e.course_id, e.user_id, e.sis_pseudonym_id, p.unique_id, u.name as user_name from enrollments e
	join course crs on e.course_id = crs.id
	join users u on e.user_id = u.id
	left join pseudonyms p on e.sis_pseudonym_id = p.id
	where e."type" = 'StudentEnrollment' and e.workflow_state = 'active'
),
enr_score as (
	select enr.course_id, g.id as ag_id, g.name as ag_name, enr.user_id, enr.unique_id, enr.user_name, s.unposted_final_score, s.course_score from scores s
	join enroll enr on s.enrollment_id = enr.id
	join assignment_groups g on s.assignment_group_id = g.id
	where s.workflow_state = 'active' and s.assignment_group_id is not null
)
select crs.id, crs.name, crs.sis_source_id, es.ag_name, es.unique_id, es.user_name, es.unposted_final_score, es.course_score from enr_score es
join course crs on es.course_id = crs.id
order by crs.id, es.ag_id, es.user_id
;

assignment-score.sql

with 
course as (
	select id, name, sis_source_id from courses where name like '251R%'and name like '%1학년세미나%'
),
enroll as (
	select e.id, e.course_id, e.user_id, e.sis_pseudonym_id, p.unique_id, u.name as user_name from enrollments e
	join course crs on e.course_id = crs.id
	join users u on e.user_id = u.id
	left join pseudonyms p on e.sis_pseudonym_id = p.id
	where e."type" = 'StudentEnrollment' and e.workflow_state = 'active'
),
assign as (
	select a.context_id as course_id, a.id, a.title from assignments a
	join course crs on a.context_id = crs.id
	where a.workflow_state <> 'deleted' and a.context_type = 'Course'
)
select crs.id, crs.name, crs.sis_source_id, ass.id, ass.title, enr.unique_id, enr.user_name, s.score from submissions s
join course crs on s.course_id = crs.id
JOIN enroll enr ON s.course_id = enr.course_id AND s.user_id = enr.user_id
join assign ass on s.course_id = ass.course_id and s.assignment_id = ass.id
order by s.course_id, s.assignment_id, s.user_id
;

course-score.sql

with 
course as (
	select id, name, sis_source_id from courses where name like '251R%'and name like '%1학년세미나%'
),
enroll as (
	select e.id, e.course_id, e.user_id, e.sis_pseudonym_id, p.unique_id, u.name as user_name from enrollments e
	join course crs on e.course_id = crs.id
	join users u on e.user_id = u.id
	left join pseudonyms p on e.sis_pseudonym_id = p.id
	where e."type" = 'StudentEnrollment' and e.workflow_state = 'active'
),
enr_score as (
	select enr.course_id, enr.user_id, enr.unique_id, enr.user_name, s.unposted_final_score, s.course_score from scores s
	join enroll enr on s.enrollment_id = enr.id
	where s.workflow_state = 'active' and s.assignment_group_id is null
)
select crs.id, crs.name, crs.sis_source_id, es.unique_id, es.user_name, es.unposted_final_score, es.course_score from enr_score es
join course crs on es.course_id = crs.id
order by crs.id, es.user_id
;