awful query

jd.sql

--  Park cursor here on Line 1, hit RUN button

--  To covert from selectable to runtime, mass substitute 23192 or similar valid canvas_id for a course instead of :course

--   Engagement for Snapshot:
--                   Run in 2 sections...select and run from line 314 up first, then remainder
--                   Tables labeled with my initials had to be made in the public schema because I am read-only to Redshift.
--                   They are temporary tables and drop at the close of session.
--                   Engagement Score represents the computed_final_score (final course grade, 0 - 100) we would predict if using Engagement relative to the atomic section cohort as the sole predictor.
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#4-engagement-charts :
--                            The engagement score shall always be calculated on a term-to-date basis.
--                            The scoring shall be run each week, the results saved, and the trend plotted (approximately as above).
--                            The engagement score is comprised of three derived activity metrics, z-scored by atomic course section, converted to LTCP, and then regressed against the response final_computed_score.
--                            These activity metrics are the time weighted average of the percent elapsed window in assignment submission, the total quiz time elapsed, and the total count of discussion topics and entries created.
--                            The engagement score shall be presented as varying between 0 and 100.
--                            For the purposes of compatibility with LTI, do not use Canvas field course_section_id for aggregation, grouping, or z-score. Instead consider the unique combination of course_id and enrollment_term_id to be the atomic course section.


--   Regarding Assignments:
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#what-is-a-valid-assignment :
--                            assignment_dim.workflow_state (varchar). To be valid, assignments must be in a published workflow state
--                            Use: assignment_dim.points_possible (double precision). This value may be null or 0. To be valid, assignments must have a points_possible value that is not null and not 0
--                            It must be part of an 'available' assignment group. Use: assignment_dim.assignment_group_id (bigint) and assignment_group_dim.workflow_state (varchar). In Canvas, assignments must be part of an assignment group.

--                   https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#what-is-a-valid-assignmentquiz
--                            there must be a due_at value to define when the assignment or quiz is due.


--   Regarding Overrides:
--                   https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#determine-due_at-on-per-student-basis
--                            Given a valid student and valid assignment/quiz, we can determine if an override applies by:
--                            Looking in assignment_override_user_rollup_fact for a combination of user_id and assignment_id/quiz_id.
--                            Join assignment_override_dim on assignment_override_user_rollup_fact.assignment_override_id
--                            Allowing only assignment_override_dim rows that have a workflow_state value of active
--                            Allowing only assignment_override_dim rows that have due_at_overridden value of new_due_at
--                            Allowing only assignment_override_dim rows that have a non-null due_at value.
--                   Similar logic has been applied to unlock_at and lock_at


--   Regarding Quizzes:
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#what-is-a-valid-quiz :
--                            Use: quiz_dim.workflow_state (varchar). Quizzes have a workflow_state value. To be valid, quiz workflow_states must be 'published.'
--                            Use: quiz_dim.quiz_type (varhcar). To be valid, quizzes must have an 'assignment' quiz_type.
--                            Use: quiz_dim.points_possible (double precision). To be valid, points_possible must not be null and must be greater than 0.
--                            Quizzes are not required to have due dates. In Snapshot, quizzes without a due_at (timestamp) are be (sic) valid data sources for Quiz charts.

--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#what-is-a-valid-assignmentquiz
--                            there must be a due_at value to define when the assignment or quiz is due.

--                   As product specification contradicts itself on whether quizzes must have due_at timestamp to be valid, due_at is ignored here as irrelevant to calculation at JDF discretion


--  Regarding Courses:
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#4-engagement-charts
--                            Consider only those courses where workflow_state is available or completed


--  Regarding Enrollments:
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#list-of-students :
--                            Use: enrollment_dim. Only enrollments with a workflow_state = 'active' and type = 'StudentEnrollment' count as valid students.


-- Regarding Start of Course:
--                   Per https://github.com/unizin/snapshot/wiki/Using-Canvas-Data-for-Snapshot#start-date


-- Optional manual housekeeping------------------------------------------------------------------------------------------------------- [JDF]
-- DROP TABLE raw_tidy;DROP TABLE JDF_tidy_1;DROP TABLE JDF_tidy_2;DROP TABLE JDF_tidy_3;DROP TABLE JDF_tidy_4;DROP TABLE JDF_tidy_5;DROP TABLE JDF_tidy_6;DROP TABLE JDF_tidy_7;DROP TABLE JDF_tidy_8;DROP TABLE JDF_tidy_9
-- DROP TABLE JDF_tidy_10;DROP TABLE JDF_tidy_11;DROP TABLE JDF_tidy_12;DROP TABLE JDF_tidy_13;DROP TABLE JDF_tidy_14;DROP TABLE JDF_tidy_15;DROP TABLE JDF_tidy_16;DROP TABLE JDF_tidy_17;DROP TABLE JDF_tidy_18



-- Begin raw_tidy -------------------------------------------------------------------------------------------------------------------- [JDF]


CREATE TEMP TABLE raw_tidy AS (




-- Begin Steve Huwig's original script, as modifed by JDF. Lines that JD touched are marked as [JDF] --------------------------------- [JDF]

WITH

-- course_assignment_groups subquery ------------------------------------------------------------------------------------------------- [JDF]

        course_assignment_groups AS
    (SELECT id
     FROM assignment_group_dim
     WHERE course_id = (SELECT id
                        FROM course_dim
                        WHERE canvas_id = :course)
           AND workflow_state = 'available'),

-- end course_assignment_groups subquery --------------------------------------------------------------------------------------------- [JDF]



-- course_assignment_submissions subquery -------------------------------------------------------------------------------------------- [JDF]

        course_assignment_submissions AS
    (SELECT
         submission_dim.*,
         submission_fact.score
     FROM submission_fact
         JOIN submission_dim ON submission_fact.submission_id = submission_dim.id
     WHERE assignment_group_id IN (SELECT id
                                   FROM course_assignment_groups)
           AND submission_dim.workflow_state = 'graded'
           AND submission_dim.grade_matches_current_submission = TRUE),

-- end course_assignment_submissions subquery ---------------------------------------------------------------------------------------- [JDF]



-- course_assignment_order subquery -------------------------------------------------------------------------------------------------- [JDF]

        course_assignment_order AS
    (SELECT
         id,
         created_at,
         unlock_at,  -- [JDF]
         due_at,
         lock_at,  -- [JDF]
         points_possible,
         row_number()
         OVER (
             ORDER BY coalesce(assignment_dim.due_at, '3000-1-1' :: DATE), assignment_dim.created_at) AS item_number
     FROM assignment_dim
     WHERE course_id = (SELECT id
                        FROM course_dim
                        WHERE canvas_id = :course)
           AND workflow_state = 'published'),

-- end course_assignment_order subquery ---------------------------------------------------------------------------------------------- [JDF]



-- course_assignment_overrides subquery ---------------------------------------------------------------------------------------------- [JDF]

        course_assignment_overrides AS
    (SELECT
         aourf.assignment_id,
         aourf.user_id,
         aod.due_at,
         aod.unlock_at, -- [JDF]
         aod.lock_at -- [JDF]
     FROM (SELECT *
           FROM assignment_override_user_rollup_fact
           WHERE course_id = (SELECT id
                              FROM course_dim
                              WHERE canvas_id = :course)) AS aourf
         JOIN assignment_override_dim AS aod
             ON aourf.assignment_override_id = aod.id
                AND workflow_state = 'active'
                AND ( -- [JDF]
                    (aod.due_at_overridden = 'new_due_at' AND aod.due_at IS NOT NULL)
                    OR  -- [JDF]
                    (aod.unlock_at_overridden = 'new_unlock_at' AND aod.unlock_at IS NOT NULL)  -- [JDF]
                    OR -- [JDF]
                    (aod.lock_at_overridden = 'new_lock_at' AND aod.lock_at IS NOT NULL)  -- [JDF]
                    )-- [JDF]
    ),

-- end course_assignment_overrides subquery ------------------------------------------------------------------------------------------ [JDF]



-- course_assignment_scores subquery ------------------------------------------------------------------------------------------------- [JDF]

        course_assignment_scores AS
    (SELECT
         user_id,
         assignment_id,
         item_number,
         submitted_at,
         round(score / points_possible * 100) AS user_score,
         due_at,
         course_assignment_order.created_at,
         course_assignment_order.unlock_at,  -- [JDF]
         course_assignment_order.lock_at,  -- [JDF]
         'assignment' :: TEXT                 AS item_type
     FROM course_assignment_order
         JOIN course_assignment_submissions
             ON course_assignment_order.id = course_assignment_submissions.assignment_id
     WHERE COALESCE(course_assignment_order.points_possible, 0) != 0  ),

-- end course_assignment_scores subquery --------------------------------------------------------------------------------------------- [JDF]



-- course_quizzes subquery ----------------------------------------------------------------------------------------------------------- [JDF]

        course_quizzes AS
    (SELECT *
     FROM quiz_dim
     WHERE course_id = (SELECT id
                        FROM course_dim
                        WHERE canvas_id = :course)
           AND workflow_state = 'published'
           AND quiz_type = 'assignment'
           AND coalesce(points_possible, 0) != 0),

-- end course_quizzes subquery ------------------------------------------------------------------------------------------------------- [JDF]



-- course_quiz_submissions subquery  ------------------------------------------------------------------------------------------------- [JDF]

        course_quiz_submissions AS
    (SELECT
         quiz_submission_dim.*,
         quiz_submission_fact.kept_score,
         quiz_submission_fact.assignment_id,
         quiz_submission_fact.time_taken AS quiz_elapsed
     FROM quiz_submission_fact
         JOIN quiz_submission_dim
             ON quiz_submission_fact.quiz_submission_id = quiz_submission_dim.id
     WHERE quiz_submission_fact.course_id = (SELECT id
                                             FROM course_dim
                                             WHERE canvas_id = :course)
           AND quiz_submission_dim.workflow_state = 'complete'
           AND quiz_submission_dim.submission_source = 'student'),

-- end course_quiz_submissions subquery  --------------------------------------------------------------------------------------------- [JDF]



-- course_quiz_order subquery  ------------------------------------------------------------------------------------------------------- [JDF]

        course_quiz_order AS
    (SELECT
         id,
         due_at,
         created_at,
         points_possible,
         row_number()
         OVER (
             ORDER BY coalesce(due_at, '3000-1-1' :: DATE), created_at) AS item_number
     FROM course_quizzes),

-- end course_quiz_order subquery  --------------------------------------------------------------------------------------------------- [JDF]



-- course_quiz_scores subquery  ------------------------------------------------------------------------------------------------------ [JDF]

        course_quiz_scores AS
    (SELECT
         user_id,
         assignment_id,
         quiz_id,
         item_number,
         finished_at,
         quiz_elapsed,
         round(kept_score / points_possible * 100) AS user_score,
         course_quiz_submissions.due_at,
         course_quiz_submissions.created_at,
         'quiz' :: TEXT                            AS item_type
     FROM course_quiz_order
         JOIN course_quiz_submissions
             ON course_quiz_order.id = course_quiz_submissions.quiz_id
     WHERE coalesce(points_possible, 0) != 0),

-- end course_quiz_scores subquery  -------------------------------------------------------------------------------------------------- [JDF]



-- enrolled students subquery  ------------------------------------------------------------------------------------------------------- [JDF]

        enrolled_students AS
    (SELECT
         enrollment_dim.id,
         enrollment_dim.user_id,
         min(address) AS address
     FROM enrollment_dim
         JOIN communication_channel_dim ON enrollment_dim.user_id = communication_channel_dim.user_id
     WHERE enrollment_dim.course_id = (SELECT id
                                       FROM course_dim
                                       WHERE canvas_id = :course)
           AND enrollment_dim.workflow_state = 'active'
           AND enrollment_dim.type = 'StudentEnrollment'
           AND communication_channel_dim.type = 'email'
     GROUP BY enrollment_dim.id, enrollment_dim.user_id),

-- end enrolled students subquery  --------------------------------------------------------------------------------------------------- [JDF]



-- section averages subquery  -------------------------------------------------------------------------------------------------------- [JDF]

        section_averages AS
    (SELECT
         min(computed_current_score)        AS section_min,
         avg(computed_current_score)        AS section_average,
         max(computed_current_score)        AS section_max,
         stddev_pop(computed_current_score) AS section_stddev
     FROM enrolled_students
         JOIN enrollment_fact ON enrolled_students.id = enrollment_fact.enrollment_id), -- [JDF, added ending comma]

-- end section averages subquery ----------------------------------------------------------------------------------------------------- [JDF]



-- discussion entry subquery - this entire subquery is new and added by JDF ---------------------------------------------------------- [JDF]

        disc_entries AS  -- [JDF]
    (SELECT  -- [JDF]
       discussion_entry_fact.user_id,  -- [JDF]
       discussion_entry_fact.discussion_entry_id, -- [JDF]
       discussion_entry_dim.created_at AS disc_entry_created-- [JDF]
    FROM discussion_entry_fact  -- [JDF]
       LEFT JOIN discussion_entry_dim ON discussion_entry_fact.discussion_entry_id = discussion_entry_dim.id  -- [JDF]
    WHERE discussion_entry_fact.course_id = (SELECT id -- [JDF]
                                            FROM course_dim  -- [JDF]
                                            WHERE canvas_id = :course)
       AND discussion_entry_fact.user_id IS NOT NULL),  -- [JDF]

-- end discussion entry subquery - this entire subquery is new and added by JDF ------------------------------------------------------ [JDF]



-- discussion topics subquery - this entire subquery is new and added by JDF --------------------------------------------------------- [JDF]

        disc_topics AS  -- [JDF]
    (SELECT  -- [JDF]
       discussion_topic_fact.user_id,  -- [JDF]
       discussion_topic_fact.discussion_topic_id, -- [JDF]
       discussion_topic_dim.created_at AS disc_topic_created-- [JDF]
    FROM discussion_topic_fact  -- [JDF]
       LEFT JOIN discussion_topic_dim ON discussion_topic_fact.discussion_topic_id = discussion_topic_dim.id  -- [JDF]
    WHERE discussion_topic_fact.course_id = (SELECT id -- [JDF]
                                            FROM course_dim  -- [JDF]
                                            WHERE canvas_id = :course)
       AND discussion_topic_fact.user_id IS NOT NULL)  -- [JDF]

-- end discussion topic subquery - this entire subquery is new and added by JDF ------------------------------------------------------ [JDF]



-- Steve Huwig's Original Main Query (joiner)  --------------------------------------------------------------------------------------- [JDF]

SELECT
    user_dim.canvas_id                                                                  AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                                                                             AS email,
    item_type,
    item_number,
    user_score,
    min(user_score)
    OVER (PARTITION BY item_number)                                                     AS item_min,
    avg(user_score)
    OVER (PARTITION BY item_number)                                                     AS item_average,
    max(user_score)
    OVER (PARTITION BY item_number)                                                     AS item_max,
    stddev_pop(user_score)
    OVER (PARTITION BY item_number)                                                     AS item_stddev,
    course_assignment_scores.created_at,
    coalesce(course_assignment_overrides.unlock_at, course_assignment_scores.unlock_at) AS unlock_at,  -- [JDF]
    submitted_at                                                                        AS completed_at,
    --not to be confused with enrollment_dim.completed_at, this is actually submission_dim.submitted_at renamed, incoming at line 103. It will UNION with quiz_submission_dim.finished_at from line 399   [JDF]
    coalesce(course_assignment_overrides.due_at, course_assignment_scores.due_at)       AS due_at,  -- [JDF]
    coalesce(course_assignment_overrides.lock_at, course_assignment_scores.lock_at)     AS lock_at,  -- [JDF]
    NULL :: NUMERIC                                                                     AS quiz_elapsed,  -- [JDF]
    NULL :: BIGINT                                                                      AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP                                                                   AS disc_entry_created,  -- [JDF]
    NULL :: BIGINT                                                                      AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP                                                                   AS disc_topic_created  -- [JDF]
FROM course_assignment_scores
    JOIN enrolled_students ON course_assignment_scores.user_id = enrolled_students.user_id
    JOIN user_dim ON course_assignment_scores.user_id = user_dim.id
    LEFT JOIN course_assignment_overrides
        ON course_assignment_scores.user_id = course_assignment_overrides.user_id
           AND course_assignment_scores.assignment_id = course_assignment_overrides.assignment_id


UNION ALL  --------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT
    user_dim.canvas_id                                                              AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                                                                         AS email,
    item_type,
    item_number,
    user_score,
    min(user_score)
    OVER (PARTITION BY item_number)                                                 AS item_min,
    avg(user_score)
    OVER (PARTITION BY item_number)                                                 AS item_average,
    max(user_score)
    OVER (PARTITION BY item_number)                                                 AS item_max,
    stddev_pop(user_score)
    OVER (PARTITION BY item_number)                                                 AS item_stddev,
    course_quiz_scores.created_at                                                   AS created_at, -- [JDF]
    NULL :: TIMESTAMP                                                               AS unlock_at,  -- [JDF]
    finished_at                                                                     AS completed_at, -- [JDF]
    --not to be confused with enrollment_dim.completed_at, this is actually quiz_submission_dim.finished_at renamed, incoming at line 212. It will UNION with submission_dim.submitted_at from line 361  [JDF]
    coalesce(course_assignment_overrides.due_at, course_quiz_scores.due_at)         AS due_at,
    NULL :: TIMESTAMP                                                               AS lock_at,  -- [JDF]
    quiz_elapsed :: NUMERIC, -- [JDF]
    NULL :: BIGINT                                                                  AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP                                                               AS disc_entry_created,  -- [JDF]
    NULL :: BIGINT                                                                  AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP                                                               AS disc_topic_created  -- [JDF]
FROM course_quiz_scores
    JOIN enrolled_students ON course_quiz_scores.user_id = enrolled_students.user_id
    JOIN user_dim ON course_quiz_scores.user_id = user_dim.id
    LEFT JOIN course_assignment_overrides
        ON course_quiz_scores.user_id = course_assignment_overrides.user_id
           AND course_quiz_scores.assignment_id = course_assignment_overrides.assignment_id


UNION ALL  -------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT DISTINCT
    user_dim.canvas_id    AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address               AS email,
    'quiz_status' :: TEXT AS item_type,
    1                     AS item_number,
    user_score,
    min(user_score)
    OVER ()               AS item_min,
    avg(user_score)
    OVER ()               AS item_average,
    max(user_score)
    OVER ()               AS item_max,
    stddev_pop(user_score)
    OVER ()                     AS item_stddev,
    NULL :: TIMESTAMP           AS created_at,
    NULL :: TIMESTAMP           AS unlock_at,  -- [JDF]
    NULL :: TIMESTAMP           AS completed_at,
    NULL :: TIMESTAMP           AS due_at,
    NULL :: TIMESTAMP           AS lock_at,  -- [JDF]
    NULL :: NUMERIC             AS quiz_elapsed,  -- [JDF]
    NULL :: BIGINT              AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP           AS disc_entry_created,  -- [JDF]
    NULL :: BIGINT              AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP           AS disc_topic_created  -- [JDF]
FROM course_quiz_scores
    JOIN enrolled_students ON course_quiz_scores.user_id = enrolled_students.user_id
    JOIN user_dim ON course_quiz_scores.user_id = user_dim.id


UNION ALL  -------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT DISTINCT
    user_dim.canvas_id     AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                AS email,
    'assig_status' :: TEXT AS item_type,
    1                      AS item_number,
    user_score,
    min(user_score)
    OVER ()                AS item_min,
    avg(user_score)
    OVER ()                AS item_average,
    max(user_score)
    OVER ()                AS item_max,
    stddev_pop(user_score)
    OVER ()                AS item_stddev,
    NULL :: TIMESTAMP      AS created_at,  -- [JDF]
    NULL :: TIMESTAMP      AS unlock_at,  -- [JDF]
    NULL :: TIMESTAMP      AS completed_at,
    NULL :: TIMESTAMP      AS due_at,  -- [JDF]
    NULL :: TIMESTAMP      AS lock_at,  -- [JDF]
    NULL :: NUMERIC        AS quiz_elapsed,  -- [JDF]
    NULL :: BIGINT         AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_entry_created,  -- [JDF]
    NULL :: BIGINT         AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_topic_created  -- [JDF]
FROM course_assignment_scores
    JOIN enrolled_students ON course_assignment_scores.user_id = enrolled_students.user_id
    JOIN user_dim ON course_assignment_scores.user_id = user_dim.id


UNION ALL  -------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT DISTINCT  --
    user_dim.canvas_id     AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                AS email,
    'disc_entry' :: TEXT   AS item_type,
    1                      AS item_number,
    NULL :: FLOAT          AS user_score,
    NULL :: FLOAT          AS item_min,
    NULL :: FLOAT          AS item_average,
    NULL :: FLOAT          AS item_max,
    NULL :: FLOAT          AS item_stddev,
    NULL :: TIMESTAMP      AS created_at,  -- [JDF]
    NULL :: TIMESTAMP      AS unlock_at,  -- [JDF]
    NULL :: TIMESTAMP      AS completed_at,
    NULL :: TIMESTAMP      AS due_at,  -- [JDF]
    NULL :: TIMESTAMP      AS lock_at,  -- [JDF]
    NULL :: NUMERIC        AS quiz_elapsed,  -- [JDF]
    discussion_entry_id, -- [JDF]
    disc_entry_created,  -- [JDF]
    NULL :: BIGINT         AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_topic_created  -- [JDF]
FROM disc_entries  -- [JDF]
    JOIN enrolled_students ON disc_entries.user_id = enrolled_students.user_id  -- [JDF]
    JOIN user_dim ON disc_entries.user_id = user_dim.id  -- [JDF]


UNION ALL  -------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT DISTINCT  --
    user_dim.canvas_id     AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                AS email,
    'disc_topic' :: TEXT   AS item_type,
    1                      AS item_number,
    NULL :: FLOAT          AS user_score,
    NULL :: FLOAT          AS item_min,
    NULL :: FLOAT          AS item_average,
    NULL :: FLOAT          AS item_max,
    NULL :: FLOAT          AS item_stddev,
    NULL :: TIMESTAMP      AS created_at,  -- [JDF]
    NULL :: TIMESTAMP      AS unlock_at,  -- [JDF]
    NULL :: TIMESTAMP      AS completed_at,
    NULL :: TIMESTAMP      AS due_at,  -- [JDF]
    NULL :: TIMESTAMP      AS lock_at,  -- [JDF]
    NULL :: NUMERIC        AS quiz_elapsed,  -- [JDF]
    NULL :: BIGINT         AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_entry_created,  -- [JDF]
    discussion_topic_id, -- [JDF]
    disc_topic_created  -- [JDF]
FROM disc_topics  -- [JDF]
    JOIN enrolled_students ON disc_topics.user_id = enrolled_students.user_id  -- [JDF]
    JOIN user_dim ON disc_topics.user_id = user_dim.id  -- [JDF]


UNION ALL  -------------------------------------------------------------------------------------------------------------------------- [JDF]


SELECT
    user_dim.canvas_id     AS canvas_user_id,
    user_dim.name,
    user_dim.sortable_name,
    address                AS email,
    'course_grade' :: TEXT AS item_type,
    1 :: INTEGER           AS item_number,
    computed_current_score AS user_score,
    section_min            AS item_min,
    section_average        AS item_average,
    section_max            AS item_max,
    section_stddev         AS item_stddev,
    NULL :: TIMESTAMP      AS created_at,
    NULL :: TIMESTAMP      AS unlock_at,  -- [JDF]
    NULL :: TIMESTAMP      AS completed_at,
    NULL :: TIMESTAMP      AS due_at,
    NULL :: TIMESTAMP      AS lock_at,  -- [JDF]
    NULL :: NUMERIC        AS quiz_elapsed,  -- [JDF]
    NULL :: BIGINT         AS discussion_entry_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_entry_created,  -- [JDF]
    NULL :: BIGINT         AS discussion_topic_id, -- [JDF]
    NULL :: TIMESTAMP      AS disc_topic_created  -- [JDF]
FROM
    enrolled_students
    JOIN user_dim ON enrolled_students.user_id = user_dim.id
    JOIN enrollment_fact ON enrolled_students.id = enrollment_fact.enrollment_id
    CROSS JOIN section_averages
ORDER BY canvas_user_id, item_type, item_number


-- End Steve Huwig's Original Main Query (joiner)  ----------------------------------------------------------------------------------- [JDF]

);
-- End raw_tidy ---------------------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_1 subquery --------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_1 AS (

WITH

-- iso_week subquery ----------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <2
GROUP BY canvas_user_id

);

-- end JDF_tidy_1 subquery ----------------------------------------------------------------------------------------------------------- [JDF]


-- JDF_tidy_2 subquery --------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_2 AS (

WITH

-- iso_week subquery ----------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <3
GROUP BY canvas_user_id

);

-- end JDF_tidy_2 subquery ----------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_3 subquery --------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_3 AS (

WITH

-- iso_week subquery ----------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <4
GROUP BY canvas_user_id

);

-- end JDF_tidy_3 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_4 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_4 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <5
GROUP BY canvas_user_id

);

-- end JDF_tidy_4 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_5 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_5 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <6
GROUP BY canvas_user_id

);

-- end JDF_tidy_5 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_6 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_6 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <7
GROUP BY canvas_user_id

);

-- end JDF_tidy_6 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_7 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_7 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <8
GROUP BY canvas_user_id

);

-- end JDF_tidy_7 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_8 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_8 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <9
GROUP BY canvas_user_id

);

-- end JDF_tidy_8 subquery ----------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_9 subquery --------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_9 AS (

WITH

-- iso_week subquery ----------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <10
GROUP BY canvas_user_id

);

-- end JDF_tidy_9 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_10 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_10 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <11
GROUP BY canvas_user_id

);

-- end JDF_tidy_10 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_11 subquery ------------------------------------------------------------------------------------------------------------- [JDF]
CREATE TEMP TABLE JDF_tidy_11 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <12
GROUP BY canvas_user_id

);

-- end JDF_tidy_11 subquery ---------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_12 subquery -------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_12 AS (

WITH

-- iso_week subquery ----------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <13
GROUP BY canvas_user_id

);

-- end JDF_tidy_12 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_13 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_13 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course), -- [JDF, to run, substitute :course or other legitimate course canvas_id for :course in 7 places (Lines 52, 70, 111, 132, 190, 225 & 508)]

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <14
GROUP BY canvas_user_id

);

-- end JDF_tidy_13 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_14 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_14 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course), -- [JDF, to run, substitute :course or other legitimate course canvas_id for :course in 7 places (Lines 52, 70, 111, 132, 190, 225 & 508)]

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------- [JDF]

-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <15
GROUP BY canvas_user_id

);

-- end JDF_tidy_14 subquery ------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_15 subquery ----------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_15 AS (

WITH

-- iso_week subquery -------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <16
GROUP BY canvas_user_id

);

-- end JDF_tidy_15 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_16 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_16 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <17
GROUP BY canvas_user_id

);

-- end JDF_tidy_16 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_17 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_17 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course),

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,

    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <18
GROUP BY canvas_user_id

);

-- end JDF_tidy_17 subquery --------------------------------------------------------------------------------------------------------- [JDF]



-- JDF_tidy_18 subquery ------------------------------------------------------------------------------------------------------------- [JDF]

CREATE TEMP TABLE JDF_tidy_18 AS (

WITH

-- iso_week subquery ---------------------------------------------------------------------------------------------------------------- [JDF]
iso_week AS (

  SELECT *,
        (
        EXTRACT (WEEK FROM(
        SELECT LEAST(completed_at, disc_entry_created, disc_topic_created)))

        -

        EXTRACT (WEEK FROM(
        SELECT LEAST(

                    (SELECT MIN(due_at)
                    FROM raw_tidy
                    WHERE item_type = 'assignment'),

                    (SELECT course_dim.start_at
                    FROM course_dim
                    WHERE course_dim.canvas_id = :course), -- [JDF, to run, substitute :course or other legitimate course canvas_id for :course in 7 places (Lines 52, 70, 111, 132, 190, 225 & 508)]

                    (SELECT enrollment_term_dim.date_start
                    FROM enrollment_term_dim
                    WHERE enrollment_term_dim.id = (SELECT course_dim.enrollment_term_id -- [JDF]
                                                    FROM course_dim  -- [JDF]
                                                    WHERE course_dim.canvas_id = :course))
                  )))
        ) +1  AS engagement_week

   FROM raw_tidy

             )
-- end iso_week subquery ------------------------------------------------------------------------------------------------------------ [JDF]


-- Week selectable metrics  --------------------------------------------------------------------------------------------------------- [JDF]

SELECT canvas_user_id AS user_id,

    (SELECT id
          FROM course_dim
          WHERE canvas_id = :course)  AS course_id,
    CASE WHEN

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window

    >1 THEN 1 ELSE

          SUM((CASE WHEN created_at IS NULL AND item_type = 'assignment' THEN
          (greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600 ELSE
          (greatest((date_part(epoch,completed_at)), (least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))) -
          least((date_part(epoch,completed_at)),(least(date_part(epoch,greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))))/3600 END)) /
          SUM((greatest(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))) -
          least(date_part(epoch, greatest(due_at, lock_at)), date_part(epoch, greatest(created_at, unlock_at))))/3600) --AS tot_sub_window
          END :: decimal(10,4) * 100
          AS twa_sub_elapsed_perc,

    AVG(quiz_elapsed)/60 :: decimal(10,2) AS ave_quiz_elapsed,

    (COUNT(DISTINCT discussion_topic_id)) + (COUNT(DISTINCT discussion_entry_id)) AS disc_count

FROM iso_week
WHERE iso_week.engagement_week <19
GROUP BY canvas_user_id

);

-- end JDF_tidy_18 subquery --------------------------------------------------------------------------------------------------------- [JDF]


-- Begin scoring -------------------------------------------------------------------------------------------------------------------- [JDF]

WITH

-- Week 1 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week1 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_1

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_1.user_id, JDF_tidy_1.course_id, JDF_tidy_1.twa_sub_elapsed_perc, JDF_tidy_1.ave_quiz_elapsed, JDF_tidy_1.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_1

LEFT JOIN

(SELECT JDF_tidy_1.course_id,
AVG(JDF_tidy_1.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_1.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_1.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_1.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_1.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_1.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_1
GROUP BY JDF_tidy_1.course_id) AS JDF_cohort

ON JDF_tidy_1.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 1 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 2 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week2 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_2

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_2.user_id, JDF_tidy_2.course_id, JDF_tidy_2.twa_sub_elapsed_perc, JDF_tidy_2.ave_quiz_elapsed, JDF_tidy_2.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_2

LEFT JOIN

(SELECT JDF_tidy_2.course_id,
AVG(JDF_tidy_2.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_2.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_2.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_2.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_2.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_2.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_2
GROUP BY JDF_tidy_2.course_id) AS JDF_cohort

ON JDF_tidy_2.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 2 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 3 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week3 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_3

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_3.user_id, JDF_tidy_3.course_id, JDF_tidy_3.twa_sub_elapsed_perc, JDF_tidy_3.ave_quiz_elapsed, JDF_tidy_3.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_3

LEFT JOIN

(SELECT JDF_tidy_3.course_id,
AVG(JDF_tidy_3.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_3.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_3.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_3.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_3.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_3.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_3
GROUP BY JDF_tidy_3.course_id) AS JDF_cohort

ON JDF_tidy_3.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 3 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 4 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week4 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_4

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_4.user_id, JDF_tidy_4.course_id, JDF_tidy_4.twa_sub_elapsed_perc, JDF_tidy_4.ave_quiz_elapsed, JDF_tidy_4.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_4

LEFT JOIN

(SELECT JDF_tidy_4.course_id,
AVG(JDF_tidy_4.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_4.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_4.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_4.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_4.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_4.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_4
GROUP BY JDF_tidy_4.course_id) AS JDF_cohort

ON JDF_tidy_4.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 4 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 5 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]


Week5 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_5

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_5.user_id, JDF_tidy_5.course_id, JDF_tidy_5.twa_sub_elapsed_perc, JDF_tidy_5.ave_quiz_elapsed, JDF_tidy_5.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_5

LEFT JOIN

(SELECT JDF_tidy_5.course_id,
AVG(JDF_tidy_5.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_5.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_5.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_5.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_5.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_5.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_5
GROUP BY JDF_tidy_5.course_id) AS JDF_cohort

ON JDF_tidy_5.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 5 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 6 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week6 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_6

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_6.user_id, JDF_tidy_6.course_id, JDF_tidy_6.twa_sub_elapsed_perc, JDF_tidy_6.ave_quiz_elapsed, JDF_tidy_6.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_6

LEFT JOIN

(SELECT JDF_tidy_6.course_id,
AVG(JDF_tidy_6.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_6.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_6.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_6.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_6.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_6.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_6
GROUP BY JDF_tidy_6.course_id) AS JDF_cohort

ON JDF_tidy_6.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 6 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 7 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week7 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_7

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_7.user_id, JDF_tidy_7.course_id, JDF_tidy_7.twa_sub_elapsed_perc, JDF_tidy_7.ave_quiz_elapsed, JDF_tidy_7.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_7

LEFT JOIN

(SELECT JDF_tidy_7.course_id,
AVG(JDF_tidy_7.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_7.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_7.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_7.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_7.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_7.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_7
GROUP BY JDF_tidy_7.course_id) AS JDF_cohort

ON JDF_tidy_7.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 7 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 8 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week8 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_8

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_8.user_id, JDF_tidy_8.course_id, JDF_tidy_8.twa_sub_elapsed_perc, JDF_tidy_8.ave_quiz_elapsed, JDF_tidy_8.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_8

LEFT JOIN

(SELECT JDF_tidy_8.course_id,
AVG(JDF_tidy_8.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_8.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_8.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_8.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_8.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_8.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_8
GROUP BY JDF_tidy_8.course_id) AS JDF_cohort

ON JDF_tidy_8.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 8 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 9 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week9 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_9

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_9.user_id, JDF_tidy_9.course_id, JDF_tidy_9.twa_sub_elapsed_perc, JDF_tidy_9.ave_quiz_elapsed, JDF_tidy_9.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_9

LEFT JOIN

(SELECT JDF_tidy_9.course_id,
AVG(JDF_tidy_9.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_9.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_9.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_9.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_9.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_9.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_9
GROUP BY JDF_tidy_9.course_id) AS JDF_cohort

ON JDF_tidy_9.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 9 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 10 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week10 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_10

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_10.user_id, JDF_tidy_10.course_id, JDF_tidy_10.twa_sub_elapsed_perc, JDF_tidy_10.ave_quiz_elapsed, JDF_tidy_10.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_10

LEFT JOIN

(SELECT JDF_tidy_10.course_id,
AVG(JDF_tidy_10.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_10.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_10.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_10.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_10.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_10.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_10
GROUP BY JDF_tidy_10.course_id) AS JDF_cohort

ON JDF_tidy_10.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 10 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 11 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]


Week11 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_11

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_11.user_id, JDF_tidy_11.course_id, JDF_tidy_11.twa_sub_elapsed_perc, JDF_tidy_11.ave_quiz_elapsed, JDF_tidy_11.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_11

LEFT JOIN

(SELECT JDF_tidy_11.course_id,
AVG(JDF_tidy_11.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_11.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_11.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_11.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_11.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_11.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_11
GROUP BY JDF_tidy_11.course_id) AS JDF_cohort

ON JDF_tidy_11.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 11 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 12 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]


Week12 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_12

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_12.user_id, JDF_tidy_12.course_id, JDF_tidy_12.twa_sub_elapsed_perc, JDF_tidy_12.ave_quiz_elapsed, JDF_tidy_12.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_12

LEFT JOIN

(SELECT JDF_tidy_12.course_id,
AVG(JDF_tidy_12.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_12.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_12.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_12.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_12.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_12.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_12
GROUP BY JDF_tidy_12.course_id) AS JDF_cohort

ON JDF_tidy_12.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 12 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 13 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week13 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_13

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_13.user_id, JDF_tidy_13.course_id, JDF_tidy_13.twa_sub_elapsed_perc, JDF_tidy_13.ave_quiz_elapsed, JDF_tidy_13.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_13

LEFT JOIN

(SELECT JDF_tidy_13.course_id,
AVG(JDF_tidy_13.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_13.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_13.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_13.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_13.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_13.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_13
GROUP BY JDF_tidy_13.course_id) AS JDF_cohort

ON JDF_tidy_13.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 13 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 14 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week14 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_14

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_14.user_id, JDF_tidy_14.course_id, JDF_tidy_14.twa_sub_elapsed_perc, JDF_tidy_14.ave_quiz_elapsed, JDF_tidy_14.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_14

LEFT JOIN

(SELECT JDF_tidy_14.course_id,
AVG(JDF_tidy_14.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_14.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_14.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_14.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_14.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_14.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_14
GROUP BY JDF_tidy_14.course_id) AS JDF_cohort

ON JDF_tidy_14.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 14 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 15 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]


Week15 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_15

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_15.user_id, JDF_tidy_15.course_id, JDF_tidy_15.twa_sub_elapsed_perc, JDF_tidy_15.ave_quiz_elapsed, JDF_tidy_15.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_15

LEFT JOIN

(SELECT JDF_tidy_15.course_id,
AVG(JDF_tidy_15.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_15.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_15.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_15.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_15.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_15.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_15
GROUP BY JDF_tidy_15.course_id) AS JDF_cohort

ON JDF_tidy_15.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 15 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 16 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week16 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_16

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_16.user_id, JDF_tidy_16.course_id, JDF_tidy_16.twa_sub_elapsed_perc, JDF_tidy_16.ave_quiz_elapsed, JDF_tidy_16.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_16

LEFT JOIN

(SELECT JDF_tidy_16.course_id,
AVG(JDF_tidy_16.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_16.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_16.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_16.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_16.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_16.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_16
GROUP BY JDF_tidy_16.course_id) AS JDF_cohort

ON JDF_tidy_16.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 16 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 17 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week17 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_17

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_17.user_id, JDF_tidy_17.course_id, JDF_tidy_17.twa_sub_elapsed_perc, JDF_tidy_17.ave_quiz_elapsed, JDF_tidy_17.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_17

LEFT JOIN

(SELECT JDF_tidy_17.course_id,
AVG(JDF_tidy_17.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_17.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_17.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_17.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_17.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_17.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_17
GROUP BY JDF_tidy_17.course_id) AS JDF_cohort

ON JDF_tidy_17.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

),
-- end Week 17 Scoring  -------------------------------------------------------------------------------------------------------------- [JDF]



-- Week 18 Scoring  ------------------------------------------------------------------------------------------------------------------ [JDF]

Week18 AS (
SELECT JDF_z.user_id,

              (64.6630223 +
              -0.0936810 * (
              CASE WHEN JDF_z.z_twa_sub_elasped_perc > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_twa_sub_elasped_perc < - 2.5 OR JDF_z.z_twa_sub_elasped_perc IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_twa_sub_elasped_perc * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.1286100 * (

              CASE WHEN JDF_z.z_ave_quiz_elapsed > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_ave_quiz_elapsed < - 2.5 OR JDF_z.z_ave_quiz_elapsed IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_ave_quiz_elapsed * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              +
              0.2471180 * (
              CASE WHEN JDF_z.z_disc_count > 2.5 THEN 100.00
              ELSE (CASE WHEN JDF_z.z_disc_count < - 2.5 OR JDF_z.z_disc_count IS NULL THEN 0.00
              ELSE ((.50093654 + JDF_z.z_disc_count * 0.19960390) :: decimal(10,4) * 100) END)END
              )
              )/102.2358223 :: decimal (10,4) * 100 AS Engagement_Score_18

FROM

--Z
(
SELECT JDF_boiler.user_id, JDF_boiler.course_id,

(CASE WHEN JDF_boiler.cohort_sd_sub_elapsed_perc = 0 THEN 0
ELSE (JDF_boiler.twa_sub_elapsed_perc - JDF_boiler.cohort_twa_sub_elapsed_perc) / JDF_boiler.cohort_sd_sub_elapsed_perc
END) AS z_twa_sub_elasped_perc,

(CASE WHEN JDF_boiler.cohort_sd_ave_quiz_elapsed = 0 THEN 0
ELSE (JDF_boiler.ave_quiz_elapsed - JDF_boiler.cohort_ave_quiz_elapsed) / JDF_boiler.cohort_sd_ave_quiz_elapsed
END) AS z_ave_quiz_elapsed,

(CASE WHEN JDF_boiler.cohort_sd_disc_count = 0 THEN 0
ELSE (JDF_boiler.disc_count - JDF_boiler.cohort_ave_disc_count) / JDF_boiler.cohort_sd_disc_count
END) AS z_disc_count

FROM

-- Boiler
(SELECT JDF_tidy_18.user_id, JDF_tidy_18.course_id, JDF_tidy_18.twa_sub_elapsed_perc, JDF_tidy_18.ave_quiz_elapsed, JDF_tidy_18.disc_count,
JDF_cohort.cohort_twa_sub_elapsed_perc, JDF_cohort.cohort_sd_sub_elapsed_perc, JDF_cohort.cohort_ave_quiz_elapsed, JDF_cohort.cohort_sd_ave_quiz_elapsed, JDF_cohort.cohort_ave_disc_count, JDF_cohort.cohort_sd_disc_count

FROM JDF_tidy_18

LEFT JOIN

(SELECT JDF_tidy_18.course_id,
AVG(JDF_tidy_18.twa_sub_elapsed_perc) :: decimal(10,2) AS cohort_twa_sub_elapsed_perc,
STDDEV(JDF_tidy_18.twa_sub_elapsed_perc) :: decimal(10,2)  AS cohort_sd_sub_elapsed_perc,
AVG(JDF_tidy_18.ave_quiz_elapsed) :: decimal(10,2)  AS cohort_ave_quiz_elapsed,
STDDEV(JDF_tidy_18.ave_quiz_elapsed) :: decimal(10,2)   AS cohort_sd_ave_quiz_elapsed,
AVG(JDF_tidy_18.disc_count) :: decimal(10,2)  AS cohort_ave_disc_count,
STDDEV(JDF_tidy_18.disc_count) :: decimal(10,2)  AS cohort_sd_disc_count
FROM JDF_tidy_18
GROUP BY JDF_tidy_18.course_id) AS JDF_cohort

ON JDF_tidy_18.course_id = JDF_cohort.course_id ) AS JDF_boiler
-- End Boiler


WHERE JDF_boiler.user_id IS NOT NULL
AND JDF_boiler.course_id IS NOT NULL
) AS JDF_z
--End Z

)
-- Bring it all together ------------------------------------------------------------------------------------------------------------ [JDF]

SELECT Week18.user_id,
Week1.Engagement_Score_1,
Week2.Engagement_Score_2,
Week3.Engagement_Score_3,
Week4.Engagement_Score_4,
Week5.Engagement_Score_5,
Week6.Engagement_Score_6,
Week7.Engagement_Score_7,
Week8.Engagement_Score_8,
Week9.Engagement_Score_9,
Week10.Engagement_Score_10,
Week11.Engagement_Score_11,
Week12.Engagement_Score_12,
Week13.Engagement_Score_13,
Week14.Engagement_Score_14,
Week15.Engagement_Score_15,
Week16.Engagement_Score_16,
Week17.Engagement_Score_17,
Week18.Engagement_Score_18
FROM Week18
LEFT OUTER JOIN
Week17
ON Week18.user_id  = Week17.user_id
LEFT OUTER JOIN
Week16
ON COALESCE(Week18.user_id, Week17.user_id) = Week16.user_id
LEFT OUTER JOIN
Week15
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id ) = Week15.user_id
LEFT OUTER JOIN
Week14
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id ) = Week14.user_id
LEFT OUTER JOIN
Week13
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id ) = Week13.user_id
LEFT OUTER JOIN
Week12
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id ) = Week12.user_id
LEFT OUTER JOIN
Week11
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id ) = Week11.user_id
LEFT OUTER JOIN
Week10
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id ) = Week10.user_id
LEFT OUTER JOIN
Week9
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id ) = Week9.user_id
LEFT OUTER JOIN
Week8
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id ) = Week8.user_id
LEFT OUTER JOIN
Week7
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id ) = Week7.user_id
LEFT OUTER JOIN
Week6
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id ) = Week6.user_id
LEFT OUTER JOIN
Week5
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id, Week6.user_id ) = Week5.user_id
LEFT OUTER JOIN
Week4
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id, Week6.user_id, Week5.user_id ) = Week4.user_id
LEFT OUTER JOIN
Week3
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id, Week6.user_id, Week5.user_id, Week4.user_id) = Week3.user_id
LEFT OUTER JOIN
Week2
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id, Week6.user_id, Week5.user_id, Week4.user_id, Week3.user_id) = Week2.user_id
LEFT OUTER JOIN
Week1
ON COALESCE(Week18.user_id, Week17.user_id, Week16.user_id, Week15.user_id, Week14.user_id, Week13.user_id, Week12.user_id, Week11.user_id, Week10.user_id, Week9.user_id, Week8.user_id, Week7.user_id, Week6.user_id, Week5.user_id, Week4.user_id, Week3.user_id, Week2.user_id) = Week1.user_id