HA Histogram.js

// getHistogramQuery('bytesJS', '2017_05_15')
getHistogramQuery = (metric, date) => `SELECT
  *
FROM
(
SELECT
  'desktop' AS client,
  volume,
  bin,
  pdf,
  SUM(pdf) OVER (ORDER BY bin) AS cdf
FROM
(
  SELECT
    COUNT(0) AS volume,
    ROUND(FLOOR(pages.${metric} / stats.bin_size) * stats.bin_size, 2) AS bin,
    RATIO_TO_REPORT(volume) OVER () AS pdf
  FROM
    [httparchive:runs.${date}_pages] AS pages CROSS JOIN
    (
      SELECT
        # https://en.wikipedia.org/wiki/Freedman%E2%80%93Diaconis_rule
        2 * (NTH(751, QUANTILES(${metric}, 1001)) - NTH(251, QUANTILES(${metric}, 1001))) / POW(COUNT(${metric}), 1/3) AS bin_size
      FROM
        [httparchive:runs.${date}_pages]
    ) AS stats
  GROUP BY
    bin
)
GROUP BY
  volume, bin, pdf
ORDER BY
  bin ASC
), (
SELECT
  'mobile' AS client,
  volume,
  bin,
  pdf,
  SUM(pdf) OVER (ORDER BY bin) AS cdf
FROM
(
  SELECT
    COUNT(0) AS volume,
    ROUND(FLOOR(pages.${metric} / stats.bin_size) * stats.bin_size, 2) AS bin,
    RATIO_TO_REPORT(volume) OVER () AS pdf
  FROM
    [httparchive:runs.${date}_pages_mobile] AS pages CROSS JOIN
    (
      SELECT
        # https://en.wikipedia.org/wiki/Freedman%E2%80%93Diaconis_rule
        2 * (NTH(751, QUANTILES(${metric}, 1001)) - NTH(251, QUANTILES(${metric}, 1001))) / POW(COUNT(${metric}), 1/3) AS bin_size
      FROM
        [httparchive:runs.${date}_pages_mobile]
    ) AS stats
  GROUP BY
    bin
)
GROUP BY
  volume, bin, pdf
ORDER BY
  bin ASC
)`;