Team Overview Report
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;
+---------+--------------+------------+-------------+-------------------+-------------+-----------------+
| 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.
