jmaher
/ gist:1b77b25928bcad397b978939828a1bb3
Created
July 2, 2020 22:33
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| --select data.date, data.project, count(distinct data.revision) as pushes, sum(data.cost) as cost, sum(data.cost)/count(distinct data.revision) as cost_per_push from ( | |
| select count(distinct data.revision) as pushes, sum(data.cost) as cost, sum(data.cost)/count(distinct data.revision) as cost_per_push from ( | |
| SELECT | |
| DATE_TRUNC(dts.date, MONTH) as date, | |
| dts.revision, | |
| dts.project, | |
| dts.workerType, | |
| -- sum(cpw.cost) as cost | |
| (sum(dts.execution)/3600)*(sum(cpw.cost)/sum(cpw.hours)) as cost | |
| FROM |
jmaher
/ gist:26cc45751b1c8ef40c47213029c78b62
Created
July 2, 2020 22:16
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- TODO extract month | |
| select data.date, data.project, count(distinct data.revision) as pushes, sum(data.cost) as cost, sum(data.cost)/count(distinct data.revision) as cost_per_push from ( | |
| SELECT | |
| DATE_TRUNC(dts.date, MONTH) as date, | |
| dts.revision, | |
| dts.project, | |
| dts.workerType, | |
| -- sum(cpw.cost) as cost | |
| (sum(dts.execution)/3600)*(sum(cpw.cost)/sum(cpw.hours)) as cost | |
| FROM |
jmaher
/ gist:1fb94e0b53fafef89acb69d70f93182b
Created
July 1, 2020 20:17
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {'06a61ba9c44415c7e84eb2c1548f53a95bd651e0': ['test-linux64-shippable-qr/opt-raptor-tp6-28-firefox-cold-e10s', 'test-linux64-shippable/opt-raptor-tp6-28-firefox-cold-e10s', 'test-macosx1014-64-shippable/opt-raptor-tp6-28-firefox-cold-e10s'], 'd28ceb16f6839c79662e1eb355584d87a9810d60': ['test-linux64-shippable-qr/opt-raptor-ares6-firefox-e10s', 'test-linux64-shippable-qr/opt-raptor-speedometer-firefox-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-13-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-25-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-webaudio-firefox-e10s', 'test-linux64-shippable/opt-raptor-motionmark-htmlsuite-firefox-e10s', 'test-linux64-shippable/opt-raptor-speedometer-firefox-e10s', 'test-linux64-shippable/opt-raptor-stylebench-firefox-e10s', 'test-linux64-shippable/opt-raptor-tp6-13-firefox-cold-e10s', 'test-linux64-shippable/opt-raptor-webaudio-firefox-e10s', 'test-macosx1014-64-shippable/opt-raptor-webaudio-firefox-e10s', 'test-windows10-64-shippable-qr/opt-rapto |
jmaher
/ gist:545e70bd7e9389f9259331911c95a98b
Created
July 1, 2020 20:10
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| {'dbaa47665f9a2693efa9bbafef7133f6c8278ee0': ['test-linux64-shippable-qr/opt-raptor-tp6-18-firefox-cold-e10s', 'test-windows7-32-shippable/opt-raptor-tp6-24-firefox-cold-e10s', 'test-windows7-32-shippable/opt-raptor-tp6-27-firefox-cold-e10s'], '06a61ba9c44415c7e84eb2c1548f53a95bd651e0': ['test-linux64-shippable-qr/opt-raptor-tp6-28-firefox-cold-e10s', 'test-linux64-shippable/opt-raptor-tp6-28-firefox-cold-e10s', 'test-macosx1014-64-shippable/opt-raptor-tp6-28-firefox-cold-e10s'], '45c58c03f69e7cf6f9bb567bdcc0580b31d92ce5': ['test-android-hw-p2-8-0-android-aarch64/pgo-raptor-tp6m-23-geckoview-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-11-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-17-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-22-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-3-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-30-firefox-cold-e10s', 'test-linux64-shippable-qr/opt-raptor-tp6-7-firefox-cold-e10s', 'test-linux64-shippable/o |
jmaher
/ gist:49b40961fd4979b96e1c07e3716c4e96
Created
June 29, 2020 20:32
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @frameworks = 'raptor,browsertime,talos,awsy'; | |
| select | |
| perf_summary.revision, | |
| job_type.name | |
| FROM ( | |
| select | |
| distinct push.revision as revision, | |
| summary.push_id as push_id, | |
| summary.repository_id as repository_id, |
jmaher
/ gist:78957fc4b150db71a8a38fe8b8ed2282
Created
June 25, 2020 16:46
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE start_date DATE DEFAULT "2020-01-01"; | |
| DECLARE end_date DATE DEFAULT "2020-06-01"; | |
| -- granularity is date_part: ideally 'day' or 'month', could be 'week' | |
| DECLARE granularity STRING DEFAULT "month"; | |
| DECLARE types ARRAY <STRING>; | |
| set types = ['t-linux-large', 't-linux-xlarge', 'gecko-t/t-linux-xlarge', 'gecko-t/t-linux-large', 'gecko-t/t-win10-64', 'gecko-t/t-win10-64-gpu-s', 'gecko-t/t-win7-32', 'gecko-t/t-win7-32-gpu']; | |
| -- set types = ['gecko-t/t-linux-large', 't-linux-large']; | |
| SELECT | |
| CASE |
jmaher
/ gist:12b744511bebff9d71c889c064691efc
Created
June 25, 2020 16:08
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE start_date DATE DEFAULT "2020-01-01"; | |
| DECLARE end_date DATE DEFAULT "2020-06-01"; | |
| -- granularity is date_part: ideally 'day' or 'month', could be 'week' | |
| DECLARE granularity STRING DEFAULT "month"; | |
| DECLARE types ARRAY <STRING>; | |
| set types = ['t-linux-large', 't-linux-xlarge', 'gecko-t/t-linux-xlarge', 'gecko-t/t-linux-large', 'gecko-t/t-win10-64', 'gecko-t/t-win10-64-gpu-s', 'gecko-t/t-win7-32', 'gecko-t/t-win7-32-gpu']; | |
| CREATE TEMP FUNCTION | |
| summary(a ARRAY<STRUCT<timestamp TIMESTAMP, | |
| eventType STRING>>) |
jmaher
/ gist:d4cb6396b3aa946fabf8ba4124248f95
Created
June 24, 2020 16:46
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE start_date DATE DEFAULT "2020-01-01"; | |
| DECLARE end_date DATE DEFAULT "2020-06-01"; | |
| -- granularity is date_part: ideally 'day' or 'month', could be 'week' | |
| DECLARE granularity STRING DEFAULT "month"; | |
| DECLARE types ARRAY <STRING>; | |
| set types = ['t-linux-large', 't-linux-xlarge', 'gecko-t/t-linux-xlarge', 'gecko-t/t-linux-large', 'gecko-t/t-win10-64', 'gecko-t/t-win10-64-gpu-s', 'gecko-t/t-win7-32', 'gecko-t/t-win7-32-gpu']; | |
| CREATE TEMP FUNCTION | |
| summary(a ARRAY<STRUCT<timestamp TIMESTAMP, | |
| eventType STRING>>) |
jmaher
/ gist:da0ef6052363f1f7c0c89e7255a74b96
Created
June 23, 2020 17:06
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SET @frameworks = 'raptor,browsertime,talos,awsy'; | |
| SELECT summary.bug_number AS bug_number, | |
| alert.created, | |
| alert.is_regression, | |
| commit.revision, | |
| job_type.name | |
| FROM performance_alert AS alert | |
| INNER JOIN performance_alert_summary AS summary ON ((alert.related_summary_id IS NULL AND summary.id = alert.summary_id) OR summary.id = alert.related_summary_id) | |
| INNER JOIN performance_alert_summary AS original_summary ON original_summary.id = alert.summary_id |
jmaher
/ gist:44420df7e2eec0876d1781849c2740b4
Created
June 23, 2020 16:54
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE start_date DATE DEFAULT "2020-01-01"; | |
| DECLARE end_date DATE DEFAULT "2020-06-01"; | |
| -- granularity is date_part: ideally 'day' or 'month', could be 'week' | |
| DECLARE granularity STRING DEFAULT "month"; | |
| DECLARE types ARRAY <STRING>; | |
| set types = ['t-linux-large', 't-linux-xlarge', 'gecko-t/t-linux-xlarge', 'gecko-t/t-linux-large', 'gecko-t/t-win10-64', 'gecko-t/t-win10-64-gpu-s', 'gecko-t/t-win7-32', 'gecko-t/t-win7-32-gpu']; | |
| CREATE TEMP FUNCTION | |
| summary(a ARRAY<STRUCT<timestamp TIMESTAMP, | |
| eventType STRING>>) |