WITH NAMED_QUERY
  AS
  (
    SELECT  SUBJECT_AREA, WORKFLOW_NAME, INSTANCE_NAME, ELAPSED, MED, EXPECTED_HIGH, EXPECTED_LOW,
        CASE WHEN ELAPSED > EXPECTED_HIGH THEN 'H'
             WHEN ELAPSED < EXPECTED_LOW THEN 'L'
             ELSE NULL
        END VARIANCE,
             RUN_DAY, MAX_RUN, HISTORY
    FROM
    (
        SELECT  B.*,
         LTRIM(SYS_CONNECT_BY_PATH(ELAPSED, ','),',') HISTORY,
         CEIL(MED*(CASE WHEN MED < 5 THEN 4
                 WHEN MED < 10 THEN 3
                 WHEN MED < 20 THEN 1.5
                 ELSE 1.3 END)) EXPECTED_HIGH,
         CEIL(MED*(CASE WHEN MED < 5 THEN 0.20
                 WHEN MED < 10 THEN 0.30
                 WHEN MED < 20 THEN 0.60
                 ELSE 0.70 END)) EXPECTED_LOW
         FROM
         (
            SELECT  SUBJECT_AREA, WORKFLOW_NAME, INSTANCE_NAME, START_TIME, END_TIME, ELAPSED,
             CEIL(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY CASE WHEN RUN_DAY > latest_runs THEN ELAPSED ELSE NULL END) OVER(PARTITION BY SUBJECT_AREA, WORKFLOW_NAME, INSTANCE_NAME)) MED,
             RUN_DAY, MAX_RUN
             FROM
             (
                 SELECT A.SUBJECT_AREA, A.WORKFLOW_NAME, A.INSTANCE_NAME, START_TIME, END_TIME,
                  CEIL((END_TIME-START_TIME)*24*60) ELAPSED,
                  ROW_NUMBER() OVER (PARTITION BY A.SUBJECT_AREA, A.WORKFLOW_NAME, A.INSTANCE_NAME ORDER BY START_TIME DESC) "RUN_DAY",
                  LEAST(COUNT(1) OVER (PARTITION BY A.SUBJECT_AREA, A.WORKFLOW_NAME, A.INSTANCE_NAME), total_runs) MAX_RUN,
                  MAX(START_TIME) OVER (PARTITION BY A.SUBJECT_AREA, A.WORKFLOW_NAME, A.INSTANCE_NAME) "LAST_RUN"
                  FROM DATA_PROFILER.INFAWFLOWTASKRUNS A
                  WHERE TASK_TYPE IN (58, 68) AND RUN_ERR_CODE = 0 AND RUN_STATUS_CODE= 1
            )
            WHERE RUN_DAY <= total_runs
            AND LAST_RUN>=TRUNC(start_dt)
            AND MAX_RUN >=  min_runs
        ) B
        START WITH RUN_DAY = latest_runs
        CONNECT BY
        SUBJECT_AREA =  PRIOR SUBJECT_AREA AND
        WORKFLOW_NAME = PRIOR WORKFLOW_NAME AND
        INSTANCE_NAME = PRIOR INSTANCE_NAME AND
        RUN_DAY+1 = PRIOR RUN_DAY
    )
)
SELECT SUBJECT_AREA sa, WORKFLOW_NAME wf, INSTANCE_NAME sn, MED et, VARIANCE va, HISTORY lr
FROM NAMED_QUERY A
WHERE
A.RUN_DAY = 1
AND
(
A.EXPECTED_HIGH < ALL (SELECT B.ELAPSED FROM NAMED_QUERY B
                         WHERE A.SUBJECT_AREA = B.SUBJECT_AREA
                         AND A.WORKFLOW_NAME = B.WORKFLOW_NAME AND A.INSTANCE_NAME = B.INSTANCE_NAME AND B.RUN_DAY <= latest_runs
            )
OR
A.EXPECTED_LOW > ALL (SELECT B.ELAPSED FROM NAMED_QUERY B
                         WHERE A.SUBJECT_AREA = B.SUBJECT_AREA
                         AND A.WORKFLOW_NAME = B.WORKFLOW_NAME AND A.INSTANCE_NAME = B.INSTANCE_NAME AND B.RUN_DAY <= latest_runs
                     )
)
ORDER BY va, sa, wf, sn;