SELECT DISTINCT course_global_id, course_code
FROM `iu-uits-tlt-la-dev.UCFT_F19`.enrollments
job runtime: 1.6 seconds
Bytes proessed: 3.5 MB
CREATE OR REPLACE TABLE `iu-uits-tlt-la-dev.UCFT_F19`.assignments AS
WITH assignments AS (
SELECT course_global_id,course_code, id as assignment_id, title as assignment_title, due_at as assignment_due_date,  
FROM `iu-uits-tlt-la-dev.UCFT_F19`.courses INNER JOIN `iu-uits-tlt-la.canvas_data`.assignment_dim ON course_global_id = course_id 
WHERE due_at IS NOT NULL
AND submission_types NOT IN ('on_paper','','Assignments','none', 'external_tool')
AND points_possible > 0
AND points_possible IS NOT NULL
AND workflow_state = 'published'
AND EXTRACT(DATE from due_at) < CURRENT_DATE()
ORDER BY course_global_id, assignment_due_date ASC)

SELECT course_global_id, course_code, count(assignment_id) AS assignments_due
FROM assignments
GROUP BY course_global_id, course_code
job runtime: 2.5 seconds
Bytes Processed: 265.25 MB
CREATE OR REPLACE TABLE `iu-uits-tlt-la-dev.UCFT_F19`.submissions AS 
WITH assignments AS (
SELECT course_global_id,course_code, id as assignment_id, title as assignment_title, due_at as assignment_due_date,  
FROM `iu-uits-tlt-la-dev.UCFT_F19`.courses INNER JOIN `iu-uits-tlt-la.canvas_data`.assignment_dim ON course_global_id = course_id 
WHERE due_at IS NOT NULL
AND submission_types NOT IN ('on_paper','','Assignments', 'not_graded','none','external_tool')
AND points_possible > 0
AND points_possible IS NOT NULL
AND workflow_state = 'published'
AND EXTRACT(DATE from due_at) < CURRENT_DATE()
ORDER BY course_global_id, assignment_due_date ASC),
submissions AS (SELECT
  course_global_id,
  course_code,
  assn.assignment_id,
  assignment_title,
  assignment_due_date,
  user_id,
  id as submission_id,
  submitted_at
FROM assignments assn INNER JOIN `iu-uits-tlt-la.canvas_data`.submission_dim sub ON assn.assignment_id = sub.assignment_id
WHERE sub.workflow_state NOT IN ('unsubmitted','deleted')
AND sub.submitted_at IS NOT NULL
ORDER BY course_global_id,course_code,assignment_due_date ASC)
SELECT 
course_global_id,
course_code,
user_id,
count(submission_id) as submissions
FROM
submissions
GROUP BY
course_global_id,
course_code,
user_id
ORDER BY
course_global_id,
course_code,
user_id
job runtime: 3.8 seconds
Bytes processed: 5.8 GB
CREATE OR REPLACE TABLE `iu-uits-tlt-la-dev.UCFT_F19`.navigationTime AS
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,
    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`.events 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-05-01' AND CURRENT_DATE()
    AND JSON_EXTRACT_SCALAR(event,"$[actor][type]") = "Person"
    AND action = 'NavigatedTo'), 
  sessionNavigationDurations AS (SELECT
    course_id,
    user_id,
    session_id,
    SUM(timebetweenEvents) AS navigationDuration
  FROM
    sessionNavigationEvents
  WHERE
    timebetweenEvents <= 1500 --ignore periods of inactivity > 25 minutes
  GROUP BY
    course_id,
    user_id,
    session_id)

SELECT University_ID, course_code, ROUND(sum(navigationDuration)/60,2) as navigationTime
FROM sessionNavigationDurations a RIGHT JOIN `iu-uits-tlt-la-dev.UCFT_F19`.enrollments en ON CAST(course_id as INT64) = en.course_canvas_id
    AND CAST(a.user_id  AS INT64) = canvas_user_id
GROUP BY  University_ID, course_code
ORDER BY University_ID
    
  

Job Runtime: 12.1 seconds
Bytes Processed: 84.88 GB
CREATE OR REPLACE TABLE `iu-uits-tlt-la-dev.UCFT_F19`.Student_Metrics AS
WITH enrollment_breakdown AS (SELECT 
en.University_ID,
en.course_code,
navigationTime,
assignments_due,
submissions
FROM `iu-uits-tlt-la-dev.UCFT_F19`.enrollments en LEFT JOIN `iu-uits-tlt-la-dev.UCFT_F19`.assignments assn ON en.course_global_id = assn.course_global_id LEFT JOIN `iu-uits-tlt-la-dev.UCFT_F19`.submissions sub ON en.course_global_id = sub.course_global_id AND en.global_user_id = sub.user_id LEFT JOIN `iu-uits-tlt-la-dev.UCFT_F19`.navigationTime nav ON nav.University_ID = en.University_ID AND nav.course_code = en.course_code
ORDER BY University_ID)
SELECT University_ID,sum(navigationTime) as Total_Active_Time,sum(assignments_due) as Total_Assignments_Due, sum(submissions) as Total_Submissions
FROM enrollment_breakdown
GROUP BY University_ID
ORDER BY University_ID
Job Runtime: 3.2 seconds
Bytes processed: 12.42 MB