Task Dependency Analysis

1-- core.clickup.task_dependency_analysis
2-- Task Dependency Analysis. Performs a task dependency analysis in ClickUp. It
3-- retrieves information about task dependencies, including task names, statuses,
4-- and dependent task details. It also calculates the total number of dependencies
5-- for each task and flags dependencies that are still holding a task up. The
6-- results are sorted by task ID and dependent task ID.
7--
8-- Grain: task x task it depends on. In ClickUp's dependency list, task_id is
9-- waiting on depends_on. A dependency is a potential blocker while the waiting
10-- task is still open and the task it waits on is not finished. Finished means
11-- a status of type closed or done, so custom status names work too.
12-- If the task waited on is not in the synced task table (deleted, or in a
13-- space that is not synced), its status is unknown and the row says so.
14--
15-- From the weld.app template library, corrected. Not reconciled against
16-- ClickUp; check the output against the source before relying on it.
17
18with
19    TaskDependencies as (
20        select
21            td.task_id
22          , t.name as task_name
23          , t.status_status as task_status
24          , coalesce(t.status_type in ('closed', 'done'), false) as task_finished
25          , td.depends_on as dependent_task_id
26          , dt.id is not null as dependent_task_found
27          , dt.name as dependent_task_name
28          , dt.status_status as dependent_task_status
29          , coalesce(dt.status_type in ('closed', 'done'), false) as dependent_task_finished
30        from
31            {{raw.clickup.task_dependencies}} td
32            join {{raw.clickup.task}} t on td.task_id = t.id
33            left join {{raw.clickup.task}} dt on td.depends_on = dt.id
34    )
35  , DependencyCounts as (
36        select
37            task_id
38          , count(distinct dependent_task_id) as total_dependencies
39        from
40            TaskDependencies