1 of 5

Getting Started

New to Worklytics? Here you will find resources intended to help you get up-to-speed as quickly as possible. The resources here assume an intermediate (or above) knowledge of relational databases and SQL specifically. Resources include:

Worklytics Datasets: an overview of available datasets and how we refer to them in this section of the documentation
Your First 2 SQL Queries: an absolute beginner's guide to Worklytics data
Sample SQL Queries: Aggregate-Level Data & Collab. Graph: once you've successfully run the first two queries, this section goes a bit deeper into analyses that are possible with the Individual Aggregates dataset & the collaboration graph
Sample SQL Queries: Event-Level Data: finally, delve into event-level data, which allows for more sophisticated analyses and the generation of custom metrics

Worklytics Datasets

Worklytics provides datasets describing work at various levels of aggregation. From less to more granular, the key Worklytics datasets you will be using are as follows:

In our Getting Started content, we use the following table names to refer to specific datasets, though the exact naming convention of tables may look different in your company's environment:

Table

Description

Usual join keys/fields

Note: Employee Events & Work Items datasets are only available in Datastream Enterprise tiers. Please contact sales@worklytics.co for more information

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

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

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

Sample SQL Queries: Aggregate-Level Data & Collab. Graph

The following is a sample set of SQL queries you can use to get started with the Worklytics dataset. This code block assumes the user has access to the individual aggregates, individual groups, and collaboration graph datasets, though the exact table names will have to be substituted for the ones in your company's environment.

/* 
SAMPLE SQL QUERIES FOR AGGREGATE-LEVEL DATA
DATE: 2024.09.19

INPUT TABLES: 
{weekly_individual_aggregates}
{weekly_individual_groups}
{collaboration_graph}
{collaboration_graph_split}


SECTION 1: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S MEETING CULTURE, FOCUS TIME, WORK-LIFE BALANCE AND COLLABORATION CULTURE USING THE INDIVIDUAL AGGREGATES TABLE
SECTION 2: IDENTIFY THE TYPICAL NUMBER OF COLLABORATORS (AND STRONG COLLABORATORS) AND TYPICAL NUMBER OF SLACK COLLABORATORS

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

*/

------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.1: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S MEETING CULTURE
--INCLUDES METRICS FOR MEDIAN NUMBER OF WEEKLY MEETING HOURS, % OF MEETING TIME FOCUSED AND DAILY FOCUS HOURS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       CAST(SUM(IF(key='calendar:events:hours:meetings',value,0)) AS NUMERIC) AS weekly_meeting_hours,
       1.0*CAST(SUM(IF(key='calendar:events:focus',value,0)) AS NUMERIC)/100.0 AS percent_meeting_focused,      
       CAST(SUM(IF(key='worklytics:hours:in:focus:blocks:v3:flow',value,0)) AS NUMERIC) AS daily_focus_hours             
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId 
AND p.week = e. week
WHERE 1=1
AND key IN ('calendar:events:hours:meetings','calendar:events:focus','worklytics:hours:in:focus:blocks:v3:flow')
GROUP BY 1,2 
),
average_totals AS (
SELECT employeeId, 
       CAST(AVG(weekly_meeting_hours) AS NUMERIC) AS average_weekly_meeting_hours,
       CAST(AVG(percent_meeting_focused) AS NUMERIC) AS average_percent_meeting_focused,
       CAST(AVG(daily_focus_hours) AS NUMERIC) AS average_daily_focus_hours
FROM weekly_totals
GROUP BY 1
)
SELECT ROUND(PERCENTILE_CONT(average_weekly_meeting_hours,0.5) OVER (),1) AS weekly_meeting_hours,
       ROUND(PERCENTILE_CONT(average_percent_meeting_focused,0.5) OVER (),2) AS percent_meeting_focused,
       ROUND(PERCENTILE_CONT(average_daily_focus_hours,0.5) OVER (),1) AS daily_focus_hours
FROM average_totals
LIMIT 1; 


------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.2: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S WORK-LIFE BALANCE BY LEVEL OF EMPLOYEE
--INCLUDES METRICS FOR MEDIAN NUMBER OF DAILY WORK HOURS AND WORK HOURS PER WEEKEND 
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId, g.level
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       e.level,
       CAST(SUM(IF(key='worklytics:weekdays:avg:timespan:hours',value,0)) AS NUMERIC) AS daily_work_hours, 
       CAST(SUM(IF(key='worklytics:weekends:total:time:worked:hours',value,0)) AS NUMERIC) AS weekly_hours_weekend
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId
AND p.week = e. week
WHERE 1=1
AND key IN ('worklytics:weekdays:avg:timespan:hours','worklytics:weekends:total:time:worked:hours')
GROUP BY 1,2,3 
),
average_totals AS (
SELECT employeeId, 
       level,
       CAST(AVG(daily_work_hours) AS NUMERIC) AS average_daily_work_hours,
       CAST(AVG(weekly_hours_weekend) AS NUMERIC) AS average_weekly_hours_weekend
FROM weekly_totals
GROUP BY 1,2
)
SELECT DISTINCT level, ROUND(PERCENTILE_CONT(average_daily_work_hours,0.5) OVER (PARTITION BY level),1) AS daily_work_hours,
       ROUND(PERCENTILE_CONT(average_weekly_hours_weekend,0.5) OVER (PARTITION BY level),1) AS weekly_hours_weekend
FROM average_totals
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.3: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S COLLABORATION CULTURE
--INCLUDES METRICS FOR MEDIAN NUMBER OF WEEKLY COLLABORATORS, WEEKLY STRONG COLLABORATORS, NUMBER OF COLLABORATORS BETWEEN LINES OF BUSINESS
--AND THE NUMBER OF HOURS SPENT COLLABORATING BETWEEN LINES OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       CAST(SUM(IF(key='collaborators:count_distinct',value,0)) AS NUMERIC) AS weekly_collaborators,
       CAST(SUM(IF(key='collaborators:strong_count_distinct',value,0)) AS NUMERIC) AS weekly_strong_collaborators,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_intrateam_hours',value,0)) AS NUMERIC) AS weekly_hours_within_lob_collaboration,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_interteam_hours',value,0)) AS NUMERIC) AS weekly_hours_between_lob_collaboration,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_intrateam_people_count',value,0)) AS NUMERIC) AS weekly_collaborators_within_lob,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_interteam_people_count',value,0)) AS NUMERIC) AS weekly_collaborators_between_lob 
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId
AND p.week = e. week
WHERE 1=1
AND key IN ('collaborators:count_distinct','collaborators:strong_count_distinct','lineofbusiness_individual_collaboration_intrateam_hours','lineofbusiness_individual_collaboration_interteam_hours','lineofbusiness_individual_collaboration_intrateam_people_count','lineofbusiness_individual_collaboration_interteam_people_count')
GROUP BY 1,2 
),
average_totals AS (
SELECT employeeId, 
       CAST(AVG(weekly_collaborators) AS NUMERIC) AS average_weekly_collaborators,
       CAST(AVG(weekly_strong_collaborators) AS NUMERIC) AS average_weekly_strong_collaborators,
       CAST(AVG(weekly_hours_within_lob_collaboration) AS NUMERIC) AS average_weekly_hours_within_lob_collaboration,
       CAST(AVG(weekly_hours_between_lob_collaboration) AS NUMERIC) AS average_weekly_hours_between_lob_collaboration,
       CAST(AVG(weekly_collaborators_within_lob) AS NUMERIC) AS average_weekly_collaborators_within_lob,
       CAST(AVG(weekly_collaborators_between_lob ) AS NUMERIC) AS average_weekly_collaborators_between_lob
FROM weekly_totals
GROUP BY 1
)
SELECT ROUND(PERCENTILE_CONT(average_weekly_collaborators,0.5) OVER (),1) AS weekly_collaborators,
       ROUND(PERCENTILE_CONT(average_weekly_strong_collaborators,0.5) OVER (),1) AS weekly_strong_collaborators,
       ROUND(PERCENTILE_CONT(average_weekly_hours_within_lob_collaboration,0.5) OVER (),1) AS weekly_hours_within_lob_collaboration, 
       ROUND(PERCENTILE_CONT(average_weekly_hours_between_lob_collaboration,0.5) OVER (),1) AS weekly_hours_between_lob_collaboration, 
       ROUND(PERCENTILE_CONT(average_weekly_collaborators_within_lob,0.5) OVER (),1) AS weekly_collaborators_within_lob, 
       ROUND(PERCENTILE_CONT(average_weekly_collaborators_between_lob,0.5) OVER (),2) AS weekly_collaborators_between_lob
FROM average_totals
LIMIT 1; 


------------------------------------------------------------------------------------------------------------------------------
--QUERY 2.1 IDENTIFY THE MEDIAN NUMBER OF COLLABORATORS AND STRONG COLLABORATORS BASED ON LINE OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, 
       a.employeeId,
       g.custom_group_1,
       g.line_of_business
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
collaborator_type AS (
SELECT c.week,
       e.line_of_business,
       employeeIdSource AS person_id_src,
       employeeIdTarget AS person_id_dst,
       SUM(weeklyCollaborationTimeSeconds)  AS time_spent_together_exclusive_ms,
       SUM(weeklyCollaborationTimeSeconds) >= (2 * 60 * 60) AS is_strong_connection
FROM {collaboration_graph} c
JOIN active_min_hours_emps e
ON c.employeeIdSource = e.employeeId
AND DATE_TRUNC(c.week, ISOWEEK) = e.week
WHERE 1=1
GROUP BY 1,2,3,4 
),
people_connections AS (
SELECT week,
       person_id_src AS person_id,
       line_of_business,
       COUNT(DISTINCT person_id_dst) AS connections,
       COUNT(DISTINCT(IF(is_strong_connection=true, person_id_dst,NULL))) AS strong_connections
FROM collaborator_type  
GROUP BY 1,2,3
)
SELECT DISTINCT line_of_business,
       ROUND(PERCENTILE_CONT(connections,0.5) OVER (PARTITION BY line_of_business),0) AS connections,
       ROUND(PERCENTILE_CONT(strong_connections,0.5) OVER (PARTITION BY line_of_business),0) AS strong_connections
FROM people_connections
WHERE line_of_business IS NOT NULL
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 2.2 IDENTIFY THE MEDIAN NUMBER OF SLACK COLLABORATORS FOR THE TYPICAL EMPLOYEE BASED ON LINE OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, 
       a.employeeId,
       g.custom_group_1,
       g.line_of_business
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
collaborator_type AS (
SELECT c.week,
       e.line_of_business,
       employeeIdSource AS person_id_src,
       employeeIdTarget AS person_id_dst
FROM {collaboration_graph_split} c
JOIN active_min_hours_emps e
ON c.employeeIdSource = e.employeeId
AND DATE_TRUNC(c.week, ISOWEEK) = e.week
WHERE 1=1
AND sourceType = 'slack'
GROUP BY 1,2,3,4 
),
people_connections_slack AS (
SELECT week,
       person_id_src AS person_id,
       line_of_business,
       COUNT(DISTINCT person_id_dst) AS slack_connections
FROM collaborator_type  
GROUP BY 1,2,3
)
SELECT DISTINCT line_of_business,
       ROUND(PERCENTILE_CONT(slack_connections,0.5) OVER (PARTITION BY line_of_business),0) AS slack_connections
FROM people_connections_slack
WHERE line_of_business IS NOT NULL
ORDER BY 1;

Sample SQL Queries: Event-Level Data

The following is a sample set of SQL queries you can use to advance beyond the basics in your use of the Worklytics dataset. This code block assumes the user has access to the employee events dataset, though the exact table names will have to be substituted for the ones in your company's environment.

If you only have access to the individual aggregates, individual groups, and collaboration graph datasets, we recommend focusing your efforts .

Sample SQL Queries: Aggregate-Level Data & Collab. Graph

/* 
SAMPLE SQL QUERIES FOR AGGREGATE-LEVEL DATA
DATE: 2024.09.19

INPUT TABLES: 
{weekly_individual_aggregates}
{weekly_individual_groups}
{collaboration_graph}
{collaboration_graph_split}


SECTION 1: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S MEETING CULTURE, FOCUS TIME, WORK-LIFE BALANCE AND COLLABORATION CULTURE USING THE INDIVIDUAL AGGREGATES TABLE
SECTION 2: IDENTIFY THE TYPICAL NUMBER OF COLLABORATORS (AND STRONG COLLABORATORS) AND TYPICAL NUMBER OF SLACK COLLABORATORS

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

*/

------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.1: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S MEETING CULTURE
--INCLUDES METRICS FOR MEDIAN NUMBER OF WEEKLY MEETING HOURS, % OF MEETING TIME FOCUSED AND DAILY FOCUS HOURS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       CAST(SUM(IF(key='calendar:events:hours:meetings',value,0)) AS NUMERIC) AS weekly_meeting_hours,
       1.0*CAST(SUM(IF(key='calendar:events:focus',value,0)) AS NUMERIC)/100.0 AS percent_meeting_focused,      
       CAST(SUM(IF(key='worklytics:hours:in:focus:blocks:v3:flow',value,0)) AS NUMERIC) AS daily_focus_hours             
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId 
AND p.week = e. week
WHERE 1=1
AND key IN ('calendar:events:hours:meetings','calendar:events:focus','worklytics:hours:in:focus:blocks:v3:flow')
GROUP BY 1,2 
),
average_totals AS (
SELECT employeeId, 
       CAST(AVG(weekly_meeting_hours) AS NUMERIC) AS average_weekly_meeting_hours,
       CAST(AVG(percent_meeting_focused) AS NUMERIC) AS average_percent_meeting_focused,
       CAST(AVG(daily_focus_hours) AS NUMERIC) AS average_daily_focus_hours
FROM weekly_totals
GROUP BY 1
)
SELECT ROUND(PERCENTILE_CONT(average_weekly_meeting_hours,0.5) OVER (),1) AS weekly_meeting_hours,
       ROUND(PERCENTILE_CONT(average_percent_meeting_focused,0.5) OVER (),2) AS percent_meeting_focused,
       ROUND(PERCENTILE_CONT(average_daily_focus_hours,0.5) OVER (),1) AS daily_focus_hours
FROM average_totals
LIMIT 1; 


------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.2: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S WORK-LIFE BALANCE BY LEVEL OF EMPLOYEE
--INCLUDES METRICS FOR MEDIAN NUMBER OF DAILY WORK HOURS AND WORK HOURS PER WEEKEND 
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId, g.level
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       e.level,
       CAST(SUM(IF(key='worklytics:weekdays:avg:timespan:hours',value,0)) AS NUMERIC) AS daily_work_hours, 
       CAST(SUM(IF(key='worklytics:weekends:total:time:worked:hours',value,0)) AS NUMERIC) AS weekly_hours_weekend
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId
AND p.week = e. week
WHERE 1=1
AND key IN ('worklytics:weekdays:avg:timespan:hours','worklytics:weekends:total:time:worked:hours')
GROUP BY 1,2,3 
),
average_totals AS (
SELECT employeeId, 
       level,
       CAST(AVG(daily_work_hours) AS NUMERIC) AS average_daily_work_hours,
       CAST(AVG(weekly_hours_weekend) AS NUMERIC) AS average_weekly_hours_weekend
FROM weekly_totals
GROUP BY 1,2
)
SELECT DISTINCT level, ROUND(PERCENTILE_CONT(average_daily_work_hours,0.5) OVER (PARTITION BY level),1) AS daily_work_hours,
       ROUND(PERCENTILE_CONT(average_weekly_hours_weekend,0.5) OVER (PARTITION BY level),1) AS weekly_hours_weekend
FROM average_totals
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 1.3: IDENTIFY KEY METRICS ABOUT YOUR ORGANIZATION'S COLLABORATION CULTURE
--INCLUDES METRICS FOR MEDIAN NUMBER OF WEEKLY COLLABORATORS, WEEKLY STRONG COLLABORATORS, NUMBER OF COLLABORATORS BETWEEN LINES OF BUSINESS
--AND THE NUMBER OF HOURS SPENT COLLABORATING BETWEEN LINES OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
weekly_totals AS (
SELECT p.week, 
       p.employeeId, 
       CAST(SUM(IF(key='collaborators:count_distinct',value,0)) AS NUMERIC) AS weekly_collaborators,
       CAST(SUM(IF(key='collaborators:strong_count_distinct',value,0)) AS NUMERIC) AS weekly_strong_collaborators,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_intrateam_hours',value,0)) AS NUMERIC) AS weekly_hours_within_lob_collaboration,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_interteam_hours',value,0)) AS NUMERIC) AS weekly_hours_between_lob_collaboration,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_intrateam_people_count',value,0)) AS NUMERIC) AS weekly_collaborators_within_lob,
       CAST(SUM(IF(key='lineofbusiness_individual_collaboration_interteam_people_count',value,0)) AS NUMERIC) AS weekly_collaborators_between_lob 
FROM {weekly_individual_aggregates} p 
JOIN active_min_hours_emps e 
ON p.employeeId = e.employeeId
AND p.week = e. week
WHERE 1=1
AND key IN ('collaborators:count_distinct','collaborators:strong_count_distinct','lineofbusiness_individual_collaboration_intrateam_hours','lineofbusiness_individual_collaboration_interteam_hours','lineofbusiness_individual_collaboration_intrateam_people_count','lineofbusiness_individual_collaboration_interteam_people_count')
GROUP BY 1,2 
),
average_totals AS (
SELECT employeeId, 
       CAST(AVG(weekly_collaborators) AS NUMERIC) AS average_weekly_collaborators,
       CAST(AVG(weekly_strong_collaborators) AS NUMERIC) AS average_weekly_strong_collaborators,
       CAST(AVG(weekly_hours_within_lob_collaboration) AS NUMERIC) AS average_weekly_hours_within_lob_collaboration,
       CAST(AVG(weekly_hours_between_lob_collaboration) AS NUMERIC) AS average_weekly_hours_between_lob_collaboration,
       CAST(AVG(weekly_collaborators_within_lob) AS NUMERIC) AS average_weekly_collaborators_within_lob,
       CAST(AVG(weekly_collaborators_between_lob ) AS NUMERIC) AS average_weekly_collaborators_between_lob
FROM weekly_totals
GROUP BY 1
)
SELECT ROUND(PERCENTILE_CONT(average_weekly_collaborators,0.5) OVER (),1) AS weekly_collaborators,
       ROUND(PERCENTILE_CONT(average_weekly_strong_collaborators,0.5) OVER (),1) AS weekly_strong_collaborators,
       ROUND(PERCENTILE_CONT(average_weekly_hours_within_lob_collaboration,0.5) OVER (),1) AS weekly_hours_within_lob_collaboration, 
       ROUND(PERCENTILE_CONT(average_weekly_hours_between_lob_collaboration,0.5) OVER (),1) AS weekly_hours_between_lob_collaboration, 
       ROUND(PERCENTILE_CONT(average_weekly_collaborators_within_lob,0.5) OVER (),1) AS weekly_collaborators_within_lob, 
       ROUND(PERCENTILE_CONT(average_weekly_collaborators_between_lob,0.5) OVER (),2) AS weekly_collaborators_between_lob
FROM average_totals
LIMIT 1; 


------------------------------------------------------------------------------------------------------------------------------
--QUERY 2.1 IDENTIFY THE MEDIAN NUMBER OF COLLABORATORS AND STRONG COLLABORATORS BASED ON LINE OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, 
       a.employeeId,
       g.custom_group_1,
       g.line_of_business
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
collaborator_type AS (
SELECT c.week,
       e.line_of_business,
       employeeIdSource AS person_id_src,
       employeeIdTarget AS person_id_dst,
       SUM(weeklyCollaborationTimeSeconds)  AS time_spent_together_exclusive_ms,
       SUM(weeklyCollaborationTimeSeconds) >= (2 * 60 * 60) AS is_strong_connection
FROM {collaboration_graph} c
JOIN active_min_hours_emps e
ON c.employeeIdSource = e.employeeId
AND DATE_TRUNC(c.week, ISOWEEK) = e.week
WHERE 1=1
GROUP BY 1,2,3,4 
),
people_connections AS (
SELECT week,
       person_id_src AS person_id,
       line_of_business,
       COUNT(DISTINCT person_id_dst) AS connections,
       COUNT(DISTINCT(IF(is_strong_connection=true, person_id_dst,NULL))) AS strong_connections
FROM collaborator_type  
GROUP BY 1,2,3
)
SELECT DISTINCT line_of_business,
       ROUND(PERCENTILE_CONT(connections,0.5) OVER (PARTITION BY line_of_business),0) AS connections,
       ROUND(PERCENTILE_CONT(strong_connections,0.5) OVER (PARTITION BY line_of_business),0) AS strong_connections
FROM people_connections
WHERE line_of_business IS NOT NULL
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 2.2 IDENTIFY THE MEDIAN NUMBER OF SLACK COLLABORATORS FOR THE TYPICAL EMPLOYEE BASED ON LINE OF BUSINESS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, 
       a.employeeId,
       g.custom_group_1,
       g.line_of_business
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
collaborator_type AS (
SELECT c.week,
       e.line_of_business,
       employeeIdSource AS person_id_src,
       employeeIdTarget AS person_id_dst
FROM {collaboration_graph_split} c
JOIN active_min_hours_emps e
ON c.employeeIdSource = e.employeeId
AND DATE_TRUNC(c.week, ISOWEEK) = e.week
WHERE 1=1
AND sourceType = 'slack'
GROUP BY 1,2,3,4 
),
people_connections_slack AS (
SELECT week,
       person_id_src AS person_id,
       line_of_business,
       COUNT(DISTINCT person_id_dst) AS slack_connections
FROM collaborator_type  
GROUP BY 1,2,3
)
SELECT DISTINCT line_of_business,
       ROUND(PERCENTILE_CONT(slack_connections,0.5) OVER (PARTITION BY line_of_business),0) AS slack_connections
FROM people_connections_slack
WHERE line_of_business IS NOT NULL
ORDER BY 1;

/* 
SAMPLE SQL QUERIES FOR AGGREGATES AND EVENT-LEVEL DATA
DATE: 2024.09.19

INPUT TABLES: 
{weekly_individual_aggregates}
{weekly_individual_groups}
{collaboration_graph}
{employee_events}

SECTIONS:
SECTION 3: IDENTIFY THE TYPICAL WORKDAY SPAN AND NO. OF HOURS SPENT IN MEETINGS BY WEEKDAY USING THE EVENT-LEVEL TABLE
SECTION 4: IDENTIFY THE CO-LOCATION OF STRONG COLLABORATORS BASED ON BUILDING BADGED INTO

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

*/

------------------------------------------------------------------------------------------------------------------------------
--QUERY 3.1: FOR EACH WEEKDAY WHAT IS THE TYPICAL WORKDAY SPAN OF THE AVERAGE EMPLOYEE
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId AS identifier
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
workday_span AS (
SELECT date(r.eventTime) AS day,
       FORMAT_DATE('%A',date(r.eventTime)) AS week_day,
       r.employeeId AS personid,
       MAX(time(eventTime)) - MIN(time(eventTime)) AS workday_span
FROM {employee_events} AS r
JOIN active_min_hours_emps AS g
ON r.employeeId = g.identifier
AND r.week = g.week
WHERE 1=1
AND r.employeeId IS NOT NULL
AND r.estimatedTimeSec > 0
AND FORMAT_DATE('%A',date(r.eventTime)) NOT IN ('Saturday', 'Sunday')
GROUP BY 1,2,3
),
total_time_disaggregated AS (
SELECT day,
       personid,
       week_day,
       workday_span,
       (EXTRACT(HOUR FROM workday_span) + EXTRACT(MINUTE FROM workday_span)/60) AS workday_span_hours
FROM workday_span d
),
averages AS (
SELECT personid,
       week_day,
       CAST(AVG(workday_span_hours) AS numeric) AS workday_span_hours
FROM total_time_disaggregated
GROUP BY 1,2
)
SELECT DISTINCT week_day,
       ROUND(PERCENTILE_CONT(workday_span_hours,0.5)  OVER (PARTITION BY week_day),1) AS median_workday_span
FROM averages
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 3.2: FOR EACH WEEKDAY WHAT IS THE TYPICAL NO. OF HOURS EMPLOYEES SPEND IN MEETINGS
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
------------------------------------------------------------------------------------------------------------------------------
WITH active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
raw_events_data AS (
SELECT date(r.eventTime) AS day,
       r.employeeId,
       sourceKindId,
       eventType,
       COUNT(DISTINCT id) AS num_actions,
       SUM(estimatedTimeSec) AS exclusive_time
FROM {employee_events} AS r
JOIN active_min_hours_emps AS g
ON r.employeeId = g.employeeId
AND r.week = g.week
WHERE 1=1
AND r.employeeId IS NOT NULL
AND r.estimatedTimeSec > 0
GROUP BY 1,2,3,4
HAVING eventType IN ('attendance','occurrence')
AND sourceKindId IS NOT NULL
),
raw_data_consolidated AS (
SELECT day,
       employeeId,
       FORMAT_DATE('%A', day) AS week_day,
       CASE WHEN CONCAT(sourceKindId, ' | ', eventType) = 'gcal | attendance' THEN 'attending meetings'
       WHEN CONCAT(sourceKindId, ' | ', eventType) = 'zoom | occurrence' THEN 'attending meetings'
       ELSE 'other' END AS activity_type,
       num_actions,
       exclusive_time
FROM raw_events_data
WHERE 1=1 
AND FORMAT_DATE('%A', day) NOT IN ('Saturday', 'Sunday')
),
activities_grouped AS (
SELECT employeeId,
       day,
       week_day,
       activity_type,
       SUM(exclusive_time)/3600 AS time_activities_hours
FROM raw_data_consolidated
GROUP BY 1,2,3,4
),
averages AS (
SELECT employeeId,
       week_day,
       activity_type,
       CAST(AVG(time_activities_hours) AS numeric) AS activities_hours
FROM activities_grouped
GROUP BY 1,2,3
)
SELECT DISTINCT week_day,
       activity_type,
       ROUND(PERCENTILE_CONT(activities_hours,0.5)  OVER (PARTITION BY week_day, activity_type),1) AS activity_hours
FROM averages
WHERE 1=1
AND activity_type IS NOT NULL
ORDER BY 1;


------------------------------------------------------------------------------------------------------------------------------
--QUERY 4.1: IDENTIFY THE PERCENTAGE OF A TYPICAL EMPLOYEE'S CLOSE COLLABORATORS THAT ARE COLOCATED BASED ON BADGE DATA 
--NOTE THAT METRICS INCLUDE ONLY ACTIVE EMPLOYEES THAT WORKED A MINIMUM OF 4 HOURS EACH DAY AND INCLUDES ONLY FULL-TIME EMPLOYEES
--NOTE THAT THE QUERY IS OPTIMIZED FOR WEEKLY DATA BUT CAN BE SWAPPED TO DAY
------------------------------------------------------------------------------------------------------------------------------
WITH building_badge_data AS (
SELECT week,
       person_id,
       time_first_visit,
       building_id
FROM (
       SELECT week,
              ARRAY(SELECT props FROM UNNEST(eventProperties) AS props WHERE props.name='buildingId')[OFFSET(0)].value AS building_id,
              employeeId AS person_id,
              MIN(eventTime) AS time_first_visit
       FROM {employee_events}
       WHERE 1=1 
       AND week >= '2024-01-01' AND week < '2024-03-31'
       AND sourceKindId ='badge' 
       AND employeeId IS NOT NULL
       GROUP BY 1,2,3)
),
building_colocating AS (
SELECT week,
       person_id,
       ARRAY_AGG(DISTINCT building_id IGNORE NULLS) AS building_ids,
       ARRAY_AGG(DISTINCT building_buddy_id IGNORE NULLS) AS building_buddy_ids
FROM (
       SELECT b.week,
              b.person_id,
              b.building_id,
              b2.person_id AS building_buddy_id
       FROM building_badge_data AS b
       LEFT JOIN building_badge_data AS b2
       ON b.week = b2.week
       AND b.building_id = b2.building_id
       AND b.person_id != b2.person_id)
GROUP BY 1,2
),
active_min_hours_emps AS (
SELECT DISTINCT a.week, a.employeeId AS identifier
FROM {weekly_individual_aggregates} a
JOIN {weekly_individual_groups} g 
ON a.employeeId = g.employeeId 
AND a.week = g.week
WHERE 1=1
AND g.week >= '2024-01-01' AND g.week < '2024-03-31'
AND g.custom_group_1 IN ('Full-Time') -- exact filter will vary by company
AND g.active = true
AND (a.key = 'worklytics:weekdays:avg:timespan:hours' AND a.value >= 4) 
),
collaborator_type AS (
SELECT c.week,
       employeeIdSource AS person_id_src,
       employeeIdTarget AS person_id_dst,
       SUM(weeklyCollaborationTimeSeconds)  AS time_spent_together_exclusive_ms,
       SUM(weeklyCollaborationTimeSeconds) >= (2 * 60 * 60) AS is_strong_connection,
       SUM(weeklyCollaborationTimeSeconds) <= (10 * 60) AS is_weak_connection
FROM {collaboration_graph} c
JOIN active_min_hours_emps e
ON c.employeeIdSource = e.identifier
AND DATE_TRUNC(c.week, ISOWEEK) = e.week
JOIN active_min_hours_emps e2
ON c.employeeIdTarget = e2.identifier
AND DATE_TRUNC(c.week, ISOWEEK) = e2.week
WHERE 1=1
GROUP BY 1,2,3 
),
people_collaborator_connections AS (
SELECT week,
       person_id_src AS person_id,
       ARRAY_AGG(DISTINCT person_id_dst IGNORE NULLS) AS connection_ids_all,
       ARRAY_AGG(DISTINCT IF(is_weak_connection, person_id_dst, NULL) IGNORE NULLS) AS connection_ids_weak,
       ARRAY_AGG(DISTINCT IF(is_strong_connection, person_id_dst, NULL) IGNORE NULLS) AS connection_ids_strong,
FROM collaborator_type  
GROUP BY 1,2 
),
people_colocating_badge AS (
SELECT onsite.week,
       onsite.person_id,
       onsite.building_id_first,
       ARRAY_LENGTH(connection_ids_strong) AS strong_collaborator_count,
       ARRAY_LENGTH(building_ids) AS building_count,
       ARRAY_LENGTH(building_buddy_ids) AS colocated_people_count,
       ARRAY_LENGTH(
       ARRAY(
       SELECT * FROM UNNEST(building_buddy_ids)
       INTERSECT DISTINCT
       SELECT * FROM UNNEST(connection_ids_strong)))
        AS strong_collaborator_colocated_count
FROM (SELECT week,
             person_id,
             ARRAY_AGG(bd.building_id IGNORE NULLS ORDER BY time_first_visit)[ORDINAL(1)] as building_id_first
      FROM building_badge_data bd 
      GROUP BY 1,2
) AS onsite 
LEFT JOIN building_colocating bc
ON onsite.week = bc.week
AND onsite.person_id = bc.person_id
LEFT JOIN people_collaborator_connections cc
ON onsite.week = cc.week
AND onsite.person_id = cc.person_id
WHERE 1=1
GROUP BY 1,2,3,4,5,6,7
),
people_colocating_badge_totals AS (
SELECT building_id_first,
       week,
       person_id,
       SUM(strong_collaborator_count) AS strong_collaborator,
       SUM(strong_collaborator_colocated_count) AS strong_collaborator_colocated
FROM people_colocating_badge
GROUP BY 1,2,3
)
SELECT DISTINCT building_id_first,
       ROUND(PERCENTILE_CONT(strong_collaborator,0.5) OVER (PARTITION BY building_id_first),0) AS strong_collaborator,
       ROUND(PERCENTILE_CONT(strong_collaborator_colocated,0.5) OVER (PARTITION BY building_id_first),0) AS strong_collaborator_colocated
FROM people_colocating_badge_totals 
ORDER BY 1;