[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
Time spent per user
The Data Connector 1.0 contains detailed information on where your users are spending their time learning.
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.
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.
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
Last updated