Engagement Scores Queries

Student Engagement Scores

-- This query outputs the Student Engagement Scores

SELECT DISTINCT
    dp.raw_person_id
    , dp.institution_person_id
    , ds.raw_section_id
    , fses.event_ts
    , fses.iteration_ts AS data_refresh_timestamp
    , fses.engagement_score
FROM data_mart.fact_student_engagement_scores fses
INNER JOIN data_mart.dim_person dp USING (dim_person_id)
INNER JOIN data_mart.dim_section ds USING (dim_section_id)
ORDER BY 1, 4, 5;

Student Top Features

-- This query outputs the Student Top Features Historically (big result set, heavy query)

WITH dim_person AS (
    SELECT
        dp.dim_person_id
        , dp.raw_person_id
        , dp.institution_person_id
    FROM data_mart.dim_person dp
    INNER JOIN data_mart.dim_enrollment de
        ON dp.raw_person_id = de.raw_person_id
    INNER JOIN data_mart.dim_section ds
        ON de.raw_section_id = ds.raw_section_id
    INNER JOIN data_mart.dim_career_term dct
        ON dct.raw_term_id = ds.raw_term_id
    WHERE
        dct.is_active = 1
        AND de.raw_career_id <> 'Not Available'
        AND de.civ_is_enrolled = 1
    GROUP BY
        dp.dim_person_id
        , dp.raw_person_id
        , dp.institution_person_id
),
active_students AS (
    SELECT
        dim_person_id
        , raw_person_id
        , institution_person_id
        , dim_section_id
        , dim_student_engagement_feature_id
        , fsetf.feature_rank
        , fsetf.feature_contrib
        , fsetf.event_ts
    FROM data_mart.fact_student_engagement_top_features fsetf
    INNER JOIN dim_person USING (dim_person_id)
)
SELECT
    fsetf.raw_person_id
    , fsetf.institution_person_id
    , ds.raw_section_id
    , dsef.civ_student_engagement_feature_name
    , dsef.civ_student_engagement_feature_category
    , dsef.civ_student_engagement_feature_description
    , fsetf.feature_rank
    , fsetf.feature_contrib
    , fsetf.event_ts
FROM active_students fsetf
INNER JOIN data_mart.dim_section ds USING (dim_section_id)
INNER JOIN data_mart.dim_student_engagement_feature dsef USING (dim_student_engagement_feature_id);

Student Top Features Latest

-- This query outputs the Student Top Features (latest top feature per student)

WITH dim_person AS (
    SELECT
        dp.dim_person_id
        , dp.raw_person_id
        , dp.institution_person_id
    FROM data_mart.dim_person dp
    INNER JOIN data_mart.dim_enrollment de
        ON dp.raw_person_id = de.raw_person_id
    INNER JOIN data_mart.dim_section ds
        ON de.raw_section_id = ds.raw_section_id
    INNER JOIN data_mart.dim_career_term dct
        ON dct.raw_term_id = ds.raw_term_id
    WHERE
        dct.is_active = 1
        AND de.raw_career_id <> 'Not Available'
        AND de.civ_is_enrolled = 1
    GROUP BY
        dp.dim_person_id
        , dp.raw_person_id
        , dp.institution_person_id
),
latest_date AS (
    SELECT
        dim_person_id
        , MAX(event_ts) AS max_event_ts
    FROM data_mart.fact_student_engagement_top_features fsetf
    GROUP BY
        dim_person_id
),
active_students AS (
    SELECT
        dim_person_id
        , raw_person_id
        , institution_person_id
        , dim_section_id
        , dim_student_engagement_feature_id
        , fsetf.feature_rank
        , fsetf.feature_contrib
        , fsetf.event_ts
    FROM data_mart.fact_student_engagement_top_features fsetf
    INNER JOIN dim_person USING (dim_person_id)
    INNER JOIN latest_date USING (dim_person_id)
    WHERE
        fsetf.event_ts = latest_date.max_event_ts
)
SELECT
    fsetf.raw_person_id
    , fsetf.institution_person_id
    , ds.raw_section_id
    , dsef.civ_student_engagement_feature_name
    , dsef.civ_student_engagement_feature_category
    , dsef.civ_student_engagement_feature_description
    , fsetf.feature_rank
    , fsetf.feature_contrib
    , fsetf.event_ts
FROM active_students fsetf
INNER JOIN data_mart.dim_section ds USING (dim_section_id)
INNER JOIN data_mart.dim_student_engagement_feature dsef USING (dim_student_engagement_feature_id);