<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Analytics>
    <CronExpression> 0 0 12 1/7 * ? * </CronExpression>
    <Editable>true</Editable>
    <Name>APIMAnalytics-ConfigureAccessToken-ConfigureAccessToken-batch1</Name>
    <Script>create temporary table accessTokenRefreshTime using CarbonAnalytics options (tableName "ORG_WSO2_ANALYTICS_APIM_ACCESSTOKENREFRESHTIMEDIFFERENCE", schema "userId STRING, clientId STRING, scopes STRING, timeDifference LONG, timestamp LONG");

create temporary table accessTokenRefreshAvrgTime using CarbonAnalytics options (tableName "ORG_WSO2_ANALYTICS_APIM_ACCESSTOKENREFRESHSUMMARYTABLE", schema "userId STRING -i, clientId STRING -i, scopes STRING -i, minTimeDifference DOUBLE -i, maxTimeDifference DOUBLE -i", primaryKeys "userId, clientId, scopes");
      
INSERT INTO TABLE accessTokenRefreshAvrgTime
      SELECT temp.userId, temp.clientId, temp.scopes, getpercentileValue(cast(AVG(timeDifference) as double), cast(SQRT(AVG(timeDifference*timeDifference) - AVG(timeDifference)*AVG(timeDifference)) as double), cast(0.10 as double)) as minTimeDifference, getpercentileValue(cast(AVG(timeDifference) as double), cast(SQRT(AVG(timeDifference*timeDifference) - AVG(timeDifference)*AVG(timeDifference)) as double), cast(0.95 as double)) as maxTimeDifference
      FROM
         (SELECT userId, clientId,scopes, timeDifference
          FROM accessTokenRefreshTime
            WHERE timestamp >= offsetInDays(-7)) temp
      GROUP BY userId, clientId, scopes;  
    </Script>
</Analytics>
