CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.`.sp20_weekly_session_navtime AS 
/* Query 1-Creates weekly navigation table for Spring 2020 courses within Canvas */
WITH sessionNavigationEvents As (
  SELECT
    SUBSTR(JSON_EXTRACT_SCALAR(event,"$[group][extensions]['com.instructure.canvas'][entity_id]"),-7,7) as course_id,
    SUBSTR(JSON_EXTRACT_SCALAR(event, "$[actor][id]"),-7,7) AS user_id, 
    SUBSTR(JSON_EXTRACT_SCALAR(event, "$[session][id]"),-32,32) as session_id,
    EXTRACT(WEEK from event_time) AS event_time_week,
    TIMESTAMP_DIFF(LEAD(event_time) OVER (PARTITION BY SUBSTR(JSON_EXTRACT_SCALAR(event,"$[session][id]"),-32,32) ORDER BY event_time), event_time, second) AS timebetweenEvents
  FROM 
    `udp-iu-prod.event_store.expanded` e 
  WHERE
    SUBSTR(JSON_EXTRACT_SCALAR(event,"$[group][extensions]['com.instructure.canvas'][entity_id]"),-7,7) IS NOT NULL
    AND SUBSTR(JSON_EXTRACT_SCALAR(event,"$[actor][id]"),-7,7) IS NOT NULL
    AND SUBSTR(JSON_EXTRACT_SCALAR(event,"$[session][id]"),-32,32) IS NOT NULL
    AND EXTRACT(DATE FROM event_time) BETWEEN '2020-01-13' AND '2020-05-08'
    AND JSON_EXTRACT_SCALAR(event,"$[actor][type]") = "Person"
    AND action = 'NavigatedTo'), 
  sessionNavigationDuration as (
    SELECT 
      course_id,
      user_id,
      session_id,
      event_time_week, 
      SUM(timebetweenEvents) as session_navigation_durations
    FROM sessionNavigationEvents 
    WHERE timebetweenEvents <= 1500
    GROUP BY course_id,user_id,event_time_week, session_id
  )
  SELECT 
    course_id,
    user_id,
    event_time_week as cal_week_number,
    count(distinct session_id) as num_sessions,
    sum(session_navigation_durations) as weekly_navigation_duration
  FROM sessionNavigationDuration
  GROUP BY course_id, user_id, event_time_week
  ORDER BY course_id, user_id, cal_week_number
  
/* Query 2- Match student_weekly_navigation session with Canvas Enrollments */
/* This query serves to (a) match only SIS provisioned courses and (b) ensuring that the user is a student and not an instructor */

CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.sp20_student_navigation_duration` AS        
SELECT a.course_id,
       b.sis_source_id,
       a.user_id,
       d.sis_user_id,
       a.cal_week_number,
       a.num_sessions,
       a.weekly_navigation_duration
 FROM `iu-uits-pti-elearninglab.Xiao21.sp20_weekly_session_navtime` a 
 INNER JOIN `iu-uits-tlt-la.canvas_data.course_dim` b ON  CAST(a.course_id AS INT64) = b.canvas_id
 INNER JOIN `iu-uits-tlt-la.canvas_data.user_dim` c ON CAST(a.user_id AS INT64) = c.canvas_id
 INNER JOIN `iu-uits-tlt-la.canvas_data.pseudonym_dim` d ON c.id = d.user_id
 INNER JOIN `iu-uits-tlt-la.canvas_data.enrollment_dim` e ON b.id = e.course_id AND c.id = e.user_id
 WHERE b.enrollment_term_id = 290000000006464 AND b.sis_source_id LIKE "SP20%" AND e.type = "StudentEnrollment" AND d.sis_user_id not like '8000%'
 ORDER BY course_id, user_id, cal_week_number

/* Query 3-Adds readable weekly date ranges to sp20_weekly_session_navtime */


CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao21.Xiao22_sp20_weekly_activity` AS 
SELECT 
  course_id as canvas_course_id,
  course_sis_id,
  canavs_user_id,
  prsn_univ_id,
  course_section_sis_source_id,
  cal_week_number,
  CASE WHEN cal_week_number = 2 THEN '01/06/20-01/12/20' 
       WHEN cal_week_number = 3 THEN '01/13/20-01/19/20'
       WHEN cal_week_number = 4 THEN '01/20/20-01/26/20'
       WHEN cal_week_number = 5 THEN '01/27/20-02/02/20'
       WHEN cal_week_number = 6 THEN '02/03/20-02/09/20'
       WHEN cal_week_number = 7 THEN '02/10/20-02/16/20'
       WHEN cal_week_number = 8 THEN '02/17/20-02/23/20'
       WHEN cal_week_number = 9 THEN '02/24/20-03/01/20'
       WHEN cal_week_number = 10 THEN '03/02/20-03/08/20'
       WHEN cal_week_number = 11 THEN '03/09/20-03/15/20'
       WHEN cal_week_number = 12 THEN '03/16/20-03/22/20'
       WHEN cal_week_number = 13 THEN '03/23/20-03/29/20'
       WHEN cal_week_number = 14 THEN '03/31/20-04/05/20'
       WHEN cal_week_number = 15 THEN '04/06/20-04/12/20'
       WHEN cal_week_number = 16 THEN '04/13/20-04/19/20'
       WHEN cal_week_number = 17 THEN '04/20-20-04/26/20'
       WHEN cal_week_number = 18 THEN '04/27/20-05/03/20'
  ELSE '>=05/04/20' END as week,
  num_sessions,
  weekly_navigation_duration
 FROM `iu-uits-pti-elearninglab.Xiao21.sp20_student_navigation_duration` 
 
 
/*Query 4-Obfuscates identifiers in weekly metrics tables for later joins */

CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.SP20_Canvas_Weekly_Activity_Metrics` AS  
  WITH canvas_activity AS ( 
  SELECT
    SHA256(CAST(canvas_course_id AS STRING)) as canvas_course_id,
    SHA256(prsn_univ_id) as student_id,
    SPLIT(course_sis_id,'-') as course_sis_id_array,
    CASE WHEN course_section_sis_source_id IS NOT NULL THEN  SPLIT(course_section_sis_source_id,'-')  ELSE SPLIT(course_sis_id,'-') END as course_section_sis_id_array,
    cal_week_number,
    week,
    num_sessions,
    weekly_navigation_duration
  FROM `iu-uits-pti-elearninglab.Xiao21.Xiao22_sp20_weekly_activity` 
 )

SELECT
  canvas_course_id,
  student_id,
  SHA256(course_sis_id_array[OFFSET(4)]) as canvas_course_sis_source_id_cls_nbr,
  SHA256(canvas_activity.course_section_sis_id_array[OFFSET(4)]) as canvas_course_section_sis_source_id_cls_nbr,
  cal_week_number,
  week,
  num_sessions,
  weekly_navigation_duration
FROM canvas_activity
 
 
/*Query 5-Derived from Xiao21 Request; This query adds section enrollment details within Canvas course sites to the dataset */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.sp20_canvas_override_detail` AS   
WITH dataset_detail AS (
SELECT 
  a.prsn_univ_id,
  b.user_id,
  a.course_canvas_id,
  a.acad_term_cd,
  a.cls_nbr,
  c.type as enroll_type,
 c.workflow_state, 
  a.score_updated_at,
  c.course_id, 
  c.course_section_id,
  SPLIT(d.sis_source_id,'-') as course_section_sis_array,
  a.has_weighted_assignments,
  a.overall_canvas_score,
  a.assn_est_score_05_08,
  a.has_override,
  a.override_score,
  a.override_grade,
  a.override_updated_at
 FROM `iu-uits-pti-elearninglab.Xiao21.dataset` a
 INNER JOIN `iu-uits-tlt-la.canvas_data.pseudonym_dim` b ON a.prsn_univ_id = b.sis_user_id
 INNER JOIN `iu-uits-tlt-la.canvas_data.enrollment_dim` c ON b.user_id = c.user_id AND CAST(CONCAT('29000000',CAST(a.course_canvas_id AS STRING)) AS INT64) = c.course_id
 INNER JOIN `iu-uits-tlt-la.canvas_data.course_section_dim` d ON c.course_section_id = d.id
 WHERE c.type = "StudentEnrollment" and c.workflow_state = 'active' AND d.sis_source_id IS NOT NULL AND d.sis_source_id like 'SP20%')

SELECT
  DISTINCT
  prsn_univ_id,
  user_id,
  course_canvas_id,
  acad_term_cd,
  cls_nbr as course_canvas_cls_nbr,
  course_section_sis_array[ORDINAL(5)] as course_section_cls_nbr,
  has_weighted_assignments,
  overall_canvas_score,
  assn_est_score_05_08,
  has_override,
  override_grade,
  override_updated_at,
FROM dataset_detail

/* Query 6-This query obfuscates identifiers within the new Canvas grade detail dataset */

CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.SP20_Canvas_Grade_Dataset2` AS    
SELECT  
  DISTINCT
  SHA256(prsn_univ_id) as student_id,
  acad_term_cd,
  SHA256(CAST(course_canvas_id AS STRING)) as canvas_course_site_id,
  SHA256(course_canvas_cls_nbr) as canvas_course_site_cls_nbr,
  SHA256(course_section_cls_nbr) as canvas_course_enrl_section_cls_nbr,
  has_weighted_assignments,
  score_updated_at,
  overall_canvas_score,
  assn_est_score_05_08,
  has_override,
  override_grade,
  override_updated_at
FROM `iu-uits-pti-elearninglab.Xiao22.sp20_canvas_override_detail` LIMIT 1000 
 
/* Query 7-From Xiao22_sp20_weekly_activity, create obfuscated data */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.SP20_Canvas_Weekly_Activity_Metrics` AS     
  SELECT
    SHA256(CAST(canvas_course_id AS STRING)) as canvas_course_id,
    SHA256(prsn_univ_id) as student_id,
    cal_week_number,
    week,
    num_sessions,
    weekly_navigation_duration
  FROM `iu-uits-pti-elearninglab.Xiao21.Xiao22_sp20_weekly_activity` 

/* The next queries are branches depending on joining the data or keeping data separate */


/* Query 8-Retention Data; This obfuscates identifiers of the retention data table */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.retn_data` AS 
SELECT
  SHA256(a.PRSN_UNIV_ID) as student_id,
  COHORT_SP_FA as retn_cohort_sp_fa,
  COHORT_TERM_CD as retn_cohort_term_cd,
  COHORT_TERM_DESC as retn_cohort_term_desc,
  NEW_STUDENT_CATEGORY as retn_new_student_catogry,
  ETHNICITY as retn_ethnicity,
  GENDER as retn_gender,
  RESIDENCY as retn_residency,
  TWNTYFRST_CENT_SCHOLAR as retn_twntyfrst_cent_scholar,
  TWNTYFRST_CENT_AWARD as retn_cent_award,
  FIRST_GENERATION as retn_first_generation,
  PELL_ELIGIBILITY as retn_pell_eligibility,
  HIGH_SCHOOL_GPA as retn_high_school_gpa,
  FORMER_ACP_IND as retn_former_acp_ind,
  SAT_DRVD_COMPOSITE_SCORE as retn_sat_drvd_composite_score,
  SAT_DRVD_RANGE as retn_sat_drvd_range,
  SAT_ACT_INDICATOR as retn_sat_act_indicator,
  SAT_DRVD_COMPOSITE_SCORE_2019 as retn_sat_drvd_composite_score_2019,
  SAT_DRVD_RANGE_2019 as retn_sat_drvd_range_2019,
  SAT_ACT_INDICATOR_2019  as retn_sat_act_indicator_2019,
  SAT_ERWS_SCORE as retn_sat_erws_score,
  SAT_ERWS_SOURCE as retn_sat_erws_source,
  SAT_MSS_SCR as retn_sat_mss_scr,
  SAT_MSS_SOURCE as retn_sat_mss_source,
  ENTRY_AGE as retn_entry_age,
  ENTRY_ACADEMIC_LOAD as retn_entry_academic_load,
  ENTRY_GPA as retn_entry_gpa,
  ENTRY_UNITS as retn_entry_units,
  ENTRY_SCHOOL as retn_entry_school,
  ENTRY_INTENDED_SCHOOL as retn_entry_intended_school,
  ENTRY_PROGRAM as retn_entry_program,
  ENTRY_MAJOR_CODE as retn_entry_major_code,
  ENTRY_MAJOR_DESC as retn_entry_major_desc,
  TERM_SEQUENCE as retn_term_sequence,
  TERM_SEQUENCE_INST as retn_term_sequence_inst,
  TERM_CODE as retn_term_code,
  TERM_STATUS  as retn_term_status,
  TERM_INST_CD  as retn_term_inst_cd,
  CLASS_LEVEL as retn_class_level,
  CLASS_NAME as retn_class_name,
  EXPANDED_CLASS_LEVEL as retn_expanded_class_level,
  SCHOOL_CODE as retn_school_code,
  SCHOOL_DESC as retn_school_desc,
  INTENDED_SCHOOL as retn_intended_school,
  TERM_PRIMARY_PROGRAM_CODE as retn_primary_program_code,
  TERM_PRIMARY_PROGRAM_DESC as retn_term_primary_program_desc,
  TERM_MAJOR_1_CODE as retn_term_major_1_code,
  TERM_MAJOR_1_DESC as retn_term_major_1_desc,
  TERM_MAJOR_1_IPEDS_CIP_CD as retn_term_maj,
  TERM_MAJOR_2_CODE as retn_term_major_2_code,
  TERM_MAJOR_2_DESC as retn_term_major_2_desc,
  TERM_MAJOR_2_IPEDS_CIP_CD as retn_term_major_2_ipeds_cip_cd,
  TERM_MAJOR_3_CODE as retn_term_major_3_code,
  TERM_MAJOR_3_DESC as retn_term_major_3_desc,
  TERM_MAJOR_3_IPEDS_CIP_CD as retn_term_major_3_ipeds_cip_cd,
  TERM_START_UNITS as retn_term_start_units,
  TERM_START_CUM_UNITS as retn_term_start_cum_units,
  TERM_START_CUM_GPA as retn_term_start_cum_gpa,
  TERM_END_UNITS as retn_term_end_units,
  TERM_END_EARNED_UNITS  as retn_term_end_earned_units,
  TERM_END_GPA as retn_term_end_gpa,
  TERM_END_CUM_GPA as retn_term_end_cum_gpa,
  BA_DEGREE_RECEIVED as retn_ba_degree_received,
  DEGREE1_DESC as retn_degree1_desc,
  DEGREE1_LEVEL as retn_degree1_level,
  DEGREE1_INST_CD as retn_degree1_inst_cd,
  DEGREE1_CUM_GPA as retn_degree1_cum_gpa,
  DEGREE1_SCHOOL as retn_degree1_school,
  DEGREE1_CMPLTN_TERM as retn_degree1_cmpltn_term,
  MOS_TO_DEGREE1 as retn_mos_to_degree1,
  DEGREE2_DESC as retn_degree2_desc,
  DEGREE2_LEVEL as retn_degree2_level,
  DEGREE2_INST_CD as retn_degree2_inst_cd,
  DEGREE2_CUM_GPA as retn_degree2_cum_gpa,
  DEGREE2_SCHOOL as retn_degree2_school,
  DEGREE2_CMPLTN_TERM as retn_degree2_cmpltn_term,
  MOS_TO_DEGREE2 as retn_mos_to_degree2,
  DEGREE3_DESC as retn_degree3_desc,
  DEGREE3_LEVEL as retn_degree3_level,
  DEGREE3_INST_CD as  retn_degree3_inst_cd,
  DEGREE3_SCHOOL as retn_degree3_school,
  DEGREE3_CMPLTN_TERM as retn_degree3_cmpltn_term,
  MOS_TO_DEGREE3 as retn_mos_to_degree3,
  DEGREE_MAJOR1_CD as retn_degree_major1_cd,
  DEGREE_MAJOR1 as retn_degree_major1,
  DEGREE_MAJOR1_CIP_CD as retn_degree_major1_cip_cd,
  DEGREE_MAJOR2_CD as retn_degree_major2_cd,
  DEGREE_MAJOR2 as retn_degree_major2,
  DEGREE_MAJOR2_CIP_CD as retn_degree_major2_cip_cd,
  DEGREE_MAJOR3_CD as retn_degree_major3_cd,
  DEGREE_MAJOR3 as retn_degree_major3,
  DEGREE_MAJOR3_CIP_CD as retn_degree_major3_cip_cd,
  DEGREE_LEVEL_4_YEARS as retn_degree_level_4_years,
  DEGREE_LEVEL_6_YEARS as retn_degree_level_6_years,
  DEGREE_LEVEL_8_YEARS as retn_degree_level_8_years,
  URM_FLAG as retn_urm_flag,
  STU_1ST_TRM_MIN_CLS_SZ_NBR as retn_stu_1st_trm_min_cls_sz_nbr,
  STU_1ST_TRM_MED_CLS_SZ_NBR as retn_stu_1st_trm_med_cls_sz_nbr,
  STU_1ST_TRM_MAX_CLS_SZ_NBR as retn_stu_1st_trm_max_cls_sz_nbr,
  STU_1ST_CERT_TERM_CD as retn_stu_1st_cert_term_cd,
  STU_1ST_CERT_TERM_GRP_CD as retn_stu_1st_cert_term_grp_cd,
  ADMIT_STATUS as retn_admit_status,
  TIME_2_CERT as retn_time_2_cert,
  HOUSING_TYPE_DESC as retn_housing_type_desc,
  CENTRENAME as retn_centrename,
  CATEGORY as retn_category,
  LCNAME as retn_lcname,
  ROOMNBR as retn_roomnbr,
  STU_1ST_TRM_ACP_HRS as retn_stu_1st_trm_acp_hrs,
  STU_1ST_TRM_NON_ACP_DCR_HRS as retn_stu_1st_trm_non_acp_dcr_hrs,
  STU_1ST_TRM_AP_TST_HRS as retn_stu_1st_trm_ap_tst_hrs,
  STU_1ST_TRM_OTHER_TST_HRS as retn_stu_1st_trm_other_tst_hrs ,
  STU_1ST_TRM_XFR_DCR_HRS as retn_stu_1st_trm_xfr_dcr_hrs,
  STU_1ST_TRM_OTHER_XFR_HRS as retn_stu_1st_trm_other_xfr_hrs,
  STU_1ST_TRM_OTHER_IU_HS_HRS as retn_stu_1st_trm_other_iu_hs_hrs
FROM `iu-uits-pti-elearninglab.Xiao22.ir_bl_ugrd_retn_2022` 

/* Query 9-Course Enrollment Data; this query obfu */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.Xiao22.crs_enrl_data` AS 
SELECT
  SHA256(b.PRSN_UNIV_ID) AS crs_enrl_student_id,
  COHORT_TERM_CD as crs_enrl_cohort_term_cd,
  NEW_STUDENT_CATEGORY as crs_enrl_new_student_category, 
  inst_cd as crs_enrl_isnt_cd,
  ACAD_UNT_TKN_NBR as crs_enrl_acad_unt_tkn_nbr,
  CRS_TYPE as crs_enrl_crs_type,
  CRS_DESC as crs_enrl_crs_category,
  EARNED_BFORE_COHORT as crs_enrl_bfore_cohort,
  SHA256(CAST(b.CRS_ID AS STRING)) as crs_enrl_crs_id,
  CRSOFR_NBR as crs_enrl_crsofr_nbr,
  CRS_SUBJ_CD as crs_enrl_crs_subj_cd,
  CRS_SUBJ_DEPT_CD as crs_enrl_crs_subj_dept_cd,
  CRS_CATLG_NBR as crs_enrl_crs_catlg_nbr,
  CRS_NM as crs_enrl_crs_name,
  CRS_DESC as crs_enrl_crs_desc,
  CRS_CMPNT_CD as crs_enrl_cmpnt_cd,
  CRS_OFCL_GRD_CD as crs_enrl_crs_ofcl_grd_cd,
  CRS_LTTR_GRD as crs_enrl_crs_lttr_grd,
  CRS_OFCL_GRD_NBR as crs_enrl_crs_ofcl_grd_nbr,
  CRS_REPEAT_CD as crs_enrl_crs_repeat_cd,
  SHA256(cls_nbr) as crs_enrl_cls_nbr,
  cls_sect_cd as crs_enrl_cls_sect_cd,
  SHA256(CLS_INSTR_UNIV_ID) as crs_enrl_cls_instr_id,
  CLS_INSTRC_MODE_CD as crs_enrl_cls_instrc_mode_cd,
  STU_GRD_PTS_NBR as crs_enrl_stu_grd_pts_nbr,
  STU_GRD_BASIS_CD as crs_enrl_stu_grd_basis_cd,
  STU_DRVD_CLS_ENRL_STAT_IND as crs_enrl_stu_drvd_cls_enrl_stat_ind,
  STU_EARN_CRDT_IND as crs_enrl_stu_earn_crdt_ind,
  STU_ENRL_DRP_DT as crs_enrl_stu_enrl_drp_dt,
  GPAO as crs_enrl_gpao,
  CLS_ONLINE_IND as crs_enrl_cls_online_ind,
  CLS_OVST_IND as crs_enrl_cls_ovst_ind,
  CLS_OVST_OR_ABROAD_IND as crs_enrl_cls_ovst_or_abroad_ind,
  class_session as crs_enrl_cls_session,
  CLASS_SESSION_DESC as crs_enrl_class_session_desc,
FROM `iu-uits-pti-elearninglab.Xiao22.crs_enrl_2022`