SELECT build.taskType as taskType, build.percent as build, start.percent as start, build.type as type
FROM (
  SELECT scripts.taskType as taskType, scripts.percent as percent, types.group as type
  FROM (
      SELECT taskType, (count(distinct projectID) / 65794 * 100) as percent
      FROM
          (SELECT *, 'nodemon'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnodemon\b)')),
          (SELECT *, 'pm2'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bpm2\b)')),
          (SELECT *, 'istanbul'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bistanbul\b)')),
          (SELECT *, 'jsdoc'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjsdoc\b)')),
          (SELECT *, 'documentation'       as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bdocumentation\b)')),
          (SELECT *, 'make'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmake\b)')),
          (SELECT *, 'semistandard'        as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsemistandard\b)')),
          (SELECT *, 'eslint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\beslint\b)')),
          (SELECT *, 'standard'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bstandard\b)')),
          (SELECT *, 'tslint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btslint\b)')),
          (SELECT *, 'jshint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjshint\b)')),
          (SELECT *, 'xo'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bxo\b)')),
          (SELECT *, 'jasmine'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjasmine\b)')),
          (SELECT *, 'jest'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjest\b)')),
          (SELECT *, 'ava'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bava\b)')),
          (SELECT *, '.sh'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'\.sh')),
          (SELECT *, 'mocha'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmocha\b)')),
          (SELECT *, 'cake'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcake\b)')),
          (SELECT *, 'babel'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbabel\b)')),
          (SELECT *, 'rollup'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\brollup\b)')),
          (SELECT *, 'bower'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbower\b)')),
          (SELECT *, 'ionic'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bionic\b)')),
          (SELECT *, 'next'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnext\b)')),
          (SELECT *, 'grunt'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bgrunt\b)')),
          (SELECT *, 'gulp'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bgulp\b)')),
          (SELECT *, 'webpack'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bwebpack\b)')),
          (SELECT *, 'browserify'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbrowserify\b)')),
          (SELECT *, 'ng'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bng\b)')),
          (SELECT *, 'tsc'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btsc\b)')),
          (SELECT *, 'react-scripts'       as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\breact-scripts\b)')),
          (SELECT *, 'react-native'        as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\breact-native\b)')),
          (SELECT *, 'ember'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bember\b)')),
          (SELECT *, 'electron'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\belectron\b)')),
          (SELECT *, 'meteor'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmeteor\b)')),
          (SELECT *, 'karma'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bkarma\b)')),
          (SELECT *, 'protractor'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bprotractor\b)')),
          (SELECT *, 'nyc'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnyc\b)')),
          (SELECT *, 'coveralls'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcoveralls\b)')),
          (SELECT *, 'lcovonly'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blcovonly\b)')),
          (SELECT *, 'typings'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btypings\b)')),
          (SELECT *, 'jscs'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjscs\b)')),
          (SELECT *, 'less'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bless\b)')),
          (SELECT *, 'sass'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsass\b)')),
          (SELECT *, 'tap'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btap\b)')),
          (SELECT *, 'semantic-release'    as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsemantic-release\b)')),
          (SELECT *, 'docker'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bdocker\b)')),
          (SELECT *, 'coffee'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcoffee\b)')),
          (SELECT *, 'babelify'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbabelify\b)')),
          (SELECT *, 'lite-server'         as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blite-server\b)')),
          (SELECT *, 'flow'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bflow\b)')),
          (SELECT *, 'nodeunit'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnodeunit\b)')),
          (SELECT *, 'lab'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blab\b)')),
          (SELECT *, 'lerna'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blerna\b)')),
          (SELECT *, 'stylelint'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bstylelint\b)')),
          (SELECT *, 'travis'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btravis\b)')),
          (SELECT *, 'compodoc'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcompodoc\b)')),
          (SELECT *, 'rm'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\brm\b)')),
          (SELECT *, 'cross-env'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcross-env\b)')),
          (SELECT *, 'find'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bfind\b)')),
          (SELECT *, 'shjs'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bshjs\b)')),
          (SELECT *, 'nightwatch'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnightwatch\b)')),
          (SELECT *, 'source-map-explorer' as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsource-map-explorer\b)')),
          (SELECT *, 'webpack-dev-server'  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bwebpack-dev-server\b)')),
      where taskName = 'build'
      group by taskType
      ) as scripts
  JOIN [types] as types
  ON scripts.taskType = types.taskType
  ORDER BY percent DESC
) as build
JOIN (SELECT taskType, (count(distinct projectID) / 107646 * 100) as percent
      FROM
          (SELECT *, 'nodemon'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnodemon\b)')),
          (SELECT *, 'pm2'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bpm2\b)')),
          (SELECT *, 'istanbul'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bistanbul\b)')),
          (SELECT *, 'jsdoc'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjsdoc\b)')),
          (SELECT *, 'documentation'       as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bdocumentation\b)')),
          (SELECT *, 'make'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmake\b)')),
          (SELECT *, 'semistandard'        as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsemistandard\b)')),
          (SELECT *, 'eslint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\beslint\b)')),
          (SELECT *, 'standard'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bstandard\b)')),
          (SELECT *, 'tslint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btslint\b)')),
          (SELECT *, 'jshint'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjshint\b)')),
          (SELECT *, 'xo'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bxo\b)')),
          (SELECT *, 'jasmine'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjasmine\b)')),
          (SELECT *, 'jest'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjest\b)')),
          (SELECT *, 'ava'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bava\b)')),
          (SELECT *, '.sh'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'\.sh')),
          (SELECT *, 'mocha'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmocha\b)')),
          (SELECT *, 'cake'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcake\b)')),
          (SELECT *, 'babel'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbabel\b)')),
          (SELECT *, 'rollup'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\brollup\b)')),
          (SELECT *, 'bower'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbower\b)')),
          (SELECT *, 'ionic'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bionic\b)')),
          (SELECT *, 'next'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnext\b)')),
          (SELECT *, 'grunt'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bgrunt\b)')),
          (SELECT *, 'gulp'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bgulp\b)')),
          (SELECT *, 'webpack'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bwebpack\b)')),
          (SELECT *, 'browserify'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbrowserify\b)')),
          (SELECT *, 'ng'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bng\b)')),
          (SELECT *, 'tsc'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btsc\b)')),
          (SELECT *, 'react-scripts'       as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\breact-scripts\b)')),
          (SELECT *, 'react-native'        as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\breact-native\b)')),
          (SELECT *, 'ember'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bember\b)')),
          (SELECT *, 'electron'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\belectron\b)')),
          (SELECT *, 'meteor'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bmeteor\b)')),
          (SELECT *, 'karma'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bkarma\b)')),
          (SELECT *, 'protractor'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bprotractor\b)')),
          (SELECT *, 'nyc'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnyc\b)')),
          (SELECT *, 'coveralls'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcoveralls\b)')),
          (SELECT *, 'lcovonly'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blcovonly\b)')),
          (SELECT *, 'typings'             as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btypings\b)')),
          (SELECT *, 'jscs'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bjscs\b)')),
          (SELECT *, 'less'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bless\b)')),
          (SELECT *, 'sass'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsass\b)')),
          (SELECT *, 'tap'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btap\b)')),
          (SELECT *, 'semantic-release'    as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsemantic-release\b)')),
          (SELECT *, 'docker'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bdocker\b)')),
          (SELECT *, 'coffee'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcoffee\b)')),
          (SELECT *, 'babelify'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bbabelify\b)')),
          (SELECT *, 'lite-server'         as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blite-server\b)')),
          (SELECT *, 'flow'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bflow\b)')),
          (SELECT *, 'nodeunit'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnodeunit\b)')),
          (SELECT *, 'lab'                 as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blab\b)')),
          (SELECT *, 'lerna'               as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\blerna\b)')),
          (SELECT *, 'stylelint'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bstylelint\b)')),
          (SELECT *, 'travis'              as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\btravis\b)')),
          (SELECT *, 'compodoc'            as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcompodoc\b)')),
          (SELECT *, 'rm'                  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\brm\b)')),
          (SELECT *, 'cross-env'           as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bcross-env\b)')),
          (SELECT *, 'find'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bfind\b)')),
          (SELECT *, 'shjs'                as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bshjs\b)')),
          (SELECT *, 'nightwatch'          as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bnightwatch\b)')),
          (SELECT *, 'source-map-explorer' as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bsource-map-explorer\b)')),
          (SELECT *, 'webpack-dev-server'  as taskType FROM [scripts] where REGEXP_MATCH(task, r'(\bwebpack-dev-server\b)')),
      where taskName = 'start'
      group by taskType
      ) as start
  ON start.taskType = build.taskType

