User Activity and Performance Report
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;
+---------+----------+-----------------------+-------+---------+---------------------+-------------+-----------------+--------------+-------------------+--------------+-------------------------+
| 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.
