Your First 2 SQL Queries

First time working with the Worklytics dataset? Try running these queries first.

1. Basic Metric Aggregation

How many strong collaborators did the typical team member have each week in August?

/* 
BASIC SAMPLE SQL QUERY 1
UPDATED: 2024.09-19

INPUT TABLE: 
{weekly_individual_aggregates}

NOTE: QUERY HAS BEEN WRITTEN FOR USE IN BIGQUERY - THEREFORE CERTAIN FUNCTIONS MAY NEED TO BE ADJUSTED FOR USE IN ANOTHER ENVIRONMENT

*/

WITH
activeEmployees AS (
SELECT week, employeeId
  FROM {weekly_individual_aggregates}
 WHERE key = 'worklytics:active:employees'
   AND value = 1.0
 GROUP BY week, employeeId
)
SELECT week, CEIL(AVG(value)) AS weekly_strong_collab_per_person
  FROM {weekly_individual_aggregates}
  JOIN activeEmployees -- filters on only employees that were active in a given week
 USING (week, employeeId)
 WHERE key = 'collaborators:strong_count_distinct' -- filters on desired metric
   AND week BETWEEN '2024-08-01' AND '2024-08-31' -- filters on desired date range
 GROUP BY week
;

Sample Query Output

week	weekly_strong_collab_per_person
2024-08-05	10
2024-08-12	9
2024-08-19	11
2024-08-26	11

Note that the above query can be quickly edited to show results for any of the metrics included in the dataset and any date range.

2. Basic Collaboration Analysis

How much time did the typical team member spend collaborating in slack in August? With how many collaborators?

/* 
BASIC SAMPLE SQL QUERY 2
UPDATED: 2024.09-19

INPUT TABLE:
{collaboration_graph_split}

NOTE: QUERY HAS BEEN WRITTEN FOR USE IN BIGQUERY - THEREFORE CERTAIN FUNCTIONS MAY NEED TO BE ADJUSTED FOR USE IN ANOTHER ENVIRONMENT

*/

SELECT	week
, SUM(weeklyCollaborationTimeMinutes) / COUNT(employeeIdSource) AS weekly_slack_collab_mins_per_person
, COUNT(employeeIdTarget) / COUNT(DISTINCT employeeIdSource) AS weekly_slack_collaborators_per_person

FROM {collaboration_graph_split}

WHERE 1=1
    AND sourceType = 'slack' -- filters on slack collaboration only 
    AND week BETWEEN '2024-08-01' AND '2024-08-31' -- filters on desired date range

GROUP BY 1 
ORDER BY 1;

Sample Query Output

week	weekly_slack_collab_mins_per_person	weekly_slack_collaborators_per_person
2024-08-05	45	15
2024-08-12	37	19
2024-08-19	44	15
2024-08-26	28	18

Note that the above query can be quickly edited to show results for any of the collaboration tools and any date range.