Sample queries

In this section, we provide several sample SQL queries to answer typical reporting needs like:

• Users who have gained XP in the last 7 days

• All current members of the group

• Time spent in Learn per technology

• Time spent in Learn per team

• Time spent in Learn per user

• XP earned by user

• Completed courses by user

• Completed assessments by user

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

Removed users

Please notice the bug.ended_at filter. If you leave this out, the query will also return the users who have been in your group but have since been removed. You can then include bug.ended_at in the SELECT clause to get the date a user left the group.

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

On team membership and scope

• Keep in mind that the query above only shows XP gained by the users currently in the respective team; this means that once someone leaves a team, the team's XP calculation would decrease.

• A single user can be a member of multiple teams, which means their XP is included in each team's total. Adding up these team-XP values will not match the total XP gained across all users.

If you would like time_spent to remain allocated to the team even if a user has left the team, you can use the bridge_user_team table which has started_at and ended_at columns to calculate the period the user was part of a team.

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('[e-mail]')
GROUP BY user.email
ORDER BY email

DataLab, Certification, and other products are not included in the query above.

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('[e-mail]')
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('[e-mail]')
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.