# JIRA BUGS
- table comes from jira_issues_agg_daily

WITH
  jira_issue_agg_task AS (
  SELECT
    *
  FROM
    `crewmeister-projects.jira_corrected.jira_issues_agg_task`
    where project_id = 10006 #CREW project only
    ),
  /* compute the increase and decrease in jira objects based on the creation date
    and resolution date respectively
  */ objects_inc_daily AS (
  SELECT
    DATE(created_time) AS date,
    COUNTIF(issuetype_id=10000) AS epics_inc,
    COUNTIF(issuetype_id=10001) AS stories_inc,
    COUNTIF(issuetype_id=10002) AS tasks_inc,
    COUNTIF(issuetype_id=10003) AS subtasks_inc,
    COUNTIF(issuetype_id=10004) AS bugs_inc,
  FROM
    jira_issue_agg_task
    #where resolution_id not in (10001, 10002, 10003)
    #(resolution_id = 10000
    #  OR resolution_id = 0)  #count only "Done" resolutions and not the "Won't Do", "Cannot Reproduce",...
    #  AND status_id <> -1 #exclude the ones that are excluded in the Jira Exception Sheet (manually entered exceptions)
  GROUP BY
    DATE(created_time)),
  objects_dec_daily AS (
  SELECT
    DATE(resolution_time) AS date,
    COUNTIF(issuetype_id=10000
      AND status_id=10002) AS epics_dec,
    COUNTIF(issuetype_id=10001
      AND status_id=10002) AS stories_dec,
    COUNTIF(issuetype_id=10002
      AND status_id=10002) AS tasks_dec,
    COUNTIF(issuetype_id=10003) AS subtasks_dec,
    COUNTIF(issuetype_id=10004
      AND (status_id=10002 or status_id=-1)) AS bugs_dec,
  FROM
    jira_issue_agg_task
  GROUP BY
    DATE(resolution_time)),
  /* combine both inc and dec in jira objects daily.
    Then compute the difference and the number of bugs open at date.
  */ issues_agg_daily_with_inc_dec AS (
  SELECT
    date,
    ifnull(objects_inc_daily.epics_inc,
      0) AS epics_inc,
    ifnull(objects_inc_daily.stories_inc,
      0) AS stories_inc,
    ifnull(objects_inc_daily.tasks_inc,
      0) AS tasks_inc,
    ifnull(objects_inc_daily.subtasks_inc,
      0) AS subtasks_inc,
    ifnull(objects_inc_daily.bugs_inc,
      0) AS bugs_inc,
    ifnull(objects_dec_daily.epics_dec,
      0) AS epics_dec,
    ifnull(objects_dec_daily.stories_dec,
      0) AS stories_dec,
    ifnull(objects_dec_daily.tasks_dec,
      0) AS tasks_dec,
    ifnull(objects_dec_daily.subtasks_dec,
      0) AS subtasks_dec,
    ifnull(objects_dec_daily.bugs_dec,
      0) AS bugs_dec,
  FROM
    UNNEST(GENERATE_DATE_ARRAY(DATE("2020-03-01"), DATE_ADD(current_date, INTERVAL 367 day), INTERVAL 1 DAY)) AS date
  LEFT JOIN
    objects_inc_daily
  ON
    DATE(objects_inc_daily.date) = date
  LEFT JOIN
    objects_dec_daily
  ON
    DATE(objects_dec_daily.date) = date),
  issues_agg_daily_with_inc_dec_diff_open AS (
  SELECT
    *,
    epics_inc-epics_dec AS epics_diff,
    stories_inc-stories_dec AS stories_diff,
    tasks_inc-tasks_dec AS tasks_diff,
    subtasks_inc-subtasks_dec AS subtasks_diff,
    bugs_inc-bugs_dec AS bugs_diff,
    SUM(epics_inc-epics_dec) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS epics_open,
    SUM(stories_inc-stories_dec) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS stories_open,
    SUM(tasks_inc-tasks_dec) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS tasks_open,
    SUM(subtasks_inc-subtasks_dec) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS subtasks_open,
    SUM(bugs_inc-bugs_dec) OVER(ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS bugs_open,
  FROM
    issues_agg_daily_with_inc_dec)
SELECT
  *
FROM
  issues_agg_daily_with_inc_dec_diff_open

WITH
  /* select the corrected versions of jira_issues and jira_issues_history 
with manual exceptions in the respective google sheets
*/ jira_issues AS (
  SELECT
    *
  FROM
    `crewmeister-projects.jira_corrected.jira_issues_corrected`
    where project_id=10006),
    
    
  jira_issues_history AS (
  SELECT
    *
  FROM
    `crewmeister-projects.jira_corrected.jira_issues_history_corrected`
    where issue_key like "%CREW%"),
  /* select the relevant fields for efficient computations */ jira_issues_selection AS (
  SELECT
    issue_id,
    issue_key,
    issuetype_id,
    issuetype_name,
    priority_id,
    priority_name,
    project_id,
    project_key,
    created_time,
    resolution_id,
    resolution_name,
    resolution_time,
    status_id,
    status_name,
    story_points,
    #parents informations
    parent_issue_id,
    parent_issue_key,
    parent_issuetype_id,
    parent_issuetype_name,
    issue_summary,
  FROM
    jira_issues),
    
  /* unnest the jira_issues array information information for each issue. 
    We cannot unnest every field in a single query since the 
    unnesting is at different nest-precision levels */ jira_issues_sprint_name AS (
  SELECT
    issue_id,
    issue_key,
    ARRAY_AGG(sprints.item.id) AS sprint_id,
    ARRAY_AGG(sprints.item.name) AS sprint_name,
  FROM
    jira_issues,
    UNNEST(sprint.list) AS sprints
  GROUP BY
    issue_id,
    issue_key),
  jira_issues_label_name AS (
  SELECT
    issue_id,
    issue_key,
    ARRAY_AGG(labels.item) AS label_name,
  FROM
    jira_issues,
    UNNEST(fields_labels.list) AS labels
  GROUP BY
    issue_id,
    issue_key),
  jira_issues_fixVersions AS (
  SELECT
    issue_id,
    issue_key,
    ARRAY_AGG(fixVersions.item.id) AS fixVersions_id,
    ARRAY_AGG(fixVersions.item.name) AS fixVersions_name,
  FROM
    jira_issues,
    UNNEST(fields_fixVersions.list) AS fixVersions
  GROUP BY
    issue_id,
    issue_key),
  jira_issues_selection_with_array_data AS (
  SELECT
    jira_issues_selection.*,
    jira_issues_sprint_name.sprint_id,
    jira_issues_sprint_name.sprint_name,
    jira_issues_label_name.label_name,
    jira_issues_fixVersions.fixVersions_name,
    jira_issues_fixVersions.fixVersions_id,
  FROM
    jira_issues_selection
  LEFT JOIN
    jira_issues_sprint_name
  ON
    jira_issues_selection.issue_id = jira_issues_sprint_name.issue_id
  LEFT JOIN
    jira_issues_label_name
  ON
    jira_issues_selection.issue_id = jira_issues_label_name.issue_id
  LEFT JOIN
    jira_issues_fixVersions
  ON
    jira_issues_selection.issue_id = jira_issues_fixVersions.issue_id),
  /* select relevant fields from jira_issues_history 
    for efficient compuations */ jira_issues_history_selection AS (
  SELECT
    DISTINCT issue_id,
    issue_key,
    history_id,
    created_time,
    field,
    field_id,
    from_id,
    from_string,
    to_id,
    to_string,
  FROM
    jira_issues_history),
  /* compute the implementation date for each issue 
    (when the respective first sub-level element is moved to implementation at least once).*/ jira_issues_history_implementation_time AS (
  SELECT
    DISTINCT jira_issues_history_selection.issue_id,
    jira_issues_history_selection.issue_key,
    MIN(
    IF
      (to_id=10016,
        jira_issues_history_selection.created_time,
        NULL)) OVER(PARTITION BY jira_issues_history_selection.issue_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS implementation_time,
    jira_issues_selection.parent_issue_id,
    jira_issues_selection.parent_issue_key,
    MIN(
    IF
      (to_id=10016,
        jira_issues_history_selection.created_time,
        NULL)) OVER(PARTITION BY jira_issues_selection.parent_issue_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS parent_implementation_time,
  FROM
    jira_issues_selection
  LEFT JOIN
    jira_issues_history_selection
  ON
    jira_issues_selection.issue_id = jira_issues_history_selection.issue_id),
  /* add the implementation date and time metrics to the jira_issues_selection data*/ jira_issues_selection_with_array_data_and_implementation_time AS (
  SELECT
    jira_issues_selection_with_array_data.*,
    ifnull(child_implementation_time_table.implementation_time,
      ifnull(parent_implementation_time_table.parent_implementation_time,
        current_timestamp)) AS implementation_time,
  FROM
    jira_issues_selection_with_array_data
  LEFT JOIN
    jira_issues_history_implementation_time child_implementation_time_table
  ON
    jira_issues_selection_with_array_data.issue_id = child_implementation_time_table.issue_id
  LEFT JOIN (
    SELECT
      DISTINCT parent_issue_id,
      parent_implementation_time
    FROM
      jira_issues_history_implementation_time) parent_implementation_time_table
  ON
    jira_issues_selection_with_array_data.issue_id = parent_implementation_time_table.parent_issue_id),
  /* compute time columns and then correct them for the blocker-bug exception case */ jira_issues_selection_times_uncorrected AS (
  SELECT
    *,
    TIMESTAMP_DIFF(implementation_time, created_time, second) AS waiting_time,
    TIMESTAMP_DIFF(resolution_time, implementation_time, second) AS lead_time,
    TIMESTAMP_DIFF(resolution_time, created_time, second) AS life_time,
  FROM
    jira_issues_selection_with_array_data_and_implementation_time),
  jira_issues_selection_times AS (
  SELECT
    *EXCEPT(waiting_time,
      lead_time,
      life_time),
    CASE
      WHEN priority_id = 1 THEN 0 #priority_id= "Blocker"
      WHEN waiting_time < 0 THEN 0
    ELSE
    waiting_time
  END
    AS waiting_time,
    CASE
      WHEN priority_id = 1 THEN life_time #priority_id= "Blocker"
      WHEN lead_time < 0 THEN 0
    ELSE
    lead_time
  END
    AS lead_time,
    CASE
      WHEN life_time < 0 THEN 0
    ELSE
    life_time
  END
    AS life_time,
  FROM
    jira_issues_selection_times_uncorrected)
SELECT
  *
FROM
  jira_issues_selection_times