/dev-vs-start.sql
Created Sep 28, 2017
| SELECT dev.taskType as taskType, dev.percent as dev, start.percent as start, dev.type as type | |
| FROM ( | |
| SELECT scripts.taskType as taskType, scripts.percent as percent, types.group as type | |
| FROM ( | |
| SELECT taskType, (count(distinct projectID) / 15868 * 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 = 'dev' | |
| group by taskType | |
| ) as scripts | |
| JOIN [types] as types | |
| ON scripts.taskType = types.taskType | |
| ORDER BY percent DESC | |
| ) as dev | |
| 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 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment