Untitled
unknown
sql
2 years ago
6.9 kB
7
Indexable
SELECT
DATE(created_day) AS date,
COUNT(*) AS total,
COUNTIF(status="done") AS done,
COUNTIF(status="timeout") AS timeout,
COUNTIF(status="error") AS error,
COUNTIF(status="running") AS running,
COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['networkLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(feature_usage, '$.networkLogs.success') LIKE '%true%'
AND JSON_EXTRACT(FEATURE_USAGE, "$['networkLogs']['size']") NOT IN ("255")
AND (JSON_EXTRACT(raw_capabilities, "$['browserstack.networkLogs']") IN ('"true"',
'"True"',
'true',
'True')
OR JSON_EXTRACT(raw_capabilities, "$['bstack:options']['networkLogs']") IN ('"true"',
'"True"',
'true',
'True'))) AS network_log_fine,
COUNTIF(JSON_EXTRACT(raw_capabilities, "$['browserstack.networkLogs']") IN ('"true"',
'"True"',
'true',
'True')
OR JSON_EXTRACT(raw_capabilities, "$['bstack:options']['networkLogs']") IN ('"true"',
'"True"',
'true',
'True')) AS network_log_enabled,
ROUND((COUNTIF(JSON_EXTRACT(FEATURE_USAGE,"$['networkLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(feature_usage, '$.networkLogs.success') LIKE '%true%'
AND JSON_EXTRACT(FEATURE_USAGE, "$['networkLogs']['size']") NOT IN ("255")
AND (JSON_EXTRACT(raw_capabilities, "$['browserstack.networkLogs']") IN ('"true"',
'"True"',
'true',
'True')
OR JSON_EXTRACT(raw_capabilities, "$['bstack:options']['networkLogs']") IN ('"true"',
'"True"',
'true',
'True')))/ COUNTIF(JSON_EXTRACT(raw_capabilities, "$['browserstack.networkLogs']") IN ('"true"',
'"True"',
'true',
'True')
OR JSON_EXTRACT(raw_capabilities, "$['bstack:options']['networkLogs']") IN ('"true"',
'"True"',
'true',
'True')))*100,2) AS network_stability,
COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['seleniumLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(capabilities, "$['browserstack.seleniumLogs']") IN ('1',
'"true"')) AS selenium_log_fine,
COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.seleniumLogs']") IN ('1',
'"true"'))AS selenium_log_enabled,
ROUND((COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['seleniumLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(capabilities, "$['browserstack.seleniumLogs']") IN ('1',
'"true"'))/ COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.seleniumLogs']") IN ('1',
'"true"')))*100,2) AS selenium_stability,
COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['kafkaRawLogs']['success']") LIKE "%true%"
AND JSON_EXTRACT(FEATURE_USAGE, "$['kafkaRawLogs']['messageDropped']") IS NULL) AS text_log_fine,
ROUND((COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['kafkaRawLogs']['success']") LIKE "%true%"
AND JSON_EXTRACT(FEATURE_USAGE, "$['kafkaRawLogs']['messageDropped']") IS NULL)/COUNT(*))*100,2) AS text_stability,
COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['videoLogs']['success']") LIKE "%true%") AS video_log_fine,
COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.video']") IN ('1',
'"true"')
OR JSON_EXTRACT(capabilities, "$['browserstack.video']") IS NULL) AS video_log_enabled,
ROUND((COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['videoLogs']['success']") LIKE "%true%") / COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.video']") IN ('1',
'"true"')
OR JSON_EXTRACT(capabilities, "$['browserstack.video']") IS NULL))*100,2) AS video_stability,
COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['appiumLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(capabilities, "$['browserstack.appiumLogs']") IN ('1',
'"true"')) AS appium_log_fine,
COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.appiumLogs']") IN ('1',
'"true"'))AS appium_log_enabled,
ROUND((COUNTIF(JSON_EXTRACT(FEATURE_USAGE, "$['appiumLogs']['size']") IS NOT NULL
AND JSON_EXTRACT(capabilities, "$['browserstack.appiumLogs']") IN ('1',
'"true"'))/ COUNTIF(JSON_EXTRACT(capabilities, "$['browserstack.appiumLogs']") IN ('1',
'"true"')))*100,2) AS appium_stability,
FROM
`browserstack-production.automate.automate_test_sessions_partitioned`
WHERE
DATE(_PARTITIONTIME) BETWEEN "2022-02-16"
AND "2022-02-17"
AND (device IS NULL
OR device = '')
AND real_mobile = 0
AND terminal in (
"208.52.180.225",
"207.254.75.21",
"207.254.75.13",
"208.52.157.31",
"207.254.75.226",
"207.254.75.237",
"207.254.75.246",
"208.52.180.130",
"208.52.180.32",
"208.52.180.24",
"208.52.180.97",
"208.52.180.228",
"207.254.53.109",
"207.254.53.111",
"207.254.12.248",
"207.254.12.140",
"207.254.62.236",
"207.254.75.10",
"207.254.75.43",
"207.254.53.172",
"208.52.137.145",
"45.95.36.81",
"208.52.145.209",
"193.186.253.14",
"193.186.253.15",
"193.186.253.80",
"10.144.144.41",
"10.144.144.50",
"10.144.208.78",
"65.74.185.252",
"207.254.75.36",
"193.186.253.7",
"216.126.44.75",
"209.10.150.52",
"65.74.185.12",
"193.186.253.27",
"193.186.253.29",
"193.186.253.31",
"10.144.144.26",
"45.95.36.2",
"10.144.208.233",
"10.144.208.237",
"10.144.208.252",
"10.144.209.4",
"10.144.209.31",
"10.144.209.166",
"10.144.209.172",
"10.144.209.175",
"10.144.209.80",
"208.52.180.226",
"207.254.75.17",
"207.254.62.192",
"207.254.53.193",
"10.144.208.62",
"208.52.180.35",
"209.10.150.115",
"45.95.36.30",
"10.144.208.152",
"10.144.144.125",
"45.95.36.100",
"207.254.53.213",
"207.254.75.143",
"207.254.75.136",
"10.144.208.240",
"10.144.208.187",
"10.144.208.188",
"10.144.208.190",
"193.186.253.48",
"193.186.253.163",
"209.10.150.77",
"10.144.208.84",
"45.95.36.13",
"45.95.36.72",
"45.95.36.53",
"45.95.36.31",
"209.10.150.199",
"65.74.147.183",
"65.74.147.160",
"208.52.166.124",
"45.95.36.77",
"10.144.208.133",
"10.144.209.21",
"65.74.147.209",
"10.144.208.215",
"208.52.180.116",
"10.144.144.31",
"45.95.36.129",
"10.144.209.150",
"10.144.144.253",
"10.144.208.56",
"45.95.36.102",
"209.10.151.191",
"65.74.147.61",
"45.95.36.96",
"10.144.209.20",
"45.95.36.82",
"208.78.105.47",
"208.52.157.104",
"208.52.180.59",
"208.52.180.64",
"207.254.53.227",
"207.254.53.195",
"10.144.144.87",
"10.144.144.60",
"209.10.151.38",
"65.74.147.190",
"193.186.253.164",
"45.95.36.154",
"45.95.36.123",
"65.74.185.125",
"209.10.150.44",
"208.52.166.101",
"10.144.208.53",
"10.144.208.89",
"207.254.75.48",
"207.254.75.49",
"208.52.174.150",
"207.254.53.61",
"207.254.75.230",
"10.144.208.58",
"10.144.208.216",
"216.126.44.86",
"10.144.208.170",
"207.254.75.233",
"207.254.75.235",
"208.52.180.104",
"208.52.180.125",
"208.52.180.118",
"207.254.53.162",
"208.52.180.161",
"45.95.36.6",
"207.254.62.231",
"207.254.62.237",
"208.52.166.59",
"208.52.145.33",
"209.10.150.194",
"209.10.150.103",
"45.95.36.11",
"65.74.147.126",
"10.144.208.99",
"207.254.75.83",
"208.52.180.29"
)
GROUP BY
1
ORDER BY
1 DESC
LIMIT
1000