This page contains a number of example queries that can help you get started analyzing the data available via the Data Connector 1.0 with SQL via AWS Athena.
All example queries reference a database when referring to tables. For example when querying the dim_user table, the query references the table as data-connector-1234.dim_user.
This database reference is unique per customer so make sure to replace this with the name of your database. To find the name of your database, you can use the bucket name and remove the -production reference at the end.
For example:
Bucket name: data-connector-1234-production
Database name: data-connector-1234
Get All Active Users
This query returns a list of all users that have gained XP in the last 7 days.
SELECT user_id, u.email, MAX(xp.created_date) AS xp_date
FROM data_connector_1234.xp_fact AS xp
LEFT JOIN data_connector_1234.user_dim AS u USING(user_id)
WHERE xp.created_date >= date_add('day', -7, CURRENT_DATE)
GROUP BY user_id, u.email
ORDER BY xp_date DESC
All users
This query returns a list of all users who are currently in your group.
SELECT user_id, registered_at, email, last_visit_at
FROM data_connector_1234.user_dim AS u
WHERE deleted_at IS NULL
ORDER BY registered_at
Removed users
Please notice the deleted_at filter, if you leave this out the query will also return the users that have been in your group but have since been removed.
The data of these removed users cuts off at the moment they left your group. If they continued learning afterwards on their own terms or subscription, this data will not be available via the Data Connector 1.0.
Time spent per user
The Data Connector 1.0 contains detailed information on where your users are spending their time learning.
Time-spent data is limited to the following content types: Courses, Practices and Projects.
Assessments, Workspace, Certification and other products are not included in these numbers!
WITH time_spent_per_user AS (
SELECT SUM(time_spent) AS total_time_spent, user_id, 'exercises' AS time_spent_type
FROM data_connector_1234.exercise_fact
GROUP BY user_id
UNION
SELECT SUM(time_spent) AS total_time_spent, user_id, 'practice' AS time_spent_type
FROM data_connector_1234.practice_fact
GROUP BY user_id
UNION
SELECT SUM(time_spent) AS total_time_spent, user_id, 'project' AS time_spent_type
FROM data_connector_1234.project_fact
GROUP BY user_id
)
SELECT user_id, email, SUM(total_time_spent) AS time_spent_seconds
FROM time_spent_per_user
LEFT JOIN data_connector_1234.user_dim USING(user_id)
GROUP BY user_id, email
ORDER BY user_id
Time spent per team
Similar to time per user, we can also aggregate time spent totals per team.
On team membership and scope
Keep in mind this only show the XP gained by the users currently in the respective team, this means once someone leaves a team, the team's XP will go down.
A single user can be member of multiple teams which means their XP will be essentially double-dipped into several team buckets. Keep this in mind as adding up these team-XP values won't add up to the total XP gained across all users.
If you want time_spent to remain allocated to the team, even if a user has left the team, you can use the user_team_bridge table which has joined_team_date & left_team_date columns you can use to calculate the period of time the user was part of a team.
WITH team_data AS (
SELECT user_id, team_id, name
FROM data_connector_1234.team_dim
INNER JOIN data_connector_1234.user_team_bridge USING(team_id)
WHERE deleted_date IS NULL AND left_team_date IS NULL
ORDER BY user_id
),
time_spent_data AS (
SELECT SUM(time_spent) AS total_time_spent, user_id
FROM data_connector_1234.exercise_fact
GROUP BY user_id
UNION
SELECT SUM(time_spent) AS total_time_spent, user_id
FROM data_connector_1234.practice_fact
GROUP BY user_id
UNION
SELECT SUM(time_spent) AS total_time_spent, user_id
FROM data_connector_1234.project_fact
GROUP BY user_id
)
SELECT team_id, name, SUM(total_time_spent) AS time_spent_seconds
FROM time_spent_data
INNER JOIN team_data USING(user_id)
GROUP BY team_id, name
ORDER BY name
Time spent per technology
Another way of looking at time spent is by technology or topic. Each content type at DataCamp has an associated technology or topic, using these dimension tables combined with the fact tables we can get information on which technologies learners are spending most of their time.
In order to get these numbers per topic instead of technology, you can swap out the technology column from the fact tables with the topic column.
with time_spent_data AS (
SELECT SUM(time_spent) AS total_time_spent, technology
FROM data_connector_1234.exercise_fact
LEFT JOIN data_connector_1234.exercise_dim USING(exercise_id)
WHERE technology IS NOT NULL
GROUP BY technology
UNION
SELECT SUM(time_spent) AS total_time_spent, technology
FROM data_connector_1234.practice_fact
LEFT JOIN data_connector_1234.practice_dim USING(practice_id)
WHERE technology IS NOT NULL
GROUP BY technology
UNION
SELECT SUM(time_spent) AS total_time_spent, technology
FROM data_connector_1234.project_fact
LEFT JOIN data_connector_1234.project_dim USING(project_id)
WHERE technology IS NOT NULL
GROUP BY technology
)
SELECT SUM(total_time_spent) AS total_time_spent_seconds, technology
FROM time_spent_data
GROUP BY technology
ORDER BY technology
Completed assessments
This query gives you all complete assessments along with their user, score and percentile.
SELECT user_id, email, assessment_id, title, score, score_group, percentile
FROM data_connector_1234.assessment_fact
LEFT JOIN data_connector_1234.assessment_dim USING (assessment_id)
LEFT JOIN data_connector_1234.user_dim AS u USING(user_id)
WHERE completed_at IS NOT NULL AND title IS NOT NULL
ORDER BY completed_at ASC
Completed courses by user
This query returns all courses that have been completed by users in your group. For this query it is important to understand that the course_fact table contains multiple entries per user/course, this table essentially contains sessions the user learned in the respective course. For each of the sessions there is a time_spent and XP value associated, indicating how long the user learned and how much XP they gained doing so. Once the course is completed every record for that user/course will have it's completed_at date set. Using this knowledge we can now query all completed courses by filtering on distinct course_id and completed_at values.
SELECT DISTINCT(course_id), title, user_id, email, completed_at
FROM data_connector_1234.course_fact AS f
LEFT JOIN data_connector_1234.user_dim USING(user_id)
LEFT JOIN data_connector_1234.course_dim AS c USING(course_id)
WHERE completed_at IS NOT NULL
ORDER BY email ASC, completed_at DESC
XP earned by user
This simple query sums up the total XP for users and decorates it with user data by joining the user_dim table.
SELECT SUM(xp) AS total_xp, user_id, email
FROM data_connector_1234.xp_fact
LEFT JOIN data_connector_1234.user_dim USING(user_id)
GROUP BY user_id, email
ORDER BY total_xp DESC
