Task Dependency Analysis
1with
2 TaskDependencies as (
3 select
4 td.task_id
5 , t.name as task_name
6 , t.status_status as task_status
7 , td.depends_on as dependent_task_id
8 , dt.name as dependent_task_name
9 , dt.status_status as dependent_task_status
10 from
11 {{raw.clickup.task_dependencies}} td
12 join {{raw.clickup.task}} t on td.task_id = t.id
13 left join {{raw.clickup.task}} dt on td.depends_on = dt.id
14 )
15 , DependencyCounts as (
16 select
17 task_id
18 , count(dependent_task_id) as total_dependencies
19 from
20 TaskDependencies
21 group by
22 task_id
23 )
24select
25 td.task_id
26 , td.task_name
27 , td.task_status
28 , td.dependent_task_id
29 , td.dependent_task_name
30 , td.dependent_task_status
31 , case
32 when td.task_status = 'Completed'
33 and td.dependent_task_status != 'Completed' then 'Potential Blocker'
34 else 'No Blocker'
35 end as blocker_status
36 , dc.total_dependencies
37from
38 TaskDependencies td
39 join DependencyCounts dc on td.task_id = dc.task_id
40order by
41 td.task_id
42 , td.dependent_task_id;
+---------+------------------+-------------+-------------------+------------------------+-------------------------+----------------+-------------------+
| task_id | task_name | task_status | dependent_task_id | dependent_task_name | dependent_task_status | blocker_status | total_dependencies|
+---------+------------------+-------------+-------------------+------------------------+-------------------------+----------------+-------------------+
| 1 | Task 1 | Completed | 2 | Dependent 1 | In Progress | Potential Blocker| 2 |
| 1 | Task 1 | Completed | 3 | Dependent 2 | Completed | No Blocker | 2 |
| 2 | Task 2 | In Progress | 3 | Dependent 2 | Completed | No Blocker | 1 |
| 3 | Task 3 | Completed | 4 | Dependent 3 | In Progress | Potential Blocker| 1 |
| 4 | Task 4 | In Progress | 5 | Dependent 4 | In Progress | Potential Blocker| 1 |
+---------+------------------+-------------+-------------------+------------------------+-------------------------+----------------+-------------------+
The SQL template named "Task Dependency Analysis" is designed to analyze task dependencies in the ClickUp integration. This SQL model retrieves information about task dependencies, including the task ID, task name, task status, dependent task ID, dependent task name, dependent task status, and the total number of dependencies for each task. By executing this SQL template, you can gain insights into the task dependencies within ClickUp. The output provides a comprehensive view of the relationships between tasks and their dependencies. Additionally, the "blocker_status" column categorizes the relationship between tasks based on their completion status. If a task is marked as "Completed" but its dependent task is not, it is labeled as a "Potential Blocker." Otherwise, it is labeled as "No Blocker." This SQL template can be useful for project managers and teams working with ClickUp to understand the impact of task dependencies on project progress. It helps identify potential blockers and allows for better planning and coordination to ensure smooth task execution and project completion.
