Generates a query to use with BigQuery that gets the 25/50/75 percentiles of a metric over all desktop/mobile pages tables.

HA pages.js

/* Generated by:
SELECT
  SUBSTR(CONCAT('20', _TABLE_SUFFIX), 0, 10) AS date,
  CASE
    WHEN ENDS_WITH(_TABLE_SUFFIX, 'mobile') THEN 'mobile'
    ELSE 'desktop'
  END AS client
FROM
  `httparchive.runs.20*`
WHERE
  ENDS_WITH(_TABLE_SUFFIX, '_pages')
  OR ENDS_WITH(_TABLE_SUFFIX, '_pages_mobile')
GROUP BY
  1,
  2
ORDER BY
  1 DESC,
  2
*/
var pages = [{"date":"2017_05_15","client":"desktop"},
{"date":"2017_05_15","client":"mobile"},
{"date":"2017_05_01","client":"desktop"},
{"date":"2017_05_01","client":"mobile"},
{"date":"2017_04_15","client":"desktop"},
{"date":"2017_04_15","client":"mobile"},
{"date":"2017_04_01","client":"desktop"},
{"date":"2017_04_01","client":"mobile"},
{"date":"2017_03_15","client":"desktop"},
{"date":"2017_03_15","client":"mobile"},
{"date":"2017_03_01","client":"desktop"},
{"date":"2017_03_01","client":"mobile"},
{"date":"2017_02_15","client":"desktop"},
{"date":"2017_02_15","client":"mobile"},
{"date":"2017_02_01","client":"desktop"},
{"date":"2017_02_01","client":"mobile"},
{"date":"2017_01_15","client":"desktop"},
{"date":"2017_01_15","client":"mobile"},
{"date":"2017_01_01","client":"desktop"},
{"date":"2017_01_01","client":"mobile"},
{"date":"2016_12_15","client":"desktop"},
{"date":"2016_12_15","client":"mobile"},
{"date":"2016_11_15","client":"desktop"},
{"date":"2016_11_15","client":"mobile"},
{"date":"2016_11_01","client":"desktop"},
{"date":"2016_11_01","client":"mobile"},
{"date":"2016_10_15","client":"desktop"},
{"date":"2016_10_15","client":"mobile"},
{"date":"2016_10_01","client":"desktop"},
{"date":"2016_10_01","client":"mobile"},
{"date":"2016_09_15","client":"desktop"},
{"date":"2016_09_15","client":"mobile"},
{"date":"2016_09_01","client":"desktop"},
{"date":"2016_09_01","client":"mobile"},
{"date":"2016_08_15","client":"desktop"},
{"date":"2016_08_15","client":"mobile"},
{"date":"2016_08_01","client":"desktop"},
{"date":"2016_08_01","client":"mobile"},
{"date":"2016_07_15","client":"desktop"},
{"date":"2016_07_15","client":"mobile"},
{"date":"2016_07_01","client":"desktop"},
{"date":"2016_07_01","client":"mobile"},
{"date":"2016_06_15","client":"desktop"},
{"date":"2016_06_15","client":"mobile"},
{"date":"2016_06_01","client":"desktop"},
{"date":"2016_06_01","client":"mobile"},
{"date":"2016_05_15","client":"desktop"},
{"date":"2016_05_15","client":"mobile"},
{"date":"2016_05_01","client":"desktop"},
{"date":"2016_05_01","client":"mobile"},
{"date":"2016_04_15","client":"desktop"},
{"date":"2016_04_15","client":"mobile"},
{"date":"2016_04_01","client":"desktop"},
{"date":"2016_04_01","client":"mobile"},
{"date":"2016_03_15","client":"desktop"},
{"date":"2016_03_01","client":"desktop"},
{"date":"2016_03_01","client":"mobile"},
{"date":"2016_02_15","client":"desktop"},
{"date":"2016_02_15","client":"mobile"},
{"date":"2016_02_01","client":"desktop"},
{"date":"2016_02_01","client":"mobile"},
{"date":"2016_01_15","client":"desktop"},
{"date":"2016_01_15","client":"mobile"},
{"date":"2016_01_01","client":"desktop"},
{"date":"2016_01_01","client":"mobile"},
{"date":"2015_12_15","client":"desktop"},
{"date":"2015_12_15","client":"mobile"},
{"date":"2015_12_01","client":"desktop"},
{"date":"2015_12_01","client":"mobile"},
{"date":"2015_11_15","client":"desktop"},
{"date":"2015_11_15","client":"mobile"},
{"date":"2015_11_01","client":"desktop"},
{"date":"2015_11_01","client":"mobile"},
{"date":"2015_10_15","client":"desktop"},
{"date":"2015_10_15","client":"mobile"},
{"date":"2015_10_01","client":"desktop"},
{"date":"2015_10_01","client":"mobile"},
{"date":"2015_09_15","client":"desktop"},
{"date":"2015_09_15","client":"mobile"},
{"date":"2015_09_01","client":"desktop"},
{"date":"2015_09_01","client":"mobile"},
{"date":"2015_08_15","client":"desktop"},
{"date":"2015_08_15","client":"mobile"},
{"date":"2015_08_01","client":"desktop"},
{"date":"2015_08_01","client":"mobile"},
{"date":"2015_07_15","client":"desktop"},
{"date":"2015_07_15","client":"mobile"},
{"date":"2015_07_01","client":"desktop"},
{"date":"2015_07_01","client":"mobile"},
{"date":"2015_06_15","client":"desktop"},
{"date":"2015_06_15","client":"mobile"},
{"date":"2015_06_01","client":"desktop"},
{"date":"2015_06_01","client":"mobile"},
{"date":"2015_05_15","client":"desktop"},
{"date":"2015_05_15","client":"mobile"},
{"date":"2015_05_01","client":"desktop"},
{"date":"2015_05_01","client":"mobile"},
{"date":"2015_04_15","client":"desktop"},
{"date":"2015_04_15","client":"mobile"},
{"date":"2015_04_01","client":"desktop"},
{"date":"2015_04_01","client":"mobile"},
{"date":"2015_03_15","client":"desktop"},
{"date":"2015_03_15","client":"mobile"},
{"date":"2015_03_01","client":"desktop"},
{"date":"2015_03_01","client":"mobile"},
{"date":"2015_02_15","client":"desktop"},
{"date":"2015_02_15","client":"mobile"},
{"date":"2015_02_01","client":"desktop"},
{"date":"2015_02_01","client":"mobile"},
{"date":"2015_01_15","client":"desktop"},
{"date":"2015_01_15","client":"mobile"},
{"date":"2015_01_01","client":"desktop"},
{"date":"2015_01_01","client":"mobile"},
{"date":"2014_12_15","client":"desktop"},
{"date":"2014_12_15","client":"mobile"},
{"date":"2014_12_01","client":"desktop"},
{"date":"2014_12_01","client":"mobile"},
{"date":"2014_11_15","client":"desktop"},
{"date":"2014_11_15","client":"mobile"},
{"date":"2014_11_01","client":"desktop"},
{"date":"2014_11_01","client":"mobile"},
{"date":"2014_10_15","client":"desktop"},
{"date":"2014_10_15","client":"mobile"},
{"date":"2014_10_01","client":"desktop"},
{"date":"2014_10_01","client":"mobile"},
{"date":"2014_09_15","client":"desktop"},
{"date":"2014_09_15","client":"mobile"},
{"date":"2014_09_01","client":"desktop"},
{"date":"2014_09_01","client":"mobile"},
{"date":"2014_08_15","client":"desktop"},
{"date":"2014_08_15","client":"mobile"},
{"date":"2014_08_01","client":"desktop"},
{"date":"2014_08_01","client":"mobile"},
{"date":"2014_07_15","client":"desktop"},
{"date":"2014_07_15","client":"mobile"},
{"date":"2014_07_01","client":"desktop"},
{"date":"2014_07_01","client":"mobile"},
{"date":"2014_06_15","client":"desktop"},
{"date":"2014_06_01","client":"desktop"},
{"date":"2014_06_01","client":"mobile"},
{"date":"2014_05_15","client":"desktop"},
{"date":"2014_05_15","client":"mobile"},
{"date":"2014_05_01","client":"desktop"},
{"date":"2014_05_01","client":"mobile"},
{"date":"2014_04_15","client":"desktop"},
{"date":"2014_04_15","client":"mobile"},
{"date":"2014_04_01","client":"desktop"},
{"date":"2014_04_01","client":"mobile"},
{"date":"2014_03_15","client":"desktop"},
{"date":"2014_03_15","client":"mobile"},
{"date":"2014_03_01","client":"desktop"},
{"date":"2014_03_01","client":"mobile"},
{"date":"2014_02_15","client":"desktop"},
{"date":"2014_02_15","client":"mobile"},
{"date":"2014_02_01","client":"desktop"},
{"date":"2014_02_01","client":"mobile"},
{"date":"2014_01_15","client":"desktop"},
{"date":"2014_01_15","client":"mobile"},
{"date":"2014_01_01","client":"desktop"},
{"date":"2014_01_01","client":"mobile"},
{"date":"2013_12_15","client":"desktop"},
{"date":"2013_12_15","client":"mobile"},
{"date":"2013_11_15","client":"desktop"},
{"date":"2013_11_15","client":"mobile"},
{"date":"2013_11_01","client":"desktop"},
{"date":"2013_11_01","client":"mobile"},
{"date":"2013_10_15","client":"desktop"},
{"date":"2013_10_15","client":"mobile"},
{"date":"2013_10_01","client":"desktop"},
{"date":"2013_10_01","client":"mobile"},
{"date":"2013_09_15","client":"desktop"},
{"date":"2013_09_15","client":"mobile"},
{"date":"2013_09_01","client":"desktop"},
{"date":"2013_09_01","client":"mobile"},
{"date":"2013_08_15","client":"desktop"},
{"date":"2013_08_15","client":"mobile"},
{"date":"2013_08_01","client":"desktop"},
{"date":"2013_08_01","client":"mobile"},
{"date":"2013_07_01","client":"desktop"},
{"date":"2013_07_01","client":"mobile"},
{"date":"2013_06_15","client":"desktop"},
{"date":"2013_06_15","client":"mobile"},
{"date":"2013_06_01","client":"desktop"},
{"date":"2013_06_01","client":"mobile"},
{"date":"2013_05_15","client":"desktop"},
{"date":"2013_05_15","client":"mobile"},
{"date":"2013_05_01","client":"desktop"},
{"date":"2013_05_01","client":"mobile"},
{"date":"2013_04_15","client":"desktop"},
{"date":"2013_04_15","client":"mobile"},
{"date":"2013_04_01","client":"desktop"},
{"date":"2013_04_01","client":"mobile"},
{"date":"2013_03_15","client":"desktop"},
{"date":"2013_03_15","client":"mobile"},
{"date":"2013_03_01","client":"desktop"},
{"date":"2013_03_01","client":"mobile"},
{"date":"2013_02_15","client":"desktop"},
{"date":"2013_02_15","client":"mobile"},
{"date":"2013_02_01","client":"desktop"},
{"date":"2013_02_01","client":"mobile"},
{"date":"2013_01_15","client":"desktop"},
{"date":"2013_01_15","client":"mobile"},
{"date":"2013_01_01","client":"desktop"},
{"date":"2013_01_01","client":"mobile"},
{"date":"2012_12_15","client":"desktop"},
{"date":"2012_12_15","client":"mobile"},
{"date":"2012_12_01","client":"desktop"},
{"date":"2012_12_01","client":"mobile"},
{"date":"2012_11_15","client":"desktop"},
{"date":"2012_11_15","client":"mobile"},
{"date":"2012_11_01","client":"desktop"},
{"date":"2012_11_01","client":"mobile"},
{"date":"2012_10_15","client":"desktop"},
{"date":"2012_10_15","client":"mobile"},
{"date":"2012_10_01","client":"desktop"},
{"date":"2012_10_01","client":"mobile"},
{"date":"2012_09_15","client":"desktop"},
{"date":"2012_09_15","client":"mobile"},
{"date":"2012_09_01","client":"desktop"},
{"date":"2012_09_01","client":"mobile"},
{"date":"2012_08_15","client":"desktop"},
{"date":"2012_08_15","client":"mobile"},
{"date":"2012_08_01","client":"desktop"},
{"date":"2012_08_01","client":"mobile"},
{"date":"2012_07_15","client":"desktop"},
{"date":"2012_07_15","client":"mobile"},
{"date":"2012_07_01","client":"desktop"},
{"date":"2012_07_01","client":"mobile"},
{"date":"2012_06_15","client":"desktop"},
{"date":"2012_06_15","client":"mobile"},
{"date":"2012_06_01","client":"desktop"},
{"date":"2012_06_01","client":"mobile"},
{"date":"2012_05_15","client":"desktop"},
{"date":"2012_05_15","client":"mobile"},
{"date":"2012_05_01","client":"desktop"},
{"date":"2012_05_01","client":"mobile"},
{"date":"2012_04_15","client":"desktop"},
{"date":"2012_04_15","client":"mobile"},
{"date":"2012_04_01","client":"desktop"},
{"date":"2012_04_01","client":"mobile"},
{"date":"2012_03_15","client":"desktop"},
{"date":"2012_03_15","client":"mobile"},
{"date":"2012_03_01","client":"desktop"},
{"date":"2012_03_01","client":"mobile"},
{"date":"2012_02_15","client":"desktop"},
{"date":"2012_02_15","client":"mobile"},
{"date":"2012_02_01","client":"desktop"},
{"date":"2012_02_01","client":"mobile"},
{"date":"2012_01_15","client":"desktop"},
{"date":"2012_01_15","client":"mobile"},
{"date":"2012_01_01","client":"desktop"},
{"date":"2012_01_01","client":"mobile"},
{"date":"2011_12_15","client":"desktop"},
{"date":"2011_12_15","client":"mobile"},
{"date":"2011_12_01","client":"desktop"},
{"date":"2011_12_01","client":"mobile"},
{"date":"2011_11_15","client":"desktop"},
{"date":"2011_11_15","client":"mobile"},
{"date":"2011_11_01","client":"desktop"},
{"date":"2011_11_01","client":"mobile"},
{"date":"2011_10_15","client":"desktop"},
{"date":"2011_10_15","client":"mobile"},
{"date":"2011_10_01","client":"desktop"},
{"date":"2011_10_01","client":"mobile"},
{"date":"2011_09_15","client":"desktop"},
{"date":"2011_09_15","client":"mobile"},
{"date":"2011_09_01","client":"desktop"},
{"date":"2011_09_01","client":"mobile"},
{"date":"2011_08_15","client":"desktop"},
{"date":"2011_08_15","client":"mobile"},
{"date":"2011_08_01","client":"desktop"},
{"date":"2011_08_01","client":"mobile"},
{"date":"2011_07_15","client":"desktop"},
{"date":"2011_07_15","client":"mobile"},
{"date":"2011_07_01","client":"desktop"},
{"date":"2011_07_01","client":"mobile"},
{"date":"2011_06_15","client":"desktop"},
{"date":"2011_06_15","client":"mobile"},
{"date":"2011_06_01","client":"desktop"},
{"date":"2011_06_01","client":"mobile"},
{"date":"2011_05_16","client":"desktop"},
{"date":"2011_05_16","client":"mobile"},
{"date":"2011_04_30","client":"desktop"},
{"date":"2011_04_15","client":"desktop"},
{"date":"2011_03_29","client":"desktop"},
{"date":"2011_03_15","client":"desktop"},
{"date":"2011_02_26","client":"desktop"},
{"date":"2011_02_11","client":"desktop"},
{"date":"2011_01_31","client":"desktop"},
{"date":"2011_01_20","client":"desktop"},
{"date":"2010_12_28","client":"desktop"},
{"date":"2010_12_16","client":"desktop"},
{"date":"2010_11_29","client":"desktop"},
{"date":"2010_11_15","client":"desktop"}];


(function() {
    return '# Generated by https://gist.github.com/rviscomi/a7ca24ded7a995d16a987073ad61724b' + pages.map(({date, client}) => {
        const metric = 'bytesJS';
        const [yyyy, mm, dd] = date.split('_');
        return `
SELECT
    '${date}' AS date,
    '${new Date(`${mm}/${dd}/${yyyy}`).getTime()}' AS timestamp,
    '${client}' AS client,
    AVG(${metric}) AS avg,
    MIN(${metric}) AS min,
    MAX(${metric}) AS max,
    APPROX_QUANTILES(${metric},1000)[OFFSET(100)] AS p10,
    APPROX_QUANTILES(${metric},1000)[OFFSET(250)] AS p25,
    APPROX_QUANTILES(${metric},1000)[OFFSET(500)] AS p50,
    APPROX_QUANTILES(${metric},1000)[OFFSET(750)] AS p75,
    APPROX_QUANTILES(${metric},1000)[OFFSET(900)] AS p90
FROM
    \`httparchive.runs.${date}_pages${client == 'mobile' ? '_mobile' : ''}\`
`;
    }).join('UNION ALL');
})()

Funny story, date.replace(/_/g, '-') doesn't produce expected results. https://twitter.com/rick_viscomi/status/872519611857633282