/* Generate underlying event table */
/*08-16-21 runtime: 1 min 7 sec elapsed, 951.6 GB processed*/
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs` AS 
SELECT DISTINCT id, 
                course_offering_id, 
                json_extract_scalar(event,"$['actor']['extensions']['com.instructure.canvas']['user_sis_id']") AS `user_sis_id`, 
                event_time,
                json_extract_scalar(event,"$['object']['extensions']['com.instructure.canvas']['asset_type']") AS asset_type, 
                json_extract_scalar(event,"$['object']['extensions']['com.instructure.canvas']['asset_subtype']") AS asset_subtype, 
                json_extract_scalar(event,"$[extensions]['com.instructure.canvas'][user_agent]") user_agent, 
                json_extract_scalar(event,"$['extensions']['com.instructure.canvas']['request_url']") AS request_url 
FROM `udp-iu-prod.event_store.events` WHERE event_time BETWEEN '2019-08-26' AND '2019-12-30' AND json_extract_scalar(event,"$['membership']['roles'][0]") = "Learner" AND json_extract_scalar(event,"$['actor']['extensions']['com.instructure.canvas']['user_sis_id']") IS NOT NULL ORDER BY user_sis_id, course_offering_id, event_time ASC
/* De-identifying student ids*/
/* 8-20-21 runtime: ~30s, 42.1 GB processed*/
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs` AS
	SELECT id,
		   course_offering_id,
		   SHA256(user_sis_id) as student_id /* hash student identifier*/,
		   event_time,
		   asset_type,
		   asset_subtype,
		   user_agent,
		   request_url
	FROM `iu-uits-pti-elearninglab.elliott21_dev.canvas_logs` 
/* Create activity logs of just students from demo data */
/* 8-20-21 42GB processed ~10s */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot21_dev.canvas_log_data` AS 
	SELECT a.*
	FROM `iu-uits-pti-elearninglab.elliot21_dev.canvas_logs` 
	LEFT JOIN `iu-uits-pti-elearninglab.stndt_incl_list` b ON a.user_sis_id = b.psrn_univ_id
	WHERE b.prsn_univ_id IS NOT NULL 
/* Copy over to shared dataset in BigQuery */
/* 8-20-21 ~30s runtime; 42.1 GB processed */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot_canvas_logs.fa19_canvas_log_data` AS   
    SELECT * 
    FROM `iu-uits-pti-elearninglab.elliott21_dev.fa19_canvas_log_data`
/* Demographic Data*/
/* Generated in IU instance of Denodo*/
/* Create table of latest rows in IU Institutional Reporting Census Data*/
CREATE OR REPLACE VIEW p_ir_cen_trm_snpsht_gt FOLDER = '/elliott21' AS 
	SELECT ir_cen_trm_snpsht_gt.prsn_univ_id AS prsn_univ_id, 
			max(ir_cen_trm_snpsht_gt.census_row_crt_dt) AS ltst_rw_dt 
	FROM ir_cen_trm_snpsht_gt WHERE (acad_term_cd = 4198 AND stu_drv_enrl_stat_ind = 'E') 
	GROUP BY ir_cen_trm_snpsht_gt.prsn_univ_id;
ALTER VIEW p_ir_cen_trm_snpsht_gt
 LAYOUT (ir_cen_trm_snpsht_gt = [20, 20, 314, 1745]);
/* Returns 112664 rows*/
/* Join back in view with requisite demographic information */
CREATE OR REPLACE VIEW fa19_stndt_demo FOLDER = '/elliott21' AS 
SELECT ir_cen_trm_snpsht_gt.prsn_univ_id AS prsn_univ_id, 
       ir_cen_trm_snpsht_gt.prsn_gndr_desc AS gender, 
	   ir_cen_trm_snpsht_gt.prsn_drvd_age_nbr AS prsn_drvd_age_nbr, 
	   ir_cen_trm_snpsht_gt.ir_rpt_inst_cd AS ir_rpt_inst_cd, 
	   case WHEN (ir_cen_trm_snpsht_gt.acad_load_ind = 'F') THEN 'Full-time' ELSE 'Part-time' END AS acad_load_ind, 
	   ir_cen_trm_snpsht_gt.acad_prm_pgm_desc AS acad_prm_pgm_desc 
FROM p_ir_cen_trm_snpsht_gt AS p_ir_cen_trm_snpsht_gt 
INNER JOIN ir_cen_trm_snpsht_gt AS ir_cen_trm_snpsht_gt ON (p_ir_cen_trm_snpsht_gt.prsn_univ_id = ir_cen_trm_snpsht_gt.prsn_univ_id 
AND p_ir_cen_trm_snpsht_gt.ltst_rw_dt = ir_cen_trm_snpsht_gt.census_row_crt_dt)  
WHERE (ir_cen_trm_snpsht_gt.acad_term_cd = 4198 AND ir_cen_trm_snpsht_gt.acad_load_ind <> 'X' 
AND ir_cen_trm_snpsht_gt.ir_rpt_inst_cd is not null );
ALTER VIEW fa19_stndt_demo
 LAYOUT (p_ir_cen_trm_snpsht_gt = [98, 146, 200, 66], ir_cen_trm_snpsht_gt = [672, 196, 200, 225]);
/* Returns 93,064 rows*/
/* Output modified in BigQuery project*/
/* 8-20-21 7.7MB processed < 1s. 
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliott21_dev.fa19_stndt_demo_data` AS 
    SELECT SHA256(prsn_univ_id) as student_id,
    gender, 
    prsn_drvd_age_nbr as age,
    ir_rpt_inst_cd as campus,
    acad_prm_pgm_desc as program_major 
FROM `iu-uits-pti-elearninglab.elliott21_dev.fa19_stdnt_demo`
WHERE acad_load_ind IS NOT NULL
/* Get student inclusion list */
/* 8-20-21; 7.7MBs processed; < 1s
CREATE OR REPLACE `iu-uits-pti-elearninglab.elliot21_dev.stndt_incl_list` AS 
	SELECT DISTINCT prsn_univ_id
	FROM `iu-uits-pti-elearninglab.elliott21_dev.fa19_stdnt_demo`
/* Copy data to shared BigQuery workspace */
/* 8-20-21; 7.7 MBs processed; > 1s */
CREATE OR REPLACE TABLE `iu-uits-pti-elearninglab.elliot_canvas_logs.fa19_stndt_demo_data` AS    
    SELECT * 
    FROM `iu-uits-pti-elearninglab.elliott21_dev.fa19_stndt_demo_data`