Weld logo
clickup logo
Clickup

User Activity and Performance Report

Generates a user activity and performance report by integrating with ClickUp. It retrieves information such as the total number of tasks, tasks completed, tasks overdue, total time spent, task priority, task status, and task count by priority and status for each user. The results are ordered by user ID.
1with
2    UserTasks as (
3        select
4            ta.id as user_id
5          , ta.username
6          , count(distinct t.id) as total_tasks
7          , count(
8                distinct case
9                    when t.date_done is not null then t.id
10                    else null
11                end
12            ) as tasks_completed
13          , count(
14                distinct case
15                    when t.date_done is null
16                    and cast(timestamp_millis(cast(t.due_date as int)) as date) < current_date then t.id
17                    else null
18                end
19            ) as tasks_overdue
20        from
21            {{raw.clickup.task_assignees}}ta
22            join {{raw.clickup.task}} t on ta.task_id = t.id
23        group by
24            ta.id
25          , ta.username
26    )
27  , UserTime as (
28        select
29            user_id
30          , sum(duration) as total_time_spent
31        from
32            {{raw.clickup.time}}
33        group by
34            user_id
35    )
36  , UserTaskDetails as (
37        select
38            ta.id as user_id
39          , t.priority
40          , t.status_status
41          , count(t.id) as task_count
42        from
43            {{raw.clickup.task_assignees}} ta
44            join {{raw.clickup.task}} t on ta.task_id = t.id
45        group by
46            ta.id
47          , t.priority
48          , t.status_status
49    )
50select
51    u.id as user_id
52  , u.username
53  , u.email
54  , u.color
55  , u.initials
56  , u.last_active
57  , ut.total_tasks
58  , ut.tasks_completed
59  , ut.tasks_overdue
60  , utme.total_time_spent
61  , utd.priority
62  , utd.status_status
63  , utd.task_count as tasks_by_priority_status
64from
65    {{raw.clickup.user}} u
66    left join UserTasks ut on u.id = ut.user_id
67    left join UserTime utme on u.id = utme.user_id
68    left join UserTaskDetails utd on u.id = utd.user_id
69order by
70    u.id;
Example of output from model:

+---------+----------+-----------------------+-------+---------+---------------------+-------------+-----------------+--------------+-------------------+--------------+-------------------------+
| user_id | username | email                 | color | initials| last_active         | total_tasks | tasks_completed | tasks_overdue| total_time_spent  | priority     | status_status           |
+---------+----------+-----------------------+-------+---------+---------------------+-------------+-----------------+--------------+-------------------+--------------+-------------------------+
| 1       | John     | john@example.com      | blue  | J       | 2021-07-15 10:30:00 | 10          | 8               | 2            | 120               | High         | In Progress             |
| 2       | Jane     | jane@example.com      | green | J       | 2021-07-14 15:45:00 | 15          | 12              | 3            | 180               | Medium       | Completed               |
| 3       | Alex     | alex@example.com      | red   | A       | 2021-07-15 09:15:00 | 5           | 4               | 1            | 60                | Low          | In Progress             |
| 4       | Sarah    | sarah@example.com     | yellow| S       | 2021-07-14 12:00:00 | 8           | 6               | 2            | 90                | High         | In Progress             |
| 5       | Mike     | mike@example.com      | purple| M       | 2021-07-15 11:00:00 | 12          | 10              | 2            | 150               | Medium       | In Progress             |
+---------+----------+-----------------------+-------+---------+---------------------+-------------+-----------------+--------------+-------------------+--------------+-------------------------+

The SQL template "User Activity and Performance Report" is designed to gather data from the ClickUp integration. It provides insights into user activity and performance based on various metrics. The SQL code consists of several common table expressions (CTEs) that extract relevant information from the ClickUp database. The "UserTasks" CTE calculates the total number of tasks, tasks completed, and tasks overdue for each user. The "UserTime" CTE calculates the total time spent by each user. The "UserTaskDetails" CTE provides additional details such as task priority, status, and task count for each user. The final SELECT statement combines the data from the ClickUp "user" table with the CTEs to generate a comprehensive report. It includes user details such as user ID, username, email, color, initials, and last active date. Additionally, it includes metrics like total tasks, tasks completed, tasks overdue, total time spent, task priority, status, and task count by priority and status. This SQL template can be useful for monitoring user activity and performance in ClickUp. It allows you to identify the number of tasks assigned to each user, their completion rate, and any overdue tasks. Furthermore, it provides insights into the total time spent by each user and the distribution of tasks based on priority and status. This information can help track user productivity, identify bottlenecks, and optimize task management in ClickUp.

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.