Project overview

1-- core.asana.project_overview
2-- One row per project: status, owner, team, task progress and overdue work.
3-- Recreates the overview Asana shows on a portfolio, as of the last sync.
4-- Grain: project.
5--
6-- Task counts only include assigned tasks, because unassigned tasks are not
7-- synced (see staging.asana.task). `pct_complete` is therefore the share of
8-- assigned tasks that are done, which can differ from Asana's own progress bar.
9-- Overdue uses the due time when one is set and the due date otherwise, as in
10-- open_task_workload.
11
12WITH task_stats AS (
13    SELECT
14        pt.project_id,
15        COUNT(*)                                                    AS tasks,
16        COUNTIF(t.is_completed)                                     AS completed_tasks,
17        COUNTIF(NOT t.is_completed)                                 AS open_tasks,
18        COUNTIF(NOT t.is_completed
19                AND IF(t.due_at IS NOT NULL, t.due_at < CURRENT_TIMESTAMP(), t.due_on < CURRENT_DATE()))
20                                                                    AS overdue_tasks,
21        COUNT(DISTINCT IF(NOT t.is_completed, t.assignee_id, NULL)) AS assignees_with_open_tasks,
22        MAX(t.completed_at)                                         AS last_completed_at,
23        MAX(t.modified_at)                                          AS last_task_modified_at
24    FROM {{staging.asana.project_task}} pt
25    JOIN {{staging.asana.task}} t USING (task_id)
26    GROUP BY 1
27)
28
29SELECT
30    p.project_id,
31    p.project_name,
32    p.workspace_id,
33    p.team_id,
34    tm.team_name,
35    p.owner_id,
36    o.user_name                                   AS owner_name,
37    p.is_archived,
38    p.current_status_color,
39    p.created_at,
40    p.due_date,