Task Checklist Progress
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;
+---------+-------------------+-----------------------+-----------------+---------------------+----------------------+
| 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.
