~~~BEGIN_MAIN_SECTION~~~
[PRODUCT]
view=PEM_SDA_MACS_DEVELOP_CHURN_AND_P_AND_E_GITLAB_VW
~~~END_MAIN_SECTION~~~


CREATE OR REPLACE VIEW "OTHQ_OWN"."PEM_SDA_MACS_DEVELOP_CHURN_AND_P_AND_E_GITLAB_VW" AS
(select /*A1.MERGE_REQUEST_IID,*/ A1.MERGE_COMMIT_ID, A1.FILE_PATH, A1.TIMESTAMP, A2.* from OTHQ_OWN.SRC_PEM_MACS_DEVELOP_CODE_CHURN_APPLICATION_TB A1 left join 
(
SELECT
    M1.PROJECT_ID,
    M1.ASSIGNEE,
    M1.AUTHOR,
    M1.BRANCH,
    M1.CODE_REVIEW_TIME,
    M1.CREATED_AT,
    M1.DESCRIPTION,
    M1.DOWNLOAD_TIMESTAMP,
    M1.INTEGRATION_TIME,
    Round(M1.INTEGRATION_TIME/24, 2) INTEGRATION_TIME_DAYS,
    M1.JIRA,
    M1.LAST_COMMIT_DATE,
    M1.MERGE_COMMIT_SHA,
    M1.MERGE_REQUEST_ID,
    M1.MERGE_REQUEST_IID,
    M1.MERGE_USER,
    M1.MERGED_AT,
    ROUND((M1.MERGED_AT - M1.CREATED_AT)*24)"MR Duration in Hours",
    M1.NLCORP,
    M1.SQUASH_ON_MERGE,
    M1.STATE,
    M1.TITLE,
    M1.WEB_URL,
    M1.WORKFLOW,
    M1.PRECOMMIT_DURATION,
    SUM(P1.DURATION) AS CALCULATED_DURATION
FROM OTHQ_OWN.SRC_CSV_GITLAB_SDLC_MERGE_REQUESTS_TB M1
LEFT JOIN (
    SELECT p.*
    FROM OTHQ_OWN.SRC_CSV_GITLAB_SDLC_PRECOMMITS_TB p
    JOIN (
        SELECT PRECOMMIT_ID, MAX(DOWNLOAD_TIMESTAMP) AS MAX_TS
        FROM OTHQ_OWN.SRC_CSV_GITLAB_SDLC_PRECOMMITS_TB
        GROUP BY PRECOMMIT_ID
    ) mx
      ON mx.PRECOMMIT_ID = p.PRECOMMIT_ID
     AND mx.MAX_TS = p.DOWNLOAD_TIMESTAMP
) P1
    ON M1.MERGE_REQUEST_ID = P1.MERGE_REQUEST_ID
WHERE M1.DOWNLOAD_TIMESTAMP = (
    SELECT MAX(M2.DOWNLOAD_TIMESTAMP)
    FROM OTHQ_OWN.SRC_CSV_GITLAB_SDLC_MERGE_REQUESTS_TB M2
    WHERE M1.MERGE_REQUEST_ID = M2.MERGE_REQUEST_ID
)
GROUP BY
    M1.PROJECT_ID,
    M1.ASSIGNEE,
    M1.AUTHOR,
    M1.BRANCH,
    M1.CODE_REVIEW_TIME,
    M1.CREATED_AT,
    M1.DESCRIPTION,
    M1.DOWNLOAD_TIMESTAMP,
    M1.INTEGRATION_TIME,
    M1.JIRA,
    M1.LAST_COMMIT_DATE,
    M1.MERGE_COMMIT_SHA,
    M1.MERGE_REQUEST_ID,
    M1.MERGE_REQUEST_IID,
    M1.MERGE_USER,
    M1.MERGED_AT,
    M1.NLCORP,
    M1.SQUASH_ON_MERGE,
    M1.STATE,
    M1.TITLE,
    M1.WEB_URL,
    M1.WORKFLOW,
    M1.PRECOMMIT_DURATION
)A2
on A2.MERGE_REQUEST_IID = A1.MERGE_REQUEST_IID and A2.MERGE_COMMIT_SHA = A1.MERGE_COMMIT_ID)