chart1_download_upload_24x7_peak
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, c.peak24h AS peak24h, round(AVG(mt.bytes_sec_interval), 2) as agent_mean, round(AVG(mt.bytes_sec_interval), 2) as metricValue FROM curr_httpget mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > 0) AND (mt.threads > 1) AND (mt.fetch_time > 3e6) AND (mt.bytes_total >= 131072) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY mt.unit_id, c.peak24h ORDER BY peak24h DESC 
 )  oq GROUP BY 'total', oq.peak24h ORDER BY oq.peak24h DESC

chart3_download_24x7_peak_tech
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, round(AVG(mt.bytes_sec_interval), 2) as agent_mean, round(AVG(mt.bytes_sec_interval), 2) as metricValue FROM curr_httpget mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > 0) AND (mt.threads > 1) AND (mt.fetch_time > 3e6) AND (mt.bytes_total >= 131072) AND (mt.target IN ('ispmon.samknows.mlab1v4.akl01.measurement-lab.org', 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC;

chart4_download_hourly_tech
SELECT oq.agent_dtime as aggregatedNumber, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT cast(b.hourNumber as integer) AS agent_dtime, mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, round(AVG(mt.bytes_sec_interval), 2) as agent_mean, round(AVG(mt.bytes_sec_interval), 2) as metricValue FROM curr_httpget mt cross join unnest(split(mt.time_bucket,',')) b (hourNumber) WHERE (mt.successes > 0) AND (mt.threads > 1) AND (mt.fetch_time > 3e6) AND (mt.bytes_total >= 131072) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY b.hourNumber, mt.unit_id, access_technology ORDER BY technology DESC 
 )  oq GROUP BY oq.agent_dtime, oq.technology ORDER BY aggregatedNumber ASC, oq.technology DESC

chart5_upload_24x7_peak_tech
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, round(AVG(mt.bytes_sec_interval), 2) as agent_mean, round(AVG(mt.bytes_sec_interval), 2) as metricValue FROM curr_httppost mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > 0) AND (mt.threads > 1) AND (mt.fetch_time > 3e6) AND (mt.bytes_total >= 131072) AND (mt.target IN ('ispmon.samknows.mlab1v4.akl01.measurement-lab.org', 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC;

chart6_upload_hourly_tech
SELECT oq.agent_dtime as aggregatedNumber, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT cast(b.hourNumber as integer) AS agent_dtime, mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, round(AVG(mt.bytes_sec_interval), 2) as agent_mean, round(AVG(mt.bytes_sec_interval), 2) as metricValue FROM curr_httppost mt cross join unnest(split(mt.time_bucket,',')) b (hourNumber) WHERE (mt.successes > 0) AND (mt.threads > 1) AND (mt.fetch_time > 3e6) AND (mt.bytes_total >= 131072) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY b.hourNumber, mt.unit_id, access_technology ORDER BY technology DESC 
 )  oq GROUP BY oq.agent_dtime, oq.technology ORDER BY aggregatedNumber ASC, oq.technology DESC


chart7_latency_24x7_peak_tech
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, round(AVG(mt.rtt_avg), 2) as agent_mean, round(AVG(mt.rtt_avg), 2) as metricValue FROM curr_udplatency mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > mt.failures) AND (mt.successes > 50) AND (mt.rtt_avg < 1500e6) AND (mt.rtt_min > 0) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC


chart8_loss_24x7_peak_tech
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, AVG(oq.agent_failure_rate) as failure_rate FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, AVG(cast(cast((cast(mt.failures as real)/(cast(mt.failures as real)+cast(mt.successes as real))) as real) * 100 as integer)) as agent_failure_rate, AVG(cast(cast((cast(mt.failures as real)/(cast(mt.failures as real)+cast(mt.successes as real))) as real) * 100 as integer)) as metricValue FROM curr_udplatency mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > mt.failures) AND (mt.successes > 50) AND (mt.rtt_min > 500) AND (mt.rtt_avg < 1500e6) AND (mt.rtt_min > 0) AND (mt.target IN ('ispmon.samknows.mlab1v4.akl01.measurement-lab.org', 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC;


chart9_percentage_of_max_available_download
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, round(AVG((mt.bytes_sec_interval / (cast(max_available_download as real) * 125000)) * 100), 2) as agent_mean, round(AVG((mt.bytes_sec_interval / (cast(max_available_download as real) * 125000)) * 100), 2) as metricValue FROM curr_httpget mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > 0) AND (mt.threads > 1) AND (max_available_download IS NOT NULL) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) AND ((mt.bytes_sec_interval / (cast(max_available_download as real) * 125000)) * 100 <= 100) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC


chart10_percentage_of_max_available_upload
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.peak24h, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, c.peak24h AS peak24h, round(AVG((mt.bytes_sec_interval / (cast(max_available_upload as real) * 125000)) * 100), 2) as agent_mean, round(AVG((mt.bytes_sec_interval / (cast(max_available_upload as real) * 125000)) * 100), 2) as metricValue FROM curr_httppost mt cross join unnest(if((HOUR(dtime) = 19 OR HOUR (dtime) = 20 OR HOUR(dtime) = 21 OR HOUR(dtime) = 22) AND (day_of_week(dtime) != 6 and day_of_week(dtime) != 7), ARRAY['Peak','24/7'],ARRAY['24/7'])) c (peak24h)WHERE (mt.successes > 0) AND (mt.threads > 1) AND (max_available_upload IS NOT NULL) AND ((mt.target = 'ispmon.samknows.mlab1v4.akl01.measurement-lab.org') OR (mt.target = 'ispmon.samknows.mlab1v4.wlg02.measurement-lab.org')) AND ((mt.bytes_sec_interval / (cast(max_available_upload as real) * 125000)) * 100 <= 100) GROUP BY mt.unit_id, access_technology, c.peak24h ORDER BY technology DESC, peak24h DESC 
 )  oq GROUP BY 'total', oq.technology, oq.peak24h ORDER BY oq.technology DESC, oq.peak24h DESC


chart11_latency_by_target
SELECT 'total' as total, round(stddev_pop(oq.metricValue),2) as stdDev, sum(oq.agent_sample_count) as sampleCount, count(distinct oq.unit_id) as agentCount, round(1.2815 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci90, round(1.9600 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci95, round(2.5758 * (stddev_pop(oq.metricValue)/sqrt(count(distinct oq.unit_id))),2) as ci99, min(oq.metricValue) as min, max(oq.metricValue) as max, map(array['1','5','10','20','25','50','75','80','90','95','99'],approx_percentile(oq.metricValue,array[0.01,0.05,0.10,0.2,0.25,0.5,0.75,0.8,0.9,0.95,0.99])) as percentiles, oq.technology, oq.target_group, round(AVG(oq.agent_mean), 2) as mean FROM  ( 
 SELECT mt.unit_id, count(*) as agent_sample_count, access_technology AS technology, mt.target_group, round(AVG(mt.rtt_avg), 2) as agent_mean, round(AVG(mt.rtt_avg), 2) as metricValue FROM curr_udplatency mt WHERE (mt.successes > mt.failures) AND (mt.successes > 50) AND (mt.rtt_avg < 1500e6) AND (mt.rtt_min > 0) GROUP BY mt.unit_id, access_technology, mt.target_group ORDER BY technology DESC, mt.target_group DESC 
 )  oq GROUP BY 'total', oq.technology, oq.target_group ORDER BY oq.technology DESC, oq.target_group DESC