Members who have earned XP

To get the number of members who have earned XP, you can use the following query:
SELECT
count(DISTINCT user_id) AS total_members
FROM data_connector_1234.fact_learn_events
WHERE event_name IN ('assessment_engaged',
'course_engagement',
'practice_engagement',
'project_engagement',
'b2b_onboarding_xp_boost',
'alpa_onboarding')
AND xp_earned > 0
The number below with the arrow is the number of new members in the last 30 days. You can calculate it by getting the number of members up until last month and subtracting it from the all-time total calculated above.
SELECT
count(DISTINCT user_id) AS total_members
FROM data_connector_1234.fact_learn_events
WHERE event_name IN ('assessment_engaged',
'course_engagement',
'practice_engagement',
'project_engagement',
'b2b_onboarding_xp_boost',
'alpa_onboarding')
AND xp_earned > 0
-- members last month
AND date(occurred_at) <= date_add('day', -31, current_date)
In the queries above, we specify the XP events to include. This is to avoid double counting XP in courses since the dataset contains course_engagement
and exercise_completed
events with a non-zero xp_earned
value. Both contain XP gained from completing an exercise, so including both would lead to double counting XP earned in courses.
Please check the Domain Gotchas section for more details.
Last updated