Progress report

The Progress report shows a table with all active members in the organization. For each member who has earned XP, it will show the total number of completions on courses, course chapters, projects, practices, tracks, and XP earned.

The filters section allows you to modify the report. The query below replicates the Progress report using the default filters:

• All time

• Since the user was part of the group

• All license types

• All content types

WITH last_xp_earned AS (
        SELECT
          user_id,
          max(occurred_at) AS last_xp_earned_at 
        FROM data_connector_1234.fact_learn_events
        WHERE xp_earned > 0
        GROUP BY user_id
    ),

   -- The Progress report includes only current members
    current_users AS (
        SELECT user_id
        FROM data_connector_1234.bridge_user_group
        WHERE ended_at IS NULL
    ),

    /* We use a separate CTE for XP events to specify 
    which events to include. */
    xp_events AS (
        SELECT 
            events.user_id, 
            events.event_name, 
            events.occurred_at, 
            events.xp_earned
        FROM data_connector_1234.fact_learn_events AS events 
        -- only current members
        INNER JOIN current_users 
          ON events.user_id = current_users.user_id
        WHERE events.event_name IN ('assessment_engaged', 
                                'course_engagement', 
                                'practice_engagement', 
                                'project_engagement', 
                                'b2b_onboarding_xp_boost', 
                                'alpa_onboarding')
    ),

    completion_events AS (
        SELECT 
            events.user_id, 
            events.event_name, 
            events.occurred_at
        FROM data_connector_1234.fact_learn_events AS events 
        -- only current members
        INNER JOIN current_users 
            ON events.user_id = current_users.user_id
        WHERE events.event_name IN ('assessment_completed', 
                                'course_completed', 
                                'practice_completed', 
                                'project_completed', 
                                'chapter_completed', 
                                'track_completed')
    ),

    completions AS (
        SELECT 
            user_id,
            count_if(event_name = 'course_completed') AS nb_courses_compled,
            count_if(event_name = 'chapter_completed') AS nb_chapters_completed,
            count_if(event_name = 'assessment_completed') AS nb_assessments_completed,
            count_if(event_name = 'project_completed') AS nb_projects_completed,
            count_if(event_name = 'practice_completed') AS nb_practices_completed,
            count_if(event_name = 'track_completed') AS nb_tracks_completed
        FROM completion_events
        GROUP BY user_id

    ),
    xp_earned AS (
        SELECT
            user_id,
            coalesce(sum(xp_earned), 0) as total_xp_earned
        FROM xp_events
        GROUP BY user_id
    )

SELECT 
    user.email,
    coalesce(completions.nb_courses_compled, 0) AS nb_courses_compled,
    coalesce(completions.nb_chapters_completed, 0) AS nb_chapters_completed,
    coalesce(completions.nb_assessments_completed, 0) AS nb_assessments_completed,
    coalesce(completions.nb_projects_completed, 0) AS nb_projects_completed,
    coalesce(completions.nb_practices_completed, 0) AS nb_practices_completed,
    coalesce(completions.nb_tracks_completed, 0) AS nb_tracks_completed,
    lxe.last_xp_earned_at,
    coalesce(xpe.total_xp_earned, 0) AS total_xp_earned
FROM completions 
LEFT JOIN last_xp_earned AS lxe 
    ON completions.user_id = lxe.user_id 
LEFT JOIN xp_earned AS xpe 
    ON completions.user_id = xpe.user_id
LEFT JOIN data_connector_1234.dim_user AS user
    ON completions.user_id = user.user_id
ORDER BY user.email