Untitled
unknown
sql
2 years ago
6.9 kB
8
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
Editor is loading...