/* Prepares basic log view from raw events subsetted to Fall'19 through Spring'21. */
/* Processes 460.03 GB in 48 secs * 11/01/21/
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs_f19_sp21_di` AS 
WITH array_asset AS (
SELECT 
    id, 
    course_offering_id,
    user_sis_id,
    event_time,
    asset_name,
    split(asset_name, '.') as asset_array,
    asset_type,
    asset_subtype,
    user_agent,
    request_url
FROM `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs_f19_s21` a
INNER JOIN `iu-uits-pti-elearninglab.elliott21_dev.fa19_sp21_stdnt_demo` b ON a.user_sis_id = b.prsn_univ_id
)
SELECT 
    id, 
    course_offering_id,
    SHA256(user_sis_id) as student_id,
    event_time,
    asset_type,
    asset_subtype,
    CASE WHEN asset_type = 'attachment' THEN CONCAT('.',asset_array[ORDINAL(ARRAY_LENGTH(asset_array))]) ELSE NULL END as asset_extension,
    user_agent,
    request_url
FROM array_asset

/* Prepares course details table in shared dataset*/
/* Processed 7.09 MB in 2 secs */

CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot_canvas_logs.course_table` AS 
    SELECT 
           acad_term_cd,
           acad_term_desc,
           course_canvas_id,
           inst_cd,
           acad_org_cd,
           acad_grp_cd,
           crs_subj_cd,
           crs_nbr_cat,
           cls_instrc_mode_desc,
           instr_type,
           cen_enrl_cnt
    FROM `iu-uits-pti-elearninglab.elliott21_dev.elliot_course_dtls`
	
/* Creates de-identified demographic table in shared data set */
/* 13.42 MB processed in 2 secs */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot_canvas_logs.canvas_logs_f19sp21` AS   
    SELECT * 
    FROM `iu-uits-pti-elearninglab.elliott21_dev.fa19_sp21_stdnt_demo_di`

/* Copies logs into shared data set */
/* Processes 326.93 GB in 45 seconds into shared dataset */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot_canvas_logs.canvas_logs_f19sp21` AS   
    SELECT * 
    FROM `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs_f19_sp21_di`

/* Processes Program level features of courses into the shared dataset */
/* 60.91 KB processed in 1 sec*/