Sample queries
In this section, we provide several sample SQL queries to answer typical reporting needs like:
Additionally, in the following section, we provide SQL queries that replicate key reports in the Groups tab.
Users who have gained XP in the last 7 days
To track which users actively engage with the platform, you can use XP as a proxy for activity and look at users who have recently earned XP.
The following query returns a list of all users that earned XP in the last 7 days.
SELECT
user.email,
max(events.occurred_at) AS last_xp_at
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_user AS user
ON events.user_id = user.user_id
WHERE events.event_name IN ('assessment_engaged',
'course_engagement',
'practice_engagement',
'project_engagement',
'b2b_onboarding_xp_boost',
'alpa_onboarding')
AND date(events.occurred_at) >= date_add('day', -7, current_date)
AND events.xp_earned > 0
GROUP BY user.email
ORDER BY last_xp_at DESC
In the query 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.
You can also use the query above to identify your top learners for an XP competition. Simply modify the date filter to include the competition period.
All current members of the group
Another common request is a list of the organization's users. The query below returns a list of all current members in your group and their start date.
SELECT
bug.user_id,
user.email,
bug.started_at
FROM data_connector_1234.bridge_user_group AS bug
LEFT JOIN data_connector_1234.dim_user AS user
ON bug.user_id = user.user_id
WHERE bug.ended_at IS NULL
ORDER BY email
Time spent in Learn per technology
Each content type at DataCamp has an associated technology (e.g., R, Python, SQL, Spark, etc.). If you want to examine what technologies your users are most focused on, you can create a report with the time spent per technology with the query below.
SELECT
content.technology,
if(sum(events.duration_engaged) = 0, 0,
sum(events.duration_engaged) / 3600) AS hours_spent
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_content AS content
ON events.content_id = content.content_id
WHERE content.technology IS NOT NULL
GROUP BY content.technology
ORDER BY content.technology
You can include a restriction in the WHERE
clause if you would like to limit the results to a particular period.
Time spent in Learn per team
If you want to see how different teams in your organization are taking advantage of our platform, we can modify the query above to look at the time spent per team in Learn content (assessments, courses, practices, and projects).
WITH team_data AS (
SELECT
user.user_id,
team.team_name
FROM data_connector_1234.dim_team AS team
INNER JOIN data_connector_1234.bridge_user_team AS user
ON team.team_id = user.team_id
WHERE team.deleted_at IS NULL -- Team hasn't been deleted
AND user.ended_at IS NULL -- User has not left team
)
SELECT
teams.team_name,
if(sum(events.duration_engaged) = 0, 0,
sum(events.duration_engaged) / 3600) AS hours_spent
FROM data_connector_1234.fact_learn_events AS events
INNER JOIN team_data AS teams
ON events.user_id = teams.user_id
GROUP BY teams.team_name
ORDER BY teams.team_name
You can include a restriction in the WHERE
clause of the bottom section if you want to limit the results to a particular period.
Time spent in Learn per user
You may be interested in reviewing the time each of your organization's users has spent learning on our platform. The following query returns each user's time spent in Learn content (assessments, courses, practices, and projects).
SELECT
user.email,
if(sum(events.duration_engaged) = 0, 0,
sum(events.duration_engaged) / 3600) AS hours_spent
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_user AS user
ON events.user_id = user.user_id
-- Exclude deleted users
WHERE user.email NOT LIKE('%@deleted.datacamp.com')
GROUP BY user.email
ORDER BY email
You can include a restriction in the WHERE
clause if you would like to limit the results to a particular period.
XP earned by user
A different way to look at engagement is to measure XP. You can track how active each of your organization's users has been on our platform with the query below that displays the total XP per user.
SELECT
user.email,
sum(events.xp_earned) AS total_xp
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_user AS user
ON events.user_id = user.user_id
WHERE events.event_name IN ('assessment_engaged',
'course_engagement',
'practice_engagement',
'project_engagement',
'b2b_onboarding_xp_boost',
'alpa_onboarding')
AND events.xp_earned > 0
-- Exclude deleted users
AND user.email NOT LIKE('%@deleted.datacamp.com')
GROUP BY user.email
ORDER BY total_xp DESC
In the query 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.
You can include a restriction in the WHERE
clause if you would like to limit the results to a particular period.
Completed courses by user
It is often necessary to review learning activity on a more granular level. A common question is, "What courses have our users completed?" The query below returns all courses completed by users in your organization.
SELECT
events.user_id,
user.email,
content.course_id,
content.content_title,
events.occurred_at AS completed_at
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_content AS content
ON events.content_id = content.content_id
LEFT JOIN data_connector_1234.dim_user AS user
ON events.user_id = user.user_id
WHERE events.event_name = 'course_completed'
-- Exclude deleted users
AND user.email NOT LIKE('%@deleted.datacamp.com')
ORDER BY user.email, completed_at DESC
You can include a restriction in the WHERE
clause if you would like to limit the results to a particular period.
Completed assessments by user
Testing a user's skill level is an integral part of learning. If you want a report of all the assessments your organization's users have completed, the query below will tell you all complete assessments and their user, score, percentile, and completion date.
SELECT
user.email,
content.content_title AS assessment_title,
events.assessment_score,
events.assessment_percentile,
events.assessment_knowledge_level,
events.occurred_at AS completed_at
FROM data_connector_1234.fact_learn_events AS events
LEFT JOIN data_connector_1234.dim_content AS content
ON events.content_id = content.content_id
LEFT JOIN data_connector_1234.dim_user AS user
ON events.user_id = user.user_id
WHERE events.event_name = 'assessment_completed'
ORDER BY completed_at DESC
You can include a restriction in the WHERE
clause if you would like to limit the results to a particular period.
Last updated