Task completion

1-- core.asana.task_completion
2-- Tasks completed per day, project and assignee, with on-time rate and time to
3-- complete.
4-- Grain: completion date (UTC) x project x assignee.
5--
6-- A task in two projects counts once in each project, so summing across projects
7-- double-counts multi-homed tasks. Tasks in no project land under project_id NULL.
8-- Subtasks are counted like any task; `subtasks_completed` shows how many of the
9-- total they are.
10--
11-- On time means done by the due time when the task has one (due_at), and by the
12-- end of the due date otherwise. The date comparison uses the UTC completion
13-- date, so a task closed late in the evening west of UTC can read one day late.
14--
15-- Asana's task stream has no created_at. `days_to_complete` measures from the
16-- task's earliest story (its "created this task" event in practice) to
17-- completion, and only for tasks whose stories are synced. Average it as
18-- SUM(days_to_complete_total) / SUM(tasks_with_known_start), never as an average
19-- of averages.
20
21WITH first_story AS (
22    SELECT task_id, MIN(created_at) AS first_story_at
23    FROM {{staging.asana.story}}
24    GROUP BY 1
25),
26
27completed AS (
28    SELECT
29        t.task_id,
30        t.assignee_id,
31        t.parent_task_id IS NOT NULL                    AS is_subtask,
32        DATE(t.completed_at)                            AS completed_date,
33        t.due_on,
34        CASE
35            WHEN t.due_at IS NOT NULL THEN t.completed_at > t.due_at
36            WHEN t.due_on IS NOT NULL THEN DATE(t.completed_at) > t.due_on
37        END                                             AS is_late,
38        TIMESTAMP_DIFF(t.completed_at, f.first_story_at, HOUR) / 24 AS days_to_complete
39    FROM {{staging.asana.task}} t
40    LEFT JOIN first_story f USING (task_id)