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.
- Grain
- See the notes
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from ClickUp
- tasktask_checklists
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.clickup.task_checklist_progress
2-- Task Checklist Progress. Generates a report on the progress of task checklists in
3-- ClickUp. It retrieves the total number of checklist items, resolved items, and
4-- unresolved items for each task. It calculates the completion percentage based on
5-- the resolved items and total checklist items. The results are sorted by
6-- completion percentage and task 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 TaskChecklistSummary as (
13 select
14 task_id
15 , count(*) as total_checklist_items
16 , sum(resolved) as resolved_items
17 , sum(unresolved) as unresolved_items
18 from
19 {{raw.clickup.task_checklists}}
20 group by
21 task_id
22 )
23select
24 t.id as task_id
25 , t.name as task_name
26 , coalesce(c.total_checklist_items, 0) as total_checklist_items
27 , coalesce(c.resolved_items, 0) as resolved_items
28 , coalesce(c.unresolved_items, 0) as unresolved_items
29 , case
30 when coalesce(c.total_checklist_items, 0) = 0 then 0
31 else round(
32 (coalesce(c.resolved_items, 0) * 100.0) / c.total_checklist_items
33 , 2
34 )
35 end as completion_percentage
36from
37 {{raw.clickup.task}} t
38 left join TaskChecklistSummary c on t.id = c.task_id
39order by
40 completion_percentage descThis 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 "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.
Raw tables it reads
- raw.clickup.task
- raw.clickup.task_checklists
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.