[Data Connector 1.0] Example queries

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