Open task workload

1-- core.asana.open_task_workload
2-- Current open workload per assignee and project: what is open, overdue and due
3-- soon. A snapshot as of the last sync, not a history.
4-- Grain: assignee x project.
5--
6-- "Overdue" follows Asana: a task with a due time (due_at) is overdue once that
7-- moment has passed, a task with only a due date once that date is over. Both
8-- are compared with the current UTC time. A task in two
9-- projects appears under both; `open_tasks` per assignee summed across projects
10-- double-counts those tasks.
11
12SELECT
13    t.assignee_id,
14    u.user_name                                              AS assignee_name,
15    u.email                                                  AS assignee_email,
16    pt.project_id,
17    p.project_name,
18    COUNT(*)                                                 AS open_tasks,
19    COUNTIF(t.parent_task_id IS NOT NULL)                    AS open_subtasks,
20    COUNTIF(IF(t.due_at IS NOT NULL, t.due_at < CURRENT_TIMESTAMP(), t.due_on < CURRENT_DATE()))
21                                                             AS overdue_tasks,
22    COUNTIF(t.due_on BETWEEN CURRENT_DATE() AND DATE_ADD(CURRENT_DATE(), INTERVAL 6 DAY)) AS due_next_7_days,
23    COUNTIF(t.due_on IS NULL)                                AS without_due_date,
24    MIN(t.due_on)                                            AS earliest_due_on,
25    MAX(IF(t.due_on < CURRENT_DATE(), DATE_DIFF(CURRENT_DATE(), t.due_on, DAY), NULL)) AS max_days_overdue
26FROM {{staging.asana.task}} t
27LEFT JOIN {{staging.asana.project_task}} pt USING (task_id)
28LEFT JOIN {{staging.asana.project}} p ON p.project_id = pt.project_id
29LEFT JOIN {{staging.asana.user}} u ON u.user_id = t.assignee_id
30WHERE NOT t.is_completed
31  AND COALESCE(p.is_archived, FALSE) = FALSE
32GROUP BY 1, 2, 3, 4, 5