/* All SQL is used to construct views within IU's Denodo Data Virtualization environment; these views were developed under Canvas Data 1 Schema */ /* Gets Student Enrollments */ /* Returns 3,227 observations */ CREATE OR REPLACE VIEW stdnt_enrollments FOLDER = '/wong22-23' AS SELECT enrollment_dim.id AS id, enrollment_dim.canvas_id AS canvas_id, enrollment_dim.root_account_id AS root_account_id, enrollment_dim.course_section_id AS course_section_id, enrollment_dim.role_id AS role_id, enrollment_dim.type AS type, enrollment_dim.workflow_state AS workflow_state, enrollment_dim.created_at AS created_at, enrollment_dim.updated_at AS updated_at, enrollment_dim.start_at AS start_at, enrollment_dim.end_at AS end_at, enrollment_dim.completed_at AS completed_at, enrollment_dim.self_enrolled AS self_enrolled, enrollment_dim.sis_source_id AS sis_source_id, enrollment_dim.course_id AS course_id, enrollment_dim.user_id AS user_id, enrollment_dim.last_activity_at AS last_activity_at FROM ((canvas_warehouse_sandbox.course_dim AS course_dim INNER JOIN canvas_warehouse_sandbox.course_section_dim AS course_section_dim ON course_dim.id = course_section_dim.course_id ) INNER JOIN canvas_warehouse_sandbox.enrollment_dim AS enrollment_dim ON course_section_dim.id = enrollment_dim.course_section_id ) INNER JOIN canvas_warehouse_sandbox.enrollment_term_dim AS enrollment_term_dim ON course_dim.enrollment_term_id = enrollment_term_dim.id WHERE (enrollment_term_dim.sis_source_id in (4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198) AND course_dim.sis_source_id like '%BL-INFO-I308%' AND enrollment_dim.workflow_state = 'active' AND enrollment_dim.type = 'StudentEnrollment') /* Get group enrollments for Canvas groups */ /* Returns 3,456 observations */ CREATE OR REPLACE VIEW stdnt_enrollments_w_groups FOLDER = '/wong22-23' AS SELECT DISTINCT stdnt_enrollments.id AS id, stdnt_enrollments.canvas_id AS canvas_id, stdnt_enrollments.root_account_id AS root_account_id, stdnt_enrollments.course_section_id AS course_section_id, stdnt_enrollments.role_id AS role_id, stdnt_enrollments.type AS type, stdnt_enrollments.workflow_state AS workflow_state, stdnt_enrollments.created_at AS created_at, stdnt_enrollments.updated_at AS updated_at, stdnt_enrollments.start_at AS start_at, stdnt_enrollments.end_at AS end_at, stdnt_enrollments.completed_at AS completed_at, stdnt_enrollments.self_enrolled AS self_enrolled, stdnt_enrollments.sis_source_id AS sis_source_id, stdnt_enrollments.course_id AS course_id, stdnt_enrollments.user_id AS user_id, stdnt_enrollments.last_activity_at AS last_activity_at, group_dim.name AS group_name, group_dim.id AS group_id, group_dim.created_at AS group_created_at, group_dim.updated_at AS group_updated_at, group_dim.deleted_at AS deleted_at, group_dim.workflow_state AS group_workflow_state FROM (stdnt_enrollments AS stdnt_enrollments INNER JOIN canvas_warehouse.group_membership_fact AS group_membership_fact ON (stdnt_enrollments.user_id = group_membership_fact.user_id AND stdnt_enrollments.course_id = group_membership_fact.parent_course_id) ) INNER JOIN canvas_warehouse.group_dim AS group_dim ON group_membership_fact.group_id = group_dim.id WHERE (group_dim.name like 'Team%' AND group_dim.workflow_state = 'available'); /* Get all submissions for courses */ /* Returns 108278 Observations */ CREATE OR REPLACE VIEW submissions_all FOLDER = '/wong22-23' AS SELECT DISTINCT course_dim.sis_source_id AS sis_source_id, submission_dim.user_id AS user_id, submission_dim.assignment_id AS assignment_id, submission_dim.group_id AS group_id, submission_fact.published_score AS published_score FROM (((canvas_warehouse.course_dim AS course_dim INNER JOIN canvas_warehouse.assignment_dim AS assignment_dim ON course_dim.id = assignment_dim.course_id ) INNER JOIN canvas_warehouse.enrollment_term_dim AS enrollment_term_dim ON course_dim.enrollment_term_id = enrollment_term_dim.id ) INNER JOIN canvas_warehouse.submission_dim AS submission_dim ON assignment_dim.id = submission_dim.assignment_id ) INNER JOIN canvas_warehouse.submission_fact AS submission_fact ON submission_dim.id = submission_fact.submission_id WHERE (submission_dim.grade_matches_current_submission = 'true' AND assignment_dim.workflow_state = 'published' AND submission_dim.workflow_state = 'graded' AND enrollment_term_dim.sis_source_id in (4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198) AND course_dim.sis_source_id like '%BL-INFO-I308%'); /* Get all course sections */ /* Returns 5,329 observations */ CREATE OR REPLACE VIEW info_i308_course_course_section FOLDER = '/wong22-23' AS SELECT DISTINCT enrollment_term_dim.sis_source_id AS acad_term_cd, course_dim.id AS course_id, course_dim.canvas_id AS course_canvas_id, course_dim.name AS course_name, course_dim.sis_source_id AS course_sis_source_id, course_section_dim.id AS course_section_id, course_section_dim.canvas_id AS course_section_canvas_id, course_section_dim.name AS course_section_name, course_section_dim.sis_source_id AS course_section_sis_source_id FROM (canvas_warehouse.course_dim AS course_dim INNER JOIN canvas_warehouse.enrollment_term_dim AS enrollment_term_dim ON course_dim.enrollment_term_id = enrollment_term_dim.id ) INNER JOIN canvas_warehouse.course_section_dim AS course_section_dim ON course_dim.id = course_section_dim.course_id WHERE (course_dim.sis_source_id like '%BL-INFO-I308%' AND enrollment_term_dim.sis_source_id in (4172, 4175, 4178, 4182, 4185, 4188, 4192, 4195, 4198)); /* Get all assignments */ /* Returns 22,684 observations */ CREATE OR REPLACE VIEW info_i308_course_course_section_assignments_all FOLDER = '/wong22-23' AS SELECT info_i308_course_course_section.acad_term_cd AS acad_term_cd, info_i308_course_course_section.course_id AS course_id, info_i308_course_course_section.course_canvas_id AS course_canvas_id, info_i308_course_course_section.course_name AS course_name, info_i308_course_course_section.course_sis_source_id AS course_sis_source_id, info_i308_course_course_section.course_section_id AS course_section_id, info_i308_course_course_section.course_section_canvas_id AS course_section_canvas_id, info_i308_course_course_section.course_section_name AS course_section_name, info_i308_course_course_section.course_section_sis_source_id AS course_section_sis_source_id, assignment_dim.id AS assn_id, assignment_dim.canvas_id AS assn_canvas_id, assignment_dim.title AS assn_title, assignment_dim.description AS description, assignment_dim.due_at AS assn_due_at, assignment_dim.unlock_at AS unlock_at, assignment_dim.lock_at AS lock_at, assignment_dim.points_possible AS points_possible, assignment_dim.grading_type AS grading_type, assignment_dim.submission_types AS submission_types, assignment_dim.workflow_state AS workflow_state, assignment_dim.created_at AS created_at, assignment_dim.updated_at AS updated_at, assignment_dim.peer_review_count AS peer_review_count, assignment_dim.peer_reviews_due_at AS peer_reviews_due_at, assignment_dim.peer_reviews_assigned AS peer_reviews_assigned, assignment_dim.peer_reviews AS peer_reviews, assignment_dim.automatic_peer_reviews AS automatic_peer_reviews, assignment_dim.all_day AS all_day, assignment_dim.all_day_date AS all_day_date, assignment_dim.could_be_locked AS could_be_locked, assignment_dim.grade_group_students_individually AS grade_group_students_individually, assignment_dim.anonymous_peer_reviews AS anonymous_peer_reviews, assignment_dim.muted AS muted, assignment_dim.assignment_group_id AS assignment_group_id, assignment_dim.position AS position, assignment_dim.visibility AS visibility, assignment_dim.external_tool_id AS external_tool_id, assignment_override_dim.assignment_id AS assignment_id, assignment_override_dim.group_id AS assn_ovrd_group_id, assignment_override_dim.quiz_id AS quiz_id, assignment_override_dim.assignment_version AS assignment_version, assignment_override_dim.due_at AS assignment_override_due_at, assignment_override_dim.due_at_overridden AS due_at_overridden, assignment_override_dim.lock_at_overridden AS lock_at_overridden, assignment_override_dim.set_type AS set_type, assignment_override_dim.title AS assignment_override_title, assignment_override_dim.id AS assn_ovrd_id, assignment_group_dim.name AS assignment_group_name FROM ((info_i308_course_course_section AS info_i308_course_course_section INNER JOIN canvas_warehouse.assignment_dim AS assignment_dim ON info_i308_course_course_section.course_id = assignment_dim.course_id ) LEFT OUTER JOIN canvas_warehouse.assignment_override_dim AS assignment_override_dim ON assignment_dim.id = assignment_override_dim.assignment_id ) INNER JOIN canvas_warehouse.assignment_group_dim AS assignment_group_dim ON assignment_dim.assignment_group_id = assignment_group_dim.id WHERE assignment_dim.workflow_state = 'published'; /* Get assignments with no overrides */ /* Returns 5329 observations */ CREATE OR REPLACE VIEW info_i308_course_course_section_assignments_no_override FOLDER = '/wong22-23' AS SELECT info_i308_course_course_section_assignments_all.acad_term_cd AS acad_term_cd, info_i308_course_course_section_assignments_all.course_id AS course_id, info_i308_course_course_section_assignments_all.course_canvas_id AS course_canvas_id, info_i308_course_course_section_assignments_all.course_name AS course_name, info_i308_course_course_section_assignments_all.course_sis_source_id AS course_sis_source_id, info_i308_course_course_section_assignments_all.course_section_id AS course_section_id, info_i308_course_course_section_assignments_all.course_section_canvas_id AS course_section_canvas_id, info_i308_course_course_section_assignments_all.course_section_name AS course_section_name, info_i308_course_course_section_assignments_all.course_section_sis_source_id AS course_section_sis_source_id, info_i308_course_course_section_assignments_all.assn_id AS assn_id, info_i308_course_course_section_assignments_all.assn_canvas_id AS assn_canvas_id, info_i308_course_course_section_assignments_all.assn_title AS assn_title, info_i308_course_course_section_assignments_all.description AS description, info_i308_course_course_section_assignments_all.assn_due_at AS assn_due_at, info_i308_course_course_section_assignments_all.unlock_at AS unlock_at, info_i308_course_course_section_assignments_all.lock_at AS lock_at, info_i308_course_course_section_assignments_all.points_possible AS points_possible, info_i308_course_course_section_assignments_all.grading_type AS grading_type, info_i308_course_course_section_assignments_all.submission_types AS submission_types, info_i308_course_course_section_assignments_all.workflow_state AS workflow_state, info_i308_course_course_section_assignments_all.created_at AS created_at, info_i308_course_course_section_assignments_all.updated_at AS updated_at, info_i308_course_course_section_assignments_all.peer_review_count AS peer_review_count, info_i308_course_course_section_assignments_all.peer_reviews_due_at AS peer_reviews_due_at, info_i308_course_course_section_assignments_all.peer_reviews_assigned AS peer_reviews_assigned, info_i308_course_course_section_assignments_all.peer_reviews AS peer_reviews, info_i308_course_course_section_assignments_all.automatic_peer_reviews AS automatic_peer_reviews, info_i308_course_course_section_assignments_all.all_day AS all_day, info_i308_course_course_section_assignments_all.all_day_date AS all_day_date, info_i308_course_course_section_assignments_all.could_be_locked AS could_be_locked, info_i308_course_course_section_assignments_all.grade_group_students_individually AS grade_group_students_individually, info_i308_course_course_section_assignments_all.anonymous_peer_reviews AS anonymous_peer_reviews, info_i308_course_course_section_assignments_all.muted AS muted, info_i308_course_course_section_assignments_all.assignment_group_id AS assignment_group_id, info_i308_course_course_section_assignments_all.position AS position, info_i308_course_course_section_assignments_all.visibility AS visibility, info_i308_course_course_section_assignments_all.external_tool_id AS external_tool_id, info_i308_course_course_section_assignments_all.assignment_id AS assignment_id, info_i308_course_course_section_assignments_all.assn_ovrd_group_id AS assn_ovrd_group_id, info_i308_course_course_section_assignments_all.quiz_id AS quiz_id, info_i308_course_course_section_assignments_all.assignment_group_name AS assignment_group_name FROM info_i308_course_course_section_assignments_all WHERE assn_ovrd_id is null ; /* Get assignments with overrides by section */ /* Returns 1045 observations */ CREATE OR REPLACE VIEW info_i308_course_course_section_assignments_override FOLDER = '/wong22-23' AS SELECT info_i308_course_course_section.acad_term_cd AS acad_term_cd, info_i308_course_course_section.course_id AS course_id, info_i308_course_course_section.course_canvas_id AS course_canvas_id, info_i308_course_course_section.course_name AS course_name, info_i308_course_course_section.course_sis_source_id AS course_sis_source_id, info_i308_course_course_section.course_section_id AS course_section_id, info_i308_course_course_section.course_section_canvas_id AS course_section_canvas_id, info_i308_course_course_section.course_section_name AS course_section_name, info_i308_course_course_section.course_section_sis_source_id AS course_section_sis_source_id, assignment_dim.id AS assn_id, assignment_dim.canvas_id AS assn_canvas_id, assignment_group_dim.name AS assignment_group_name, assignment_override_dim.id AS id, assignment_dim.title AS assn_title, assignment_dim.description AS description, assignment_dim.due_at AS due_at, assignment_dim.unlock_at AS unlock_at, assignment_dim.lock_at AS lock_at, assignment_dim.points_possible AS points_possible, assignment_dim.grading_type AS grading_type, assignment_dim.submission_types AS submission_types, assignment_dim.workflow_state AS workflow_state, assignment_dim.created_at AS created_at, assignment_dim.updated_at AS updated_at, assignment_dim.peer_review_count AS peer_review_count, assignment_dim.peer_reviews_due_at AS peer_reviews_due_at, assignment_dim.peer_reviews_assigned AS peer_reviews_assigned, assignment_dim.peer_reviews AS peer_reviews, assignment_dim.automatic_peer_reviews AS automatic_peer_reviews, assignment_dim.all_day AS all_day, assignment_dim.all_day_date AS all_day_date, assignment_dim.could_be_locked AS could_be_locked, assignment_dim.grade_group_students_individually AS grade_group_students_individually, assignment_dim.anonymous_peer_reviews AS anonymous_peer_reviews, assignment_dim.muted AS muted, assignment_dim.assignment_group_id AS assignment_group_id, assignment_dim.position AS position, assignment_dim.visibility AS visibility, assignment_dim.external_tool_id AS external_tool_id, assignment_override_dim.canvas_id AS canvas_id, assignment_override_dim.assignment_id AS assignment_id, assignment_override_dim.group_id AS group_id, assignment_override_dim.quiz_id AS quiz_id, assignment_override_dim.assignment_version AS assignment_version, assignment_override_dim.due_at AS assn_ovrd_due_at, assignment_override_dim.due_at_overridden AS due_at_overridden, assignment_override_dim.title AS assn_ovrd_title FROM ((info_i308_course_course_section AS info_i308_course_course_section INNER JOIN canvas_warehouse.assignment_dim AS assignment_dim ON info_i308_course_course_section.course_id = assignment_dim.course_id ) INNER JOIN canvas_warehouse.assignment_override_dim AS assignment_override_dim ON (info_i308_course_course_section.course_section_id = assignment_override_dim.course_section_id AND assignment_dim.id = assignment_override_dim.assignment_id) ) INNER JOIN canvas_warehouse.assignment_group_dim AS assignment_group_dim ON assignment_dim.assignment_group_id = assignment_group_dim.id WHERE assignment_override_dim.workflow_state = 'active'; /* Get submissions on all non-overrided assignments for all enrollments */ /* Returns 84,533 observations */ CREATE OR REPLACE VIEW info_i308_stnt_grp_enrl_subm_no_assn_ovrrd FOLDER = '/wong22-23' AS SELECT DISTINCT stdnt_enrollments_w_groups.course_id AS course_id, stdnt_enrollments_w_groups.user_id AS user_id, info_i308_course_course_section_assignments_no_override.acad_term_cd AS acad_term_cd, info_i308_course_course_section_assignments_no_override.course_sis_source_id AS course_sis_source_id, stdnt_enrollments_w_groups.group_name AS group_name, info_i308_course_course_section_assignments_no_override.assignment_id AS assignment_id, info_i308_course_course_section_assignments_no_override.assn_title AS assn_title, info_i308_course_course_section_assignments_no_override.assignment_group_name AS assignment_group_name, info_i308_course_course_section_assignments_no_override.assn_due_at AS assn_due_at, info_i308_course_course_section_assignments_no_override.grading_type AS grading_type, info_i308_course_course_section_assignments_no_override.submission_types AS submission_types, info_i308_course_course_section_assignments_no_override.grade_group_students_individually AS grade_group_students_individually, info_i308_course_course_section_assignments_no_override.points_possible AS points_possible, submissions_all.published_score AS published_score FROM (stdnt_enrollments_w_groups AS stdnt_enrollments_w_groups INNER JOIN info_i308_course_course_section_assignments_no_override AS info_i308_course_course_section_assignments_no_override ON stdnt_enrollments_w_groups.course_id = info_i308_course_course_section_assignments_no_override.course_id ) LEFT OUTER JOIN submissions_all AS submissions_all ON (info_i308_course_course_section_assignments_no_override.assn_id = submissions_all.assignment_id AND stdnt_enrollments_w_groups.user_id = submissions_all.user_id) ORDER BY course_sis_source_id ASC, assn_title ASC, user_id ASC; /* Get submissions for all overrided assignments for all enrollments */ /* Returns 30714 observations */ CREATE OR REPLACE VIEW info_i308_stnt_grp_enrl_subm_w_assn_ovrrd FOLDER = '/wong22-23' AS SELECT DISTINCT stdnt_enrollments_w_groups.course_id AS course_id, stdnt_enrollments_w_groups.user_id AS user_id, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.acad_term_cd AS acad_term_cd, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.course_sis_source_id AS course_sis_source_id, stdnt_enrollments_w_groups.group_name AS group_name, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.assignment_id AS assignment_id, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.assn_title AS assn_title, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.assignment_group_name AS assignment_group_name, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.grading_type AS grading_type, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.submission_types AS submission_types, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.grade_group_students_individually AS grade_group_students_individually, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.assn_ovrd_due_at AS assn_due_at, info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.points_possible AS points_possible, submissions_all.published_score AS published_score FROM (stdnt_enrollments_w_groups AS stdnt_enrollments_w_groups INNER JOIN info_i308_course_course_section_assignments_override AS info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim ON stdnt_enrollments_w_groups.course_section_id = info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.course_section_id ) LEFT OUTER JOIN submissions_all AS submissions_all ON (info_i308_course_course_section_j_assignment_dim_j_assignment_override_dim.assn_id = submissions_all.assignment_id AND stdnt_enrollments_w_groups.user_id = submissions_all.user_id) ; /* Combine the submissions for assignments with and without overrides for all enrollments as a union*/ /* Returns 116,147 observations */ CREATE OR REPLACE VIEW info_i308_union FOLDER = '/wong22-23' AS SELECT course_id AS course_id, user_id AS user_id, acad_term_cd AS acad_term_cd, course_sis_source_id AS course_sis_source_id, group_name AS group_name, assignment_id AS assignment_id, assn_title AS assn_title, assignment_group_name AS assignment_group_name, assn_due_at AS assn_due_at, grading_type AS grading_type, submission_types AS submission_types, grade_group_students_individually AS grade_group_students_individually, points_possible AS points_possible, published_score AS published_score FROM (SELECT course_id, user_id, acad_term_cd, course_sis_source_id, group_name, assignment_id, assn_title, assignment_group_name, assn_due_at, grading_type, submission_types, grade_group_students_individually, points_possible, published_score FROM info_i308_stnt_grp_enrl_subm_no_assn_ovrrd SQL UNION ALL SELECT course_id, user_id, acad_term_cd, course_sis_source_id, group_name, assignment_id, assn_title, assignment_group_name, assn_due_at, grading_type, submission_types, grade_group_students_individually, points_possible, published_score FROM info_i308_stnt_grp_enrl_subm_w_assn_ovrrd) ORDER BY acad_term_cd ASC, course_id ASC, assn_title ASC; /* Do some clean up for final data and anonymization */ /* Returns 103902 observations */ CREATE OR REPLACE VIEW info_i308_datav3 FOLDER = '/wong22-23' AS SELECT DISTINCT hash(pseudonym_dim.sis_user_id) AS student_id, case WHEN (info_i308_union.acad_term_cd = 4172) THEN 'S2017' WHEN (info_i308_union.acad_term_cd = 4178) THEN 'F2017' WHEN (info_i308_union.acad_term_cd = 4182) THEN 'S2018' WHEN (info_i308_union.acad_term_cd = 4188) THEN 'F2018' WHEN (info_i308_union.acad_term_cd = 4192) THEN 'S2019' ELSE 'F2019' END AS semester, hash(info_i308_union.course_sis_source_id) AS course_id, info_i308_union.group_name AS stdnt_group, info_i308_union.assn_title AS assn_title, case WHEN (info_i308_union.assignment_group_name = 'Team Assignments') THEN 'group' ELSE 'indiv' END AS assignment_collab, info_i308_union.assn_due_at AS assn_due_at, info_i308_union.points_possible AS points_possible, info_i308_union.published_score AS published_score, info_i308_union.assignment_group_name AS assignment_group_name FROM info_i308_union AS info_i308_union INNER JOIN canvas_warehouse_sandbox.pseudonym_dim AS pseudonym_dim ON info_i308_union.user_id = pseudonym_dim.user_id WHERE (pseudonym_dim.unique_name not like '%@%' AND pseudonym_dim.sis_user_id not like '8000%' AND info_i308_union.acad_term_cd not in (4175, 4185, 4195));