# \[Data Connector 1.0] Example queries

{% hint style="warning" %}
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`
  {% endhint %}

## Get All Active Users

This query returns a list of all users that have gained XP in the last 7 days.

```sql
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. 

```sql
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
```

{% hint style="info" %}
**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.
{% endhint %}

## Time spent per user

The Data Connector 1.0 contains detailed information on where your users are spending their time learning. 

{% hint style="info" %}
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!
{% endhint %}

```sql
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. 

{% hint style="info" %}
**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.*
{% endhint %}

```sql
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.

{% hint style="info" %}
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.
{% endhint %}

```sql
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.

```sql
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.

```sql
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.

```sql
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
```