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.
- Grain
- See the notes
- Built from
- 3Raw tables
- 1Core
- 1Analytics
- Reads from ClickUp
- taskteamtime
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.clickup.team_overview_report
2-- Team Overview Report. Generates a team overview report by retrieving data from
3-- the ClickUp integration. It calculates the total number of tasks, total time
4-- spent, task status, and task count for each team. The report includes team ID,
5-- name, color, total tasks, total time spent, task status, and tasks in each
6-- status. The results are ordered by team ID.
7--
8-- From the weld.app template library. Not reconciled against ClickUp;
9-- check the output against the source before relying on it.
10
11with
12 TeamTasks as (
13 select
14 t.team_id
15 , count(distinct t.id) as total_tasks
16 from
17 {{raw.clickup.task}} t
18 group by
19 t.team_id
20 )
21 , TeamTime as (
22 select
23 t.team_id
24 , sum(time.duration) as total_time_spent
25 from
26 {{raw.clickup.time}} time
27 join {{raw.clickup.task}} t on time.task_id = t.id
28 group by
29 t.team_id
30 )
31 , TeamTaskStatus as (
32 select
33 t.team_id
34 , t.status_status as task_status
35 , count(distinct t.id) as task_count
36 from
37 {{raw.clickup.task}} t
38 group by
39 t.team_id
40 , t.status_statusThis model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
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.
Raw tables it reads
- raw.clickup.task
- raw.clickup.team
- raw.clickup.time
Put ClickUp in front
of your agents.
Connect ClickUp, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.