Script Query Examples

javascript
deployFailuresLast6Months = await sql`SELECT date_trunc('month', datetime) as month, COUNT(*) FROM custom_data_points
    where datetime > date_trunc('month', current_date - interval '6' month)
    AND tag='deploy_failure'
    GROUP BY month;`
    
deploysLast6Months = await sql`SELECT date_trunc('month', external_merged_at) as month, COUNT(*) FROM pull_requests
    where external_merged_at > date_trunc('month', current_date - interval '6' month)
    AND base_branch_name='deploy'
    GROUP BY month;
    `

changeFailureRaterArray = [];

for(deploysForMonth of deploysLast6Months) {
    deployFailuresForMonth = deployFailuresLast6Months.find((failuresPerMonth) => {
        return JSON.stringify(deploysForMonth['month']) === JSON.stringify(failuresPerMonth['month'])
    });
    
    deployFailuresForMonth = deployFailuresForMonth ? deployFailuresForMonth['count'] : 0;
    
    console.log('deploysForMonth', deploysForMonth['count'])
    console.log('deployFailuresForMonth', deployFailuresForMonth)
    
    changeFailureRate = deployFailuresForMonth / deploysForMonth['count']
    
    // 0.02 is 2%
    changeFailureRaterArray.push({
        month: deploysForMonth['month'],
        changeFailureRate: Math.round(changeFailureRate * 100) / 100
    })
}

return changeFailureRaterArray

javascript
begin_date = {begin_date}
end_date = {end_date}
time_period_group_by_id = {time_period_group_by_id}

should_include_date_filter = begin_date && end_date
date_filter = sql`AND external_merged_at > ${begin_date} AND external_merged_at <= ${end_date}`

deployFailuresOverTime = await sql`
    SELECT
        DATE_TRUNC(${time_period_group_by_id}, external_merged_at) AS period,
        COUNT(*) AS number_of_failed_deploys
    FROM
        pull_requests
    WHERE
        external_merged_at IS NOT NULL
        AND (
    		body ILIKE '%revert%' OR
            title ILIKE '%revert%' OR
            body ILIKE '%rollback%' OR
            title ILIKE '%rollback%' OR
            body ILIKE '%roll back%' OR
            title ILIKE '%roll back%' OR
            body ILIKE '%fix deploy%' OR
            title ILIKE '%fix deploy%' OR
            body ILIKE '%hot fix%'  OR  
            title ILIKE '%hot fix%' OR
            body ILIKE '%fix prod%' OR
            title ILIKE '%fix prod%' OR
            body ILIKE '%fixes prod%' OR
            title ILIKE '%fixes prod%'
        )
        AND base_branch_name='master'
        ${should_include_date_filter ? date_filter : sql``}
    GROUP BY
        period
    ORDER BY
        period
`
    
deploysOverTime = await sql`SELECT date_trunc(${time_period_group_by_id}, external_merged_at) as period, COUNT(*) FROM pull_requests
    where base_branch_name='master'
    ${should_include_date_filter ? date_filter : sql``}
    GROUP BY period;`

changeFailureRaterArray = [];

for(deploysForPeriod of deploysOverTime) {
    deployFailuresForPeriod = deployFailuresOverTime.find((failuresPerPeriod) => {
        return JSON.stringify(deploysForPeriod['period']) === JSON.stringify(failuresPerPeriod['period'])
    });
    
    deployFailuresForPeriod = deployFailuresForPeriod ? deployFailuresForPeriod['number_of_failed_deploys'] : 0;
    

    changeFailureRate = deployFailuresForPeriod / deploysForPeriod['count']
    
    // console.log('Month', deploysForMonth['period'])
    // console.log('deployFailuresForMonth', deployFailuresForMonth)
    // console.log('deploysForMonth', deploysForMonth['count'])
    
    // 0.02 is 2%
    changeFailureRaterArray.push({
        period: deploysForPeriod['period'],
        changeFailureRate: Math.round(changeFailureRate * 100) / 100
    })
}

return changeFailureRaterArray

javascript
last6MonthsOutagesAndRestores = await sql`
    SELECT * from custom_data_points 
    where (tag='system_outage' OR tag='system_restored')
    AND datetime > date_trunc('month', current_date - interval '6' month)
    ORDER BY datetime ASC
`

last6MonthsOutagesAndRestoresNoRepeats = [];

for(i=0; i < last6MonthsOutagesAndRestores.length; i++) {
  currentValue = last6MonthsOutagesAndRestores[i]
  previousValue = last6MonthsOutagesAndRestores[i - 1]
  
  if (!previousValue || currentValue['tag'] != previousValue['tag']) {
    last6MonthsOutagesAndRestoresNoRepeats.push(currentValue)
  }
}

systemRestoresWithTimes = [];

for(i=0; i < last6MonthsOutagesAndRestoresNoRepeats.length; i++) {
  currentValue = last6MonthsOutagesAndRestoresNoRepeats[i]
  
  if (currentValue['tag'] === 'system_restored') {
    previousValue = last6MonthsOutagesAndRestoresNoRepeats[i - 1]

    if (previousValue) {
        restoredDate = new Date(currentValue['datetime'])
        outageDate = new Date(previousValue['datetime'])
        systemRestoresWithTimes.push({
            datetime: currentValue['datetime'],
            timeToRestoreMs: restoredDate - outageDate
        })
    }
  }
}

function getRestoresWithin(restores, startDate, endDate) {
    return restores.filter((restore) => {
        return new Date(restore['datetime']) >= startDate && new Date(restore['datetime']) < endDate;
    })
}

begginingOfMonth = new Date();
begginingOfMonth.setDate(1);
begginingOfMonth.setMonth(begginingOfMonth.getMonth() - 6);

restoreTimesByMonth = []

for (i = 0; i <= 6; i++) {
    begginingOfNextMonth = new Date(begginingOfMonth);
    begginingOfNextMonth.setMonth(begginingOfMonth.getMonth() + 1);
    begginingOfNextMonth.setDate(1);
    
    restoresForCurrentMonth = getRestoresWithin(
        systemRestoresWithTimes,
        begginingOfMonth,
        begginingOfNextMonth
    )
    
    restoreTimesByMonth.push({
      month: begginingOfMonth.toISOString(),
      restoresForCurrentMonth
    });
    
    begginingOfMonth.setMonth(begginingOfMonth.getMonth() + 1);
}

restoreTimeAverageByMonth = []

for(restoreTimesForSingleMonth of restoreTimesByMonth) {
    restoreTimes = restoreTimesForSingleMonth['restoresForCurrentMonth'].map((time) => time['timeToRestoreMs']);
    
    averageRestoreTimeInMs = restoreTimes.reduce((a, b) => a + b, 0) / restoreTimes.length;
    restoreTimeAverageByMonth.push({
        month: restoreTimesForSingleMonth['month'],
        averageRestoreTimeInMinutes: Math.round(averageRestoreTimeInMs / 1000.0 / 60.0)
    })
}

return restoreTimeAverageByMonth

javascript
allCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' GROUP BY Week`;
    
restartedCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' AND metadata ILIKE '%is-rerun-true%'GROUP BY Week`;

runData = [];

for(ciRunsForSingleWeek of allCiRunsByWeek) {
    numberOfRestartedCiRunsForCurrentWeek = restartedCiRunsByWeek.find((restartedCiRunsForSingleWeek) => {
        return JSON.stringify(restartedCiRunsForSingleWeek['week']) === JSON.stringify(ciRunsForSingleWeek['week'])
    });
    
    
    runData.push({
        week: ciRunsForSingleWeek['week'],
        total_runs: ciRunsForSingleWeek['count'],
        restarted_runs: numberOfRestartedCiRunsForCurrentWeek['count']
    })
}

return runData

javascript
allCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' GROUP BY Week`;
    
restartedCiRunsByWeek = await sql`SELECT date_trunc('week', datetime) as Week, COUNT(*) from custom_data_points where tag='ci_run' AND metadata ILIKE '%is-rerun-true%'GROUP BY Week`;

runData = [];

for(ciRunsForSingleWeek of allCiRunsByWeek) {
    numberOfRestartedCiRunsForCurrentWeek = restartedCiRunsByWeek.find((restartedCiRunsForSingleWeek) => {
        return JSON.stringify(restartedCiRunsForSingleWeek['week']) === JSON.stringify(ciRunsForSingleWeek['week'])
    });
    
    
    restartedRunsCount = numberOfRestartedCiRunsForCurrentWeek ? numberOfRestartedCiRunsForCurrentWeek['count'] : 0
    
    runData.push({
        week: ciRunsForSingleWeek['week'],
        restart_ratio: restartedRunsCount / ciRunsForSingleWeek['count'],
    })
}

return runData

javascript
begin_date = {begin_date}
end_date = {end_date}
team_id = {team_id}
repository_id = {repository_id}
contributor_parent_id = {contributor_parent_id}
time_period_group_by_id = {time_period_group_by_id}

should_include_date_filter = begin_date && end_date
date_filter = sql`AND authored_at > ${begin_date} AND authored_at <= ${end_date}`
should_include_team_filter = !!team_id
team_filter = sql`AND contributor_parent_teams.team_id = ${team_id}`
team_inner_join = sql`INNER JOIN contributor_parent_teams ON contributor_parents.id=contributor_parent_teams.contributor_parent_id`
should_include_repository_filter= !!repository_id
repository_filter = sql`AND repositories.id = ${repository_id}`
should_include_contributor_parent_filter= !!contributor_parent_id
contributor_parent_filter = sql`AND contributor_parents.id = ${contributor_parent_id}`

return sql`SELECT date_trunc(${time_period_group_by_id}, subquery.authored_at) as Period,
        ROUND(SUM(subquery.impact)) as Impact
        FROM (
            SELECT distinct(commits.id), impact, authored_at
            FROM commits
            INNER JOIN contributors ON commits.contributor_id=contributors.id
            INNER JOIN contributor_parents ON contributor_parents.id=contributors.contributor_parent_id
            ${should_include_team_filter ? team_inner_join : sql``}
            INNER JOIN repositories ON repositories.id=commits.repository_id
            where 1=1
            ${should_include_date_filter ? date_filter : sql``}
            ${should_include_team_filter ? team_filter : sql``}
            ${should_include_repository_filter ? repository_filter : sql``}
            ${should_include_contributor_parent_filter ? contributor_parent_filter : sql``}
        ) subquery
    GROUP BY Period`;

javascript
begin_date = {begin_date}
end_date = {end_date}
team_id = {team_id}
repository_id = {repository_id}
contributor_parent_id = {contributor_parent_id}
time_period_group_by_id = {time_period_group_by_id}

should_include_date_filter = begin_date && end_date
date_filter = sql`AND authored_at > ${begin_date} AND authored_at <= ${end_date}`
should_include_team_filter = !!team_id
team_filter = sql`AND contributor_parent_teams.team_id = ${team_id}`
team_inner_join = sql`INNER JOIN contributor_parent_teams ON contributor_parent_teams.contributor_parent_id=contributor_parents.id`
should_include_repository_filter= !!repository_id
repository_filter = sql`AND repositories.id = ${repository_id}`
should_include_contributor_parent_filter= !!contributor_parent_id
contributor_parent_filter = sql`AND contributor_parents.id = ${contributor_parent_id}`
    
return sql`
            SELECT distinct(commits.id), impact, authored_at
            FROM commits
            INNER JOIN contributors ON commits.contributor_id=contributors.id
            INNER JOIN contributor_parents ON contributor_parents.id=contributors.contributor_parent_id
            INNER JOIN repositories ON repositories.id=commits.repository_id
            ${should_include_team_filter ? team_inner_join : sql``}
            where 1=1
            AND impact IS NOT NULL
            ${should_include_date_filter ? date_filter : sql``}
            ${should_include_team_filter ? team_filter : sql``}
            ${should_include_repository_filter ? repository_filter : sql``}
            ${should_include_contributor_parent_filter ? contributor_parent_filter : sql``}
            ORDER BY impact DESC
        `;