Weld logo
clickup logo
Clickup

Task Checklist Progress

Generates a report on the progress of task checklists in ClickUp. It retrieves the total number of checklist items, resolved items, and unresolved items for each task. It calculates the completion percentage based on the resolved items and total checklist items. The results are sorted by completion percentage and task ID.
1with
2    TaskChecklistSummary as (
3        select
4            task_id
5          , count(*) as total_checklist_items
6          , sum(resolved) as resolved_items
7          , sum(unresolved) as unresolved_items
8        from
9            {{raw.clickup.task_checklists}}
10        group by
11            task_id
12    )
13select
14    t.id as task_id
15  , t.name as task_name
16  , coalesce(c.total_checklist_items, 0) as total_checklist_items
17  , coalesce(c.resolved_items, 0) as resolved_items
18  , coalesce(c.unresolved_items, 0) as unresolved_items
19  , case
20        when coalesce(c.total_checklist_items, 0) = 0 then 0
21        else round(
22            (coalesce(c.resolved_items, 0) * 100.0) / c.total_checklist_items
23          , 2
24        )
25    end as completion_percentage
26from
27    {{raw.clickup.task}} t
28    left join TaskChecklistSummary c on t.id = c.task_id
29order by
30    completion_percentage desc
31  , task_id;
Example of output from model:

+---------+-------------------+-----------------------+-----------------+---------------------+----------------------+
| task_id |    task_name      | total_checklist_items | resolved_items  | unresolved_items   | completion_percentage|
+---------+-------------------+-----------------------+-----------------+---------------------+----------------------+
|   123   |   Task 1          |          5            |        3        |         2           |         60.00        |
|   456   |   Task 2          |          8            |        6        |         2           |         75.00        |
|   789   |   Task 3          |          0            |        0        |         0           |         0.00         |
+---------+-------------------+-----------------------+-----------------+---------------------+----------------------+

The SQL template "Task Checklist Progress" is designed to analyze the progress of task checklists in ClickUp. It retrieves data from the "task_checklists" table in the ClickUp integration and calculates various metrics related to checklist items. The SQL code first creates a temporary table called "TaskChecklistSummary" using a common table expression (CTE). This table aggregates the checklist data by task, counting the total number of checklist items, the number of resolved items, and the number of unresolved items. The main query then selects data from the "task" table in ClickUp and left joins it with the "TaskChecklistSummary" table based on the task ID. This allows for the retrieval of task-specific information along with the corresponding checklist summary data. The resulting output includes the task ID, task name, total checklist items, resolved items, unresolved items, and completion percentage. The completion percentage is calculated by dividing the number of resolved items by the total checklist items and multiplying by 100, rounded to two decimal places. This SQL template is useful for tracking and monitoring the progress of task checklists in ClickUp. It provides insights into the overall completion percentage of checklists for each task, allowing users to identify tasks with higher completion rates and those that require further attention. The output can be used to prioritize tasks, assess team productivity, and ensure that all checklist items are being addressed effectively.

Ready to start modeling your own clickup data?

Get started building your data warehouse with clickup and connect all your apps and databases.

clickup logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.