/* canvas_scr_dataset */ /* Creates score of Canvas current score for enrollment */ /Process 1.19 GB in 9 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.sp20_canvas_scr_dataset` AS WITH sp20_canvas_scores AS ( SELECT p.sis_user_id AS prsn_univ_id, c.canvas_id as course_canvas_id, csec.canvas_id as course_section_canvas_id, csec.sis_source_id as course_section_sis_id, SPLIT(csec.sis_source_id, '-') AS sis_id_array, cd.updated_at as score_updated_at, cf.current_score as canvas_scores FROM `iu-uits-tlt-la.canvas_data.course_dim` c INNER JOIN `iu-uits-tlt-la.canvas_data.course_section_dim` csec ON c.id = csec.course_id INNER JOIN `iu-uits-tlt-la.canvas_data.enrollment_dim` en ON csec.id = en.course_section_id INNER JOIN `iu-uits-tlt-la.canvas_data.pseudonym_dim` p ON en.user_id = p.user_id INNER JOIN `iu-uits-tlt-la.canvas_data.course_score_dim` cd ON en.id = cd.enrollment_id INNER JOIN `iu-uits-tlt-la.canvas_data.course_score_fact` cf ON cd.id = cf.score_id WHERE p.sis_user_id NOT LIKE '8000%' AND c.enrollment_term_id = 290000000006464 AND en.workflow_state = 'active' AND en.type = 'StudentEnrollment' AND cd.workflow_state = 'active' AND c.sis_source_id IS NOT NULL AND c.sis_source_id NOT LIKE 'ETRAIN%' AND csec.sis_source_id IS NOT NULL AND csec.sis_source_id LIKE '%BL%' ) SELECT prsn_univ_id, course_canvas_id, cs.course_section_sis_id , sis_id_array[ORDINAL(array_length(sis_id_array ))] as cls_nbr, score_updated_at, canvas_scores, CASE WHEN go.override_grade IS NOT NULL THEN 1 ELSE 0 END as has_override, go.override_score, go.override_grade FROM sp20_canvas_scores cs INNER JOIN `iu-uits-pti-elearninglab.Xiao21.sp20_grade_w_override` go ON cs.prsn_univ_id = go.student_sis AND cs.course_section_canvas_id = go.section_id WHERE go.course_sis IS NOT NULL /* assn_est_score*/ /* Creates table of estimated assignment scores */ /* Processed 12.33 GB in 10 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.assn_est_score` AS WITH assn_grp_tots as ( SELECT course_id, assignment_group_name, assignment_group_weight, sum(assignment_points_possible) as assn_tot_pnts_possible FROM `iu-uits-pti-elearninglab.Xiao21.course_assignment_details` GROUP BY course_id, assignment_group_name, assignment_group_weight), stdnt_assn_grp_tots AS ( select a.course_id, a.assignment_id, a.assignment_group_id, a.assignment_points_possible, a.assignment_group_name, a.assignment_group_weight, b.id as submission_id, c.sis_user_id, b.published_grade, d.published_score, round((d.published_score/a.assignment_points_possible),2) * 100 as assignment_score_percentage from `iu-uits-pti-elearninglab.Xiao21.course_assignment_details` a inner join`iu-uits-tlt-la.canvas_data.submission_dim` b ON a.assignment_id = b.assignment_id inner join `iu-uits-tlt-la.canvas_data.pseudonym_dim` c ON b.user_id = c.user_id inner join `iu-uits-tlt-la.canvas_data.submission_fact` d ON b.id = d.submission_id WHERE b.workflow_state = 'graded' and b.grade_state <> 'not_graded' AND c.sis_user_id not like '8000%' AND c.sis_user_id not like '%@%' ORDER by a.course_id), stndt_assn_totals as ( select a.course_id, a.sis_user_id, a.assignment_group_name, b.assignment_group_weight, b.assn_tot_pnts_possible, sum(published_score) as assn_grp_score from stdnt_assn_grp_tots a inner join assn_grp_tots b ON a.course_id = b.course_id AND a.assignment_group_name = b.assignment_group_name group by a.course_id, a.sis_user_id,a.assignment_group_name,b.assignment_group_weight,b.assn_tot_pnts_possible), stdnt_assn_grp_wght_scrs AS ( select course_id, sis_user_id, assignment_group_name, assignment_group_weight, assn_tot_pnts_possible, assn_grp_score , ROUND(assn_grp_score /assn_tot_pnts_possible ,2) as assn_grp_scr_perc, CASE WHEN assignment_group_weight = 0.0 THEN ROUND(assn_grp_score/assn_tot_pnts_possible,2) * 100 ELSE assignment_group_weight * ROUND(assn_grp_score /assn_tot_pnts_possible ,2) END as assn_grp_wght_scr from stndt_assn_totals ORDER BY course_id, sis_user_id, assignment_group_name), non_weighted_assn AS ( select course_id, sis_user_id, avg(assn_grp_wght_scr) as est_score from stdnt_assn_grp_wght_scrs where assignment_group_weight = 0.0 group by course_id,sis_user_id ), wghted_assn as ( select course_id, sis_user_id, sum(assn_grp_wght_scr) as est_score from stdnt_assn_grp_wght_scrs where assignment_group_weight <> 0.0 group by course_id,sis_user_id ), cmb_assn as ( select a.* from non_weighted_assn a UNION DISTINCT SELECT b.* FROM wghted_assn b order by course_id, sis_user_id ) select course_id, sis_user_id, max(est_score) as assn_est_score from cmb_assn group by course_id, sis_user_id /* assignment_details */ /* Gets details of assignments */ /* Processed 537.66 MB in 11 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.course_assignment_details` AS WITH course as ( select distinct course_id as canvas_course_id, cd.id as course_id FROM `iu-uits-pti-elearninglab.Xiao21.sp20_grade_w_override` INNER JOIN `iu-uits-tlt-la.canvas_data.course_dim` cd ON course_id = cd.canvas_id) select c.course_id, ad.id as assignment_id, ad.assignment_group_id, af.points_possible as assignment_points_possible, agd.name as assignment_group_name, agf.group_weight as assignment_group_weight from course c inner join `iu-uits-tlt-la.canvas_data.assignment_dim` ad ON c.course_id = ad.course_id inner join `iu-uits-tlt-la.canvas_data.assignment_fact` af ON ad.id = af.assignment_id inner join `iu-uits-tlt-la.canvas_data.assignment_group_dim` agd ON agd.id = ad.assignment_group_id inner join `iu-uits-tlt-la.canvas_data.assignment_group_fact` agf ON agd.id = agf.assignment_group_id WHERE ad.workflow_state = 'published' AND ad.due_at IS NOT NULL and ad.points_possible > 0 AND agd.workflow_state = 'available' ORDER BY course_id /* dataset-baseline dataset for the merging of data provided by BAR */ /* Processes 17.94 MB in 4 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.dataset` AS select a.prsn_univ_id, a.course_canvas_id, CAST(4202 AS STRING) as acad_term_cd, a.cls_nbr, a.score_updated_at, CASE WHEN b.has_weighted_assignments IS NOT NULL THEN b.has_weighted_assignments ELSE 0 END as has_weighted_assignments, a.canvas_scores as overall_canvas_score, c.assn_est_score as assn_est_score_05_08, a.has_override, a.override_score, a.override_grade, a.override_updated_at from `iu-uits-pti-elearninglab.Xiao21.sp20_canvas_scr_dataset` a left join `iu-uits-pti-elearninglab.Xiao21.course_assignment_weights` b ON CAST(CONCAT('29000000',CAST(a.course_canvas_id AS STRING)) AS INT64) = b.course_id left join `iu-uits-pti-elearninglab.Xiao21.assn_est_score` c ON CAST(CONCAT('29000000',CAST(a.course_canvas_id AS STRING)) AS INT64) = c.course_id AND c.sis_user_id = a.prsn_univ_id ORDER by course_canvas_id, prsn_univ_id /* Creates join of baseline dataset and BAR data */ /* Processes 1.96 in 15 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.canvas_bar_dataset` AS SELECT SHA256(a.prsn_univ_id) as student_id, a.acad_term_cd, c.COHORT_SP_FA as sr_retention_cohort_sp_fa, CAST(c.COHORT_TERM_CD AS int64) as sr_retention_cohort_term_cd, c.COHORT_TERM_DESC as sr_retention_cohort_term_desc, c.NEW_STUDENT_CATEGORY as sr_retention_new_student_category, c.ETHNICITY as sr_retention_ethnicty, b.URM_FLAG as stu_attribute_urm_flag, b.gender as stu_attribute_gender, b.RESIDENCY as stu_attribute_residency, c.TWNTYFRST_CENT_SCHOLAR as sr_retention_twntyfrst_cent_scholar, c.TWNTYFRST_CENT_AWARD as sr_retention_twntyfrst_cent_award, c.FIRST_GENERATION as sr_retention_first_generation, c.PELL_ELIGIBILITY as sr_retention_pell_eligibility, c.HIGH_SCHOOL_GPA as sr_retention_high_school_gpa, c.SAT_DRVD_COMPOSITE_SCORE as sr_retention_sat_drvd_composite_score, c.entry_age as sr_retention_entry_age, c.class_level as sr_retention_class_level, c.ENTRY_UNITS as sr_retention_entry_units, c.ENTRY_SCHOOL as sr_retention_entry_school, c.ENTRY_INTENDED_SCHOOL as sr_retention_entry_intended_school, c.ENTRY_PROGRAM as sr_retention_entry_program, c.ENTRY_MAJOR_CODE as sr_retention_entry_major_code, c.entry_major_desc as sr_retention_entry_major_desc, b.ADMIT_STATUS as stu_attribute_admit_status, b.TIME_2_CERT as stu_attribute_time_2_cert, c.TERM_SEQUENCE as sr_retention_term_sequence, c.TERM_STATUS as sr_retention_term_status, c.EXPANDED_CLASS_LEVEL as sr_retention_expanded_class_level, c.SCHOOL_CODE as sr_retention_school_code, c.SCHOOL_DESC as sr_retention_school_desc, c.INTENDED_SCHOOL as sr_retention_intended_school, c.term_start_units as sr_retention_term_start_units, c.term_start_cum_units as sr_retention_term_start_cum_units, c.TERM_END_UNITS as sr_retention_term_end_units, c.TERM_END_EARNED_UNITS as sr_retention_term_end_earned_units, c.TERM_END_CUM_UNITS as sr_retention_term_end_cum_units, c.TERM_END_GPA as sr_retention_term_end_gpa, c.TERM_END_CUM_GPA as sr_retention_term_end_cum_gpa, c.BA_DEGREE_RECEIVED as sr_retention_ba_degree_received, c.DEGREE1_DESC as sr_retention_degree1_desc, c.DEGREE1_LEVEL as sr_retention_degree1_level, c.DEGREE1_SCHOOL as sr_retention_degree1_school, c.DEGREE1_CUM_GPA as sr_retention_degree1_cum_gpa, c.MOS_TO_DEGREE1 as sr_retention_mos_to_degree1, c.DEGREE2_DESC as sr_retention_degree2_desc, c.DEGREE2_LEVEL as sr_retention_degree2_level, c.DEGREE2_SCHOOL as sr_retention_degree2_school, c.DEGREE2_CUM_GPA as sr_retention_degree2_cum_gpa, c.MOS_TO_DEGREE2 as sr_retention_mos_to_degree2, c.DEGREE3_DESC as sr_retention_degree3_desc, c.DEGREE3_LEVEL as sr_retention_degree3_level, c.DEGREE3_SCHOOL as sr_retention_degree3_school, c.DEGREE3_CUM_GPA as sr_retention_degree3_cum_gpa, c.MOS_TO_DEGREE3 as sr_retention_mos_to_degree3, c.DEGREE_MAJOR1_CD as sr_retention_major1_cd, c.DEGREE_MAJOR1 as sr_retention_degree_major1, c.DEGREE_MAJOR2_CD as sr_retention_degree_major2_cd, c.DEGREE_MAJOR2 as sr_retention_degree_major2, c.DEGREE_MAJOR3_CD as sr_retention_degree_major3_cd, c.DEGREE_MAJOR3 as sr_retention_degree_major3, c.DEGREE_LEVEL_4_YEARS as sr_retention_degree_level_4_years, c.DEGREE_LEVEL_6_YEARS as sr_retention_degree_level_6_years, c.DEGREE_LEVEL_8_YEARS as sr_retention_degree_level_8_years, e.PRIMARY_PROGRAM_CODE as student_major_primary_program_code, e.PRIMARY_PROGRAM_DESCRIPTION as student_major_primary_program_description, e.MAJOR_1_CODE as student_major_major_1_code, e.MAJOR_1_DESCRIPTION as student_major_major_1_desc, e.MAJOR_2_CODE as student_major_major_2_code, e.MAJOR_2_DESCRIPTION as student_major_major_2_desc, e.MAJOR_3_CODE as student_major_major_3_code, e.MAJOR_3_DESCRIPTION as student_major_3_desc, SHA256(CAST(a.course_canvas_id AS STRING)) as course_canvas_id_hashed, SHA256(a.cls_nbr) as cls_nbr_hashed, d.INST_CD as stu_crs_tbl_inst_cd, d.ACAD_UNT_TKN_NBR as stu_crs_tbl_acad_unt_tkn_nbr, d.CRS_TYPE as stu_crs_tbl_crs_type, d.CRS_CATEGORY as stu_crs_tbl_crs_category, d.EARNED_BFORE_COHORT as stu_crs_tbl_earned_before_cohort, SHA256(d.CRS_ID) as stu_crs_tbl_crs_id_hashed, SHA256(d.CRSOFR_NBR) as stu_crs_tbl_crsofr_nbr_hashed, d.CRS_SUBJ_DEPT_CD as stu_crs_tbl_crs_subj_dept_cd, d.CRS_SUBJ_CD as stu_crs_tbl_crs_subj_cd, d.CRS_CATLG_NBR as stu_crs_tbl_crs_catlg_nbr, d.CRS_NM as stu_crs_tbl_crs_nm, d.CRS_DESC as stu_crs_tbl_crs_desc, d.CRS_CMPNT_CD as stu_crs_tbl_crs_cmpnt_cd, d.CRS_OFCL_GRD_CD as stu_crs_tbl_crs_ofcl_grd_cd, d.CRS_LTTR_GRD as stu_crs_tbl_crs_lttr_grd, d.CRS_REPEAT_CD as stu_crs_tbl_crs_repeat_cd, SHA256(d.CLS_INSTR_UNIV_ID) as stu_crs_tbl_cls_instr_id_hashed, d.CLS_INSTRC_MODE_CD as stu_crs_tbl_cls_instr_mode_cd, d.STU_GRD_PTS_NBR as stu_crs_tbl_stu_grd_pts_nbr, d.STU_GRD_BASIS_CD as stu_crs_tbl_stu_grd_basis_cd, d.STU_DRVD_CLS_ENRL_STAT_IND as stu_crs_tbl_stu_drvd_cls_enrl_stat_ind, d.STU_EARN_CRDT_IND as stu_crs_tbl_stu_earn_crdt_ind, d.STU_ENRL_DRP_DT as stu_crs_tbl_stu_enrl_drp_dt, d.EXT_ORG_ID as stu_crs_tbl_ext_org_id, d.EXT_ORG_NM as stu_crs_tbl_ext_org_nm, d.GPAO as stu_crs_tbl_gpao, a.score_updated_at as canvas_score_updated_at, has_weighted_assignments as canvas_has_weighted_assignments, overall_canvas_score as canvas_estimated_score, assn_est_score_05_08 as canvas_assn_est_score_05_08, has_override as canvas_has_override, override_score as canvas_override_score, override_grade as canvas_override_grade, override_updated_at as canvas_override_updated_at from `iu-uits-pti-elearninglab.Xiao21.dataset` a inner join `iu-uits-pti-elearninglab.Xiao21.stu_attributes` b ON a.prsn_univ_id = b.PRSN_UNIV_ID inner join `iu-uits-pti-elearninglab.Xiao21.sr_retention` c ON a.prsn_univ_id = c.PRSN_UNIV_ID AND a.acad_term_cd = c.term_code inner join `iu-uits-pti-elearninglab.Xiao21.stu_crs_tbl` d ON a.prsn_univ_id = d.PRSN_UNIV_ID AND a.acad_term_cd = d.ACAD_TERM_CD AND a.cls_nbr = d.CLS_NBR left join `iu-uits-pti-elearninglab.Xiao21.student_major` e ON a.prsn_univ_id = e.prsn_univ_id AND a.acad_term_cd = e.term_code /* Generates data for combining BAR datasets that exclude Spring 2020 course enrollments, degrees, and enrollments */ /* Processes 1.95 in 21 secs */ CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.bar_data_join_nosp20` AS WITH stdnt_crs_enrl AS ( SELECT SHA256(b.prsn_univ_id) as student_id, c.COHORT_SP_FA as sr_retention_cohort_sp_fa, CAST(c.COHORT_TERM_CD AS int64) as sr_retention_cohort_term_cd, c.COHORT_TERM_DESC as sr_retention_cohort_term_desc, c.NEW_STUDENT_CATEGORY as sr_retention_new_student_category, c.ETHNICITY as sr_retention_ethnicty, b.URM_FLAG as stu_attribute_urm_flag, b.gender as stu_attribute_gender, b.RESIDENCY as stu_attribute_residency, c.TWNTYFRST_CENT_SCHOLAR as sr_retention_twntyfrst_cent_scholar, c.TWNTYFRST_CENT_AWARD as sr_retention_twntyfrst_cent_award, c.FIRST_GENERATION as sr_retention_first_generation, c.PELL_ELIGIBILITY as sr_retention_pell_eligibility, c.HIGH_SCHOOL_GPA as sr_retention_high_school_gpa, c.SAT_DRVD_COMPOSITE_SCORE as sr_retention_sat_drvd_composite_score, c.entry_age as sr_retention_entry_age, c.class_level as sr_retention_class_level, c.ENTRY_UNITS as sr_retention_entry_units, c.ENTRY_SCHOOL as sr_retention_entry_school, c.ENTRY_INTENDED_SCHOOL as sr_retention_entry_intended_school, c.ENTRY_PROGRAM as sr_retention_entry_program, c.ENTRY_MAJOR_CODE as sr_retention_entry_major_code, c.entry_major_desc as sr_retention_entry_major_desc, b.ADMIT_STATUS as stu_attribute_admit_status, b.TIME_2_CERT as stu_attribute_time_2_cert, c.TERM_SEQUENCE as sr_retention_term_sequence, c.TERM_STATUS as sr_retention_term_status, c.EXPANDED_CLASS_LEVEL as sr_retention_expanded_class_level, c.SCHOOL_CODE as sr_retention_school_code, c.SCHOOL_DESC as sr_retention_school_desc, c.INTENDED_SCHOOL as sr_retention_intended_school, c.term_start_units as sr_retention_term_start_units, c.term_start_cum_units as sr_retention_term_start_cum_units, c.TERM_END_UNITS as sr_retention_term_end_units, c.TERM_END_EARNED_UNITS as sr_retention_term_end_earned_units, c.TERM_END_CUM_UNITS as sr_retention_term_end_cum_units, c.TERM_END_GPA as sr_retention_term_end_gpa, c.TERM_END_CUM_GPA as sr_retention_term_end_cum_gpa, c.BA_DEGREE_RECEIVED as sr_retention_ba_degree_received, c.DEGREE1_DESC as sr_retention_degree1_desc, c.DEGREE1_LEVEL as sr_retention_degree1_level, c.DEGREE1_SCHOOL as sr_retention_degree1_school, c.DEGREE1_CUM_GPA as sr_retention_degree1_cum_gpa, c.MOS_TO_DEGREE1 as sr_retention_mos_to_degree1, c.DEGREE2_DESC as sr_retention_degree2_desc, c.DEGREE2_LEVEL as sr_retention_degree2_level, c.DEGREE2_SCHOOL as sr_retention_degree2_school, c.DEGREE2_CUM_GPA as sr_retention_degree2_cum_gpa, c.MOS_TO_DEGREE2 as sr_retention_mos_to_degree2, c.DEGREE3_DESC as sr_retention_degree3_desc, c.DEGREE3_LEVEL as sr_retention_degree3_level, c.DEGREE3_SCHOOL as sr_retention_degree3_school, c.DEGREE3_CUM_GPA as sr_retention_degree3_cum_gpa, c.MOS_TO_DEGREE3 as sr_retention_mos_to_degree3, c.DEGREE_MAJOR1_CD as sr_retention_major1_cd, c.DEGREE_MAJOR1 as sr_retention_degree_major1, c.DEGREE_MAJOR2_CD as sr_retention_degree_major2_cd, c.DEGREE_MAJOR2 as sr_retention_degree_major2, c.DEGREE_MAJOR3_CD as sr_retention_degree_major3_cd, c.DEGREE_MAJOR3 as sr_retention_degree_major3, c.DEGREE_LEVEL_4_YEARS as sr_retention_degree_level_4_years, c.DEGREE_LEVEL_6_YEARS as sr_retention_degree_level_6_years, c.DEGREE_LEVEL_8_YEARS as sr_retention_degree_level_8_years, e.PRIMARY_PROGRAM_CODE as student_major_primary_program_code, e.PRIMARY_PROGRAM_DESCRIPTION as student_major_primary_program_description, e.MAJOR_1_CODE as student_major_major_1_code, e.MAJOR_1_DESCRIPTION as student_major_major_1_description, e.major_2_code as student_major_major_2_code, e.MAJOR_2_DESCRIPTION as student_major_major_2_description, e.MAJOR_3_CODE as student_major_major_3_code, e.MAJOR_3_DESCRIPTION as student_major_major_3_description, SHA256(d.cls_nbr) as cls_nbr_hashed, d.INST_CD as stu_crs_tbl_inst_cd, d.ACAD_TERM_CD as stu_crs_tbl_acad_term_cd, d.ACAD_UNT_TKN_NBR as stu_crs_tbl_acad_unt_tkn_nbr, d.CRS_TYPE as stu_crs_tbl_crs_type, d.CRS_CATEGORY as stu_crs_tbl_crs_category, d.EARNED_BFORE_COHORT as stu_crs_tbl_earned_before_cohort, SHA256(d.CRS_ID) as stu_crs_tbl_crs_id_hashed, SHA256(d.CRSOFR_NBR) as stu_crs_tbl_crsofr_nbr_hashed, d.CRS_SUBJ_DEPT_CD as stu_crs_tbl_crs_subj_dept_cd, d.CRS_SUBJ_CD as stu_crs_tbl_crs_subj_cd, d.CRS_CATLG_NBR as stu_crs_tbl_crs_catlg_nbr, d.CRS_NM as stu_crs_tbl_crs_nm, d.CRS_DESC as stu_crs_tbl_crs_desc, d.CRS_CMPNT_CD as stu_crs_tbl_crs_cmpnt_cd, d.CRS_OFCL_GRD_CD as stu_crs_tbl_crs_ofcl_grd_cd, d.CRS_LTTR_GRD as stu_crs_tbl_crs_lttr_grd, d.CRS_REPEAT_CD as stu_crs_tbl_crs_repeat_cd, SHA256(d.CLS_INSTR_UNIV_ID) as stu_crs_tbl_cls_instr_id_hashed, d.CLS_INSTRC_MODE_CD as stu_crs_tbl_cls_instr_mode_cd, d.STU_GRD_PTS_NBR as stu_crs_tbl_stu_grd_pts_nbr, d.STU_GRD_BASIS_CD as stu_crs_tbl_stu_grd_basis_cd, d.STU_DRVD_CLS_ENRL_STAT_IND as stu_crs_tbl_stu_drvd_cls_enrl_stat_ind, d.STU_EARN_CRDT_IND as stu_crs_tbl_stu_earn_crdt_ind, d.STU_ENRL_DRP_DT as stu_crs_tbl_stu_enrl_drp_dt, d.EXT_ORG_ID as stu_crs_tbl_ext_org_id, d.EXT_ORG_NM as stu_crs_tbl_ext_org_nm, d.GPAO as stu_crs_tbl_gpao FROM `iu-uits-pti-elearninglab.Xiao21.stu_attributes` b INNER join `iu-uits-pti-elearninglab.Xiao21.sr_retention` c ON b.prsn_univ_id = c.PRSN_UNIV_ID INNER join `iu-uits-pti-elearninglab.Xiao21.stu_crs_tbl` d ON b.prsn_univ_id = d.PRSN_UNIV_ID AND c.term_code = d.acad_term_cd LEFT JOIN `iu-uits-pti-elearninglab.Xiao21.student_major` e ON b.prsn_univ_id = e.PRSN_UNIV_ID AND c.term_code = e.TERM_CODE) SELECT * FROM stdnt_crs_enrl WHERE student_id IN (SELECT DISTINCT student_id FROM `iu-uits-pti-elearninglab.Xiao21.canvas_bar_dataset`) AND stu_crs_tbl_acad_term_cd <> '4202'