Assessments

To replicate the Assessments Activity table, you can use the following query:

WITH assessment_completions AS (
    SELECT 
      content_id, 
      user_id, 
      assessment_score, 
      occurred_at
    FROM data_connector_1234.fact_learn_events
    WHERE event_name = 'assessment_completed'
),

assessment_completions_best_score AS (
    SELECT 
        user_id,
        content_id,
        max(assessment_score) AS assessment_score
    FROM assessment_completions
    GROUP BY user_id, content_id
),

assessment_completions_first_score AS (
    SELECT
        user_id,
        content_id,
        first_value(assessment_score) 
            OVER(PARTITION BY user_id, content_id 
                ORDER BY occurred_at ASC) AS assessment_first_score
    FROM assessment_completions
),

score_increases AS (
    SELECT
        best_score.content_id,
        coalesce(count(DISTINCT best_score.user_id), 0) AS nb_unique_improved_members
    FROM assessment_completions_best_score AS best_score
    INNER JOIN assessment_completions_first_score AS first_score 
        ON best_score.user_id = first_score.user_id
            AND best_score.content_id = first_score.content_id
    WHERE best_score.assessment_score > first_score.assessment_first_score
    GROUP BY best_score.content_id
),

median_scores AS (
    SELECT 
        content_id,
        approx_percentile(assessment_score, 0.6) AS median_score
    FROM assessment_completions
    GROUP BY content_id
),

metrics_without_medians AS (
    SELECT 
        completions.content_id,
        count(*) AS nb_assessments_completed,
        count(DISTINCT completions.user_id) AS nb_unique_member_completions,
        approx_percentile(completions.assessment_score, 0.5) AS median_score,
        approx_percentile(best_score.assessment_score, 0.5) AS median_best_score
    FROM assessment_completions AS completions
    INNER JOIN assessment_completions_best_score AS best_score 
        ON completions.user_id = best_score.user_id 
            AND completions.content_id = best_score.content_id
    GROUP BY completions.content_id
)

SELECT 
    coalesce(content.content_title, 'Deprecated Assessment') AS assessment,
    coalesce(mwm.nb_assessments_completed, 0) AS completions,
    coalesce(mwm.nb_unique_member_completions, 0) AS members,
    coalesce(sci.nb_unique_improved_members, 0) AS improved_members,
    coalesce(mwm.median_score, 0) AS median_score
FROM metrics_without_medians AS mwm 
LEFT JOIN score_increases AS sci 
    ON mwm.content_id = sci.content_id
LEFT JOIN median_scores AS ms
    ON ms.content_id = mwm.content_id
LEFT JOIN data_connector_1234.dim_content AS content
    ON content.content_id = mwm.content_id 
        AND content.assessment_active = true
ORDER BY completions DESC