Weld logo
clickup
Clickup

Team Overview Report

Generates a team overview report by retrieving data from the ClickUp integration. It calculates the total number of tasks, total time spent, task status, and task count for each team. The report includes team ID, name, color, total tasks, total time spent, task status, and tasks in each status. The results are ordered by team ID.
1with
2    TeamTasks as (
3        select
4            t.team_id
5          , count(distinct t.id) as total_tasks
6        from
7            {{raw.clickup.task}} t
8        group by
9            t.team_id
10    )
11  , TeamTime as (
12        select
13            t.team_id
14          , sum(time.duration) as total_time_spent
15        from
16            {{raw.clickup.time}} time
17            join {{raw.clickup.task}} t on time.task_id = t.id
18        group by
19            t.team_id
20    )
21  , TeamTaskStatus as (
22        select
23            t.team_id
24          , t.status_status as task_status
25          , count(distinct t.id) as task_count
26        from
27            {{raw.clickup.task}} t
28        group by
29            t.team_id
30          , t.status_status
31    )
32select
33    team.id as team_id
34  , team.name as team_name
35  , team.color as team_color
36  , coalesce(tt.total_tasks, 0) as total_tasks
37  , coalesce(ttime.total_time_spent, 0) as total_time_spent
38  , coalesce(tts.task_status, 'N/A') as task_status
39  , coalesce(tts.task_count, 0) as tasks_in_status
40from
41    {{raw.clickup.team}} team
42    left join TeamTasks tt on team.id = tt.team_id
43    left join TeamTime ttime on team.id = ttime.team_id
44    left join TeamTaskStatus tts on team.id = tts.team_id
45order by
46    team.id;
Example of output from model:

+---------+--------------+------------+-------------+-------------------+-------------+-----------------+
| team_id | team_name    | team_color | total_tasks | total_time_spent | task_status | tasks_in_status |
+---------+--------------+------------+-------------+-------------------+-------------+-----------------+
| 1       | Team A       | #FF0000    | 10          | 120               | In Progress | 5               |
| 2       | Team B       | #00FF00    | 15          | 180               | Completed   | 10              |
| 3       | Team C       | #0000FF    | 5           | 60                | N/A         | 0               |
+---------+--------------+------------+-------------+-------------------+-------------+-----------------+

The SQL template "Team Overview Report" is designed to provide a comprehensive overview of team performance using data from the ClickUp integration. This SQL model combines information from multiple tables to generate insights about team tasks, time spent, and task status. The SQL code begins by creating three temporary tables: TeamTasks, TeamTime, and TeamTaskStatus. The TeamTasks table calculates the total number of tasks for each team by counting the distinct task IDs associated with each team ID. The TeamTime table calculates the total time spent by each team by summing the duration of tasks joined with the time table on matching task IDs. The TeamTaskStatus table calculates the task count for each team and task status combination by counting the distinct task IDs grouped by team ID and task status. The final SELECT statement retrieves the desired columns from the ClickUp team table, including team ID, team name, and team color. It also includes the calculated metrics such as total tasks, total time spent, task status, and tasks in each status. The COALESCE function is used to handle cases where there is no data available, replacing null values with appropriate defaults. The result set is ordered by team ID to provide a consistent view of team performance. This SQL template can be useful for generating team overview reports that provide insights into team productivity, task distribution, time allocation, and task status. By analyzing the output, stakeholders can identify high-performing teams, track progress, and make informed decisions to optimize team performance and resource allocation.

Ready to start modeling your own clickup data?

Get started building your data warehouse with clickup and 100+ more apps and databases available.

clickup
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.